DEV Community

Vahid Yousefzadeh
Vahid Yousefzadeh

Posted on

PostgreSQL Table Bloat and Defragmentation: Autovacuum vs VACUUM vs VACUUM FULL

#postgres #vacuum #autovacuum #database

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
Enter fullscreen mode Exit fullscreen mode 

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)
Enter fullscreen mode Exit fullscreen mode 

postgres=# SELECT count(*) FROM tbl_fragmentation;
  count
---------
 2000000
(1 row)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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
------------
 on
Enter fullscreen mode Exit fullscreen mode

Now we delete 1.8 million rows.

postgres=# DELETE FROM tbl_fragmentation WHERE id <= 1800000;
DELETE 1800000
Time: 27884.584 ms (00:27.885)
Enter fullscreen mode Exit fullscreen mode

Only 200,000 rows remain:

postgres=# SELECT count(*)FROM tbl_fragmentation;
 count
--------
 200000
(1 row)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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_fragmentation
Enter fullscreen mode Exit fullscreen mode

We 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 |                2
Enter fullscreen mode Exit fullscreen mode

The 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)
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode 

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 |
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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 ms
Enter fullscreen mode Exit fullscreen mode

Now 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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (1)