PostgreSQL Table Bloat and Defragmentation: Autovacuum vs VACUUM vs VACUUM FULL
When rows are updated or deleted in PostgreSQL, the database does not immediately remove the old row versions from the table. Instead, PostgreSQL uses MVCC (Multi-Version Concurrency Control) and creates new row versions when necessary.
As a result, a table can contain dead tuples and occupy significantly more disk space than the amount required by its current live data.
In this article, we will use the same table and workload to demonstrate how PostgreSQL handles this situation with:
- Autovacuum: Automatically removes dead tuples in the background and makes their space reusable. It does not require an exclusive table lock and normally does not shrink the table on disk.
- Manual VACUUM: Does the same cleanup as autovacuum, but you start it manually. It removes dead tuples and makes space reusable, but normally does not shrink the table.It does not require an exclusive table lock.
- VACUUM FULL: Rebuilds the table using only live rows and physically releases unused disk space. It requires an ACCESS EXCLUSIVE lock, blocking concurrent access to the table while it runs.
The important point is that these operations do not have the same effect on table size.
1. Initial Table
First, we create a table containing 2 million rows.
postgres=# CREATE TABLE tbl_fragmentation (
postgres(# id BIGSERIAL PRIMARY KEY,
postgres(# name TEXT,
postgres(# description TEXT,
postgres(# amount NUMERIC(12,2),
postgres(# created_at TIMESTAMP DEFAULT now()
postgres(# );
CREATE TABLE
postgres=# INSERT INTO tbl_fragmentation (name, description, amount)
postgres-# SELECT
postgres-# 'User_' || gs,
postgres-# repeat('X', 500),
postgres-# (random() * 10000)::numeric(12,2)
postgres-# FROM generate_series(1, 2000000) AS gs;
INSERT 0 2000000
Time: 48542.364 ms (00:48.542)
postgres=# SELECT count(*) FROM tbl_fragmentation;
count
---------
2000000
(1 row)The table initially occupies approximately 1116 MB.
postgres=# \dt+
List of tables
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
public | tbl_fragmentation | table | postgres | permanent | heap | 1116 MB |
(1 row)At this point, the table contains 2 million live rows and there is no significant dead-tuple accumulation.
2. Table Defragmentation Using Autovacuum
PostgreSQL enables autovacuum by default.
postgres=# SHOW autovacuum;
autovacuum
------------
onNow we delete 1.8 million rows.
postgres=# DELETE FROM tbl_fragmentation WHERE id <= 1800000;
DELETE 1800000
Time: 27884.584 ms (00:27.885)Only 200,000 rows remain:
postgres=# SELECT count(*)FROM tbl_fragmentation;
count
--------
200000
(1 row)However, the physical table size is still approximately 1116 MB.
postgres=# \dt+
List of tables
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
public | tbl_fragmentation | table | postgres | permanent | heap | 1116 MB |
(1 row)A DELETE does not immediately return the occupied pages to the operating system. The deleted rows become dead tuples, and the space can later be reused by PostgreSQL.
Autovacuum detects the dead tuples and starts a vacuum operation.
postgres=# SELECT pid, backend_type, state, wait_event_type, wait_event, now() - query_start AS running_for, query FROM pg_stat_activity WHERE backend_type LIKE 'autovacuum%' and state='active';
pid | backend_type | state | wait_event_type | wait_event | running_for | query
-------+-------------------+--------+-----------------+-------------+-----------------+---------------------------------------------
14080 | autovacuum worker | active | Timeout | VacuumDelay | 00:07:26.277602 | autovacuum: VACUUM public.tbl_fragmentationWe can check the table statistics:
SELECT
n_live_tup,
n_dead_tup,
last_autovacuum,
autovacuum_count
FROM pg_stat_user_tables
WHERE relname = 'tbl_fragmentation';
n_live_tup | n_dead_tup | last_autovacuum | autovacuum_count
------------+------------+----------------------------------+------------------
19889 | 0 | 2026-09-15 22:17:42.548493+03:30 | 2The important observation is that n_dead_tup is now zero after autovacuum.
However, the table size has not decreased.
postgres=# \dt+
List of tables
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
public | tbl_fragmentation | table | postgres | permanent | heap | 1116 MB |
(1 row)This illustrates an important PostgreSQL behavior:
VACUUM makes space occupied by dead tuples reusable, but normally does not shrink the physical table file.
We can demonstrate this by inserting another 1.8 million rows.
INSERT INTO tbl_fragmentation (name, description, amount)
SELECT
'User_' || gs,
repeat('X', 500),
(random() * 10000)::numeric(12,2)
FROM generate_series(2000001, 3800000) AS gs;
INSERT 0 1800000
postgres=# select count(*) from tbl_fragmentation;
count
---------
2000000
postgres=# \dt+
List of tables
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
public | tbl_fragmentation | table | postgres | permanent | heap | 1116 MB |
The space that previously belonged to deleted rows was made available for reuse, so PostgreSQL could insert new rows without requiring the table file to grow.
The table statistics also show that the dead tuples were cleaned:
SELECT
n_live_tup,
n_dead_tup,
last_autovacuum,
autovacuum_count
FROM pg_stat_user_tables
WHERE relname = 'tbl_fragmentation';
n_live_tup | n_dead_tup | last_autovacuum | autovacuum_count
------------+------------+----------------------------------+------------------
1999955 | 0 | 2026-09-15 22:27:41.696749+03:30 | 3
(1 row)What happens with UPDATE?
UPDATE is particularly important because PostgreSQL normally implements an update by creating a new row version rather than modifying the existing row in place.
We now update 1.8 million rows:
postgres=# select count(*) from tbl_fragmentation;
count
---------
2000000
(1 row)
postgres=# \dt+
List of tables
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
public | tbl_fragmentation | table | postgres | permanent | heap | 1116 MB |
(1 row)
postgres=# UPDATE tbl_fragmentation
postgres-# SET description = repeat('Y', 500)
postgres-# WHERE id <= 3600000;
UPDATE 1800000
Time: 76530.884 ms (01:16.531)
postgres=# \dt+
List of tables
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
public | tbl_fragmentation | table | postgres | permanent | heap | 2121 MB |
(1 row)
postgres=# select count(*) from tbl_fragmentation;
count
---------
2000000
(1 row)
postgres=# SELECT pid, backend_type, state, wait_event_type, wait_event, now() - query_start AS running_for, query FROM pg_stat_activity WHERE backend_type LIKE 'autovacuum%' and state='active';
pid | backend_type | state | wait_event_type | wait_event | running_for | query
-------+-------------------+--------+-----------------+-------------+-----------------+-----------------------------------------------------
12604 | autovacuum worker | active | Timeout | VacuumDelay | 00:08:47.232972 | autovacuum: VACUUM ANALYZE public.tbl_fragmentation
(1 row)
postgres=# \dt+
List of tables
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
public | tbl_fragmentation | table | postgres | permanent | heap | 2121 MB |
(1 row)
SELECT
n_live_tup,
n_dead_tup,
last_autovacuum,
autovacuum_count
FROM pg_stat_user_tables
WHERE relname = 'tbl_fragmentation';
n_live_tup | n_dead_tup | last_autovacuum | autovacuum_count
------------+------------+----------------------------------+------------------
2005317 | 53580 | 2026-09-15 22:45:08.785006+03:30 | 4
(1 row)
The key observation is that the update generated dead tuples. Autovacuum can clean these dead tuples and make their space reusable, but it does not normally compact the entire table and return the unused space to the operating system.
3. Manual VACUUM
Now we disable autovacuum for the test table so that we can manually control when vacuum occurs.
postgres=# DROP TABLE IF EXISTS tbl_fragmentation;
DROP TABLE
postgres=# CREATE TABLE tbl_fragmentation (
postgres(# id BIGSERIAL PRIMARY KEY,
postgres(# name TEXT,
postgres(# description TEXT,
postgres(# amount NUMERIC(12,2),
postgres(# created_at TIMESTAMP DEFAULT now()
postgres(# );
CREATE TABLE
postgres=# ALTER TABLE tbl_fragmentation SET (autovacuum_enabled = false);
ALTER TABLE
postgres=# INSERT INTO tbl_fragmentation (name, description, amount)
postgres-# SELECT
postgres-# 'User_' || gs,
postgres-# repeat('X', 500),
postgres-# (random() * 10000)::numeric(12,2)
postgres-# FROM generate_series(1, 2000000) AS gs;
INSERT 0 2000000
Time: 49524.589 ms (00:49.525)
postgres=# SELECT count(*) FROM tbl_fragmentation;
count
---------
2000000
(1 row)The table is approximately 1116 MB:
postgres=# \dt+
List of tables
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
public | tbl_fragmentation | table | postgres | permanent | heap | 1116 MB |
(1 row)Now we update 1.8 million rows.
postgres=# UPDATE tbl_fragmentation
postgres-# SET description = repeat('Y', 500)
postgres-# WHERE id <= 1800000;
UPDATE 1800000
Time: 69229.523 ms (01:09.230)
postgres=# SELECT count(*) FROM tbl_fragmentation;
count
---------
2000000
(1 row)The table grows to 2121 MB:
postgres=# \dt+
List of tables
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+------------------+-------+----------+-------------+---------------+---------+-------------
public | tbl_fragmentation | table | postgres | permanent | heap | 2121 MB |
(1 row)The statistics show approximately 1.79 million dead tuples:
postgres=# SELECT
postgres-# n_live_tup,
postgres-# n_dead_tup,
postgres-# last_vacuum,
postgres-# vacuum_count
postgres-# FROM pg_stat_user_tables
postgres-# WHERE relname = 'tbl_fragmentation';
n_live_tup | n_dead_tup | last_vacuum | vacuum_count
------------+------------+-------------+--------------
2006910 | 1793096 | | 0
(1 row)Now we manually execute:
postgres=# VACUUM tbl_fragmentation;
VACUUM
Time: 24971.367 ms (00:24.971)After vacuum:
postgres=# SELECT
postgres-# n_live_tup,
postgres-# n_dead_tup,
postgres-# last_vacuum,
postgres-# vacuum_count
postgres-# FROM pg_stat_user_tables
postgres-# WHERE relname = 'tbl_fragmentation';
n_live_tup | n_dead_tup | last_vacuum | vacuum_count
------------+------------+----------------------------------+--------------
1904225 | 0 | 2026-09-15 23:05:24.632805+03:30 | 1
(1 row)The dead tuples have been cleaned. However, the table is still 2121 MB:
postgres=# \dt+
List of tables
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
public | tbl_fragmentation | table | postgres | permanent | heap | 2121 MB |
(1 row)This is the same fundamental behavior we observed with autovacuum.
Manual VACUUM and autovacuum both reclaim dead-tuple space for reuse; neither is intended to compact the table file and return all unused space to the operating system.
Now we delete 1.8 million rows:
postgres=# DELETE FROM tbl_fragmentation WHERE id <= 1800000;
DELETE 1800000
Time: 29234.591 ms (00:29.235)
postgres=# \dt+
List of tables
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
public | tbl_fragmentation | table | postgres | permanent | heap | 2121 MB |
(1 row)
postgres=# SELECT
postgres-# n_live_tup,
postgres-# n_dead_tup,
postgres-# last_vacuum,
postgres-# vacuum_count
postgres-# FROM pg_stat_user_tables
postgres-# WHERE relname = 'tbl_fragmentation';
n_live_tup | n_dead_tup | last_vacuum | vacuum_count
------------+------------+----------------------------------+--------------
105605 | 1800000 | 2026-09-15 23:22:02.167342+03:30 | 1
(1 row)
postgres=# VACUUM tbl_fragmentation;
VACUUM
Time: 25668.475 ms (00:25.668)
postgres=# \dt+
List of tables
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
public | tbl_fragmentation | table | postgres | permanent | heap | 1116 MB |
(1 row)
postgres=# SELECT count(*) FROM tbl_fragmentation;
count
--------
200000
(1 row)The table size is shown as 1116 MB after this operation in the test output:
2121 MB →1116 MB
4. VACUUM FULL
VACUUM FULL has a fundamentally different purpose.
It physically rewrites the table and creates a compact version containing the live rows.
Again, we create the test table with autovacuum disabled and repeat the scenario:
postgres=# DROP TABLE IF EXISTS tbl_fragmentation;
DROP TABLE
Time: 269.546 ms
postgres=# CREATE TABLE tbl_fragmentation (
postgres(# id BIGSERIAL PRIMARY KEY,
postgres(# name TEXT,
postgres(# description TEXT,
postgres(# amount NUMERIC(12,2),
postgres(# created_at TIMESTAMP DEFAULT now()
postgres(# );
CREATE TABLE
Time: 7.112 ms
postgres=# INSERT INTO tbl_fragmentation (name, description, amount)
postgres-# SELECT
postgres-# 'User_' || gs,
postgres-# repeat('X', 500),
postgres-# (random() * 10000)::numeric(12,2)
postgres-# FROM generate_series(1, 2000000) AS gs;
INSERT 0 2000000
Time: 48542.364 ms (00:48.542)
postgres=# SELECT count(*) FROM tbl_fragmentation;
count
---------
2000000
(1 row)
Time: 28598.005 ms (00:28.598)
postgres=# \dt+
List of tables
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
public | tbl_fragmentation | table | postgres | permanent | heap | 1116 MB |
(1 row)
postgres=# UPDATE tbl_fragmentation
postgres-# SET description = repeat('Y', 500)
postgres-# WHERE id <= 1800000;
UPDATE 1800000
Time: 67174.851 ms (01:07.175)
postgres=# SELECT count(*) FROM tbl_fragmentation;
count
---------
2000000
(1 row)
Time: 54238.243 ms (00:54.238)
postgres=# \dt+
List of tables
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
public | tbl_fragmentation | table | postgres | permanent | heap | 2121 MB |
(1 row)
postgres=# SELECT
postgres-# n_live_tup,
postgres-# n_dead_tup,
postgres-# last_vacuum,
postgres-# vacuum_count
postgres-# FROM pg_stat_user_tables
postgres-# WHERE relname = 'tbl_fragmentation';
n_live_tup | n_dead_tup | last_vacuum | vacuum_count
------------+------------+-------------+--------------
1990950 | 1809056 | | 0
(1 row)
Time: 1.124 msNow we execute VACUUM FULL command:
postgres=# VACUUM FULL tbl_fragmentation;
VACUUM
Time: 34663.258 ms (00:34.663)After VACUUM FULL, the table is physically compacted:
postgres=# SELECT
postgres-# n_live_tup,
postgres-# n_dead_tup,
postgres-# last_vacuum,
postgres-# vacuum_count
postgres-# FROM pg_stat_user_tables
postgres-# WHERE relname = 'tbl_fragmentation';
n_live_tup | n_dead_tup | last_vacuum | vacuum_count
------------+------------+-------------+--------------
1990950 | 1809056 | | 0
(1 row)
postgres=# \dt+
List of tables
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
public | tbl_fragmentation | table | postgres | permanent | heap | 1116 MB |
(1 row)In this particular test, the table returns to approximately its original size.
Now we delete 1.8 million rows again:
postgres=# DELETE FROM tbl_fragmentation WHERE id <= 1800000;
DELETE 1800000
Time: 29704.323 ms (00:29.704)
postgres=# SELECT count(*)FROM tbl_fragmentation;
count
--------
200000
(1 row)
Time: 25574.595 ms (00:25.575)
postgres=# \dt+
List of tables
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
public | tbl_fragmentation | table | postgres | permanent | heap | 1116 MB |
(1 row)
postgres=# VACUUM FULL tbl_fragmentation;
VACUUM
Time: 4336.966 ms (00:04.337)
postgres=# \dt+
List of tables
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+-------------------+-------+----------+-------------+---------------+--------+-------------
public | tbl_fragmentation | table | postgres | permanent | heap | 112 MB |
(1 row)
The table has now shrunk from approximately 1116 MB to 112 MB.
This is the main difference between normal VACUUM and VACUUM FULL.
VACUUM FULL physically rebuilds the table using only the remaining live rows, allowing PostgreSQL to release the unused disk space associated with the old table.
Space Reclamation and Execution Time Comparison
In this test, autovacuum took several minutes because it runs in the background and intentionally throttles its work to reduce its impact on normal database activity. Manual VACUUM completed much faster because it was started directly without waiting for autovacuum scheduling. VACUUM FULL also completed quickly in this test and physically compacted the table, significantly reducing its size.
Written by Vahid Yousefzadeh
I have been a DBA since 2011 and I work with Oracle technology. Linkdin: linkedin.com/in/vahidusefzadeh telegram channel ID:@oracledb vahidusefzadeh@gmail.com
Comments
Post a Comment