What is table bloat in PostgreSQL and how do I fix it?
Quick answer: Bloat is dead row versions left behind by
UPDATEandDELETEunder PostgreSQL’s MVCC. Autovacuum reclaims that space for reuse inside the table; the file usually stays the same size. To shrink a bloated file you needVACUUM FULL(anACCESS EXCLUSIVErewrite) or an online tool likepg_repack.
Why bloat happens: MVCC
Section titled “Why bloat happens: MVCC”PostgreSQL never updates a row in place. Under MVCC (multi-version concurrency control):
- An
UPDATEwrites a new row version and marks the old one dead. - A
DELETEmarks the row dead.
Dead tuples are not removed immediately. They linger so concurrent transactions can still see the version they started with. On a table with frequent updates or deletes, dead tuples pile up faster than they are cleaned, and the table grows with dead space. That is bloat: disk and cache occupied by rows nobody can see.
How do I measure it?
Section titled “How do I measure it?”A quick ratio comes from the stats view:
SELECT relname, n_live_tup, n_dead_tup, round(n_dead_tup::numeric / nullif(n_live_tup, 0), 3) AS dead_ratioFROM pg_stat_user_tablesORDER BY n_dead_tup DESC;For a precise measurement, the pgstattuple extension reports actual
dead_tuple_percent and free_percent:
SELECT * FROM pgstattuple('orders');A high dead-tuple ratio on a large table warrants investigation.
The key surprise: VACUUM does not shrink the file
Section titled “The key surprise: VACUUM does not shrink the file”- Autovacuum / plain
VACUUMremoves dead tuples and marks the space reusable within the table. The file usually stays the same size on disk; future inserts and updates reuse the freed space. One exception: vacuum will truncate fully empty pages at the end of the table and return those to the OS (unlessvacuum_truncateis off). It runs online (SHARE UPDATE EXCLUSIVE, reads and writes continue). VACUUM FULLrewrites the whole table into a fresh file and does return space to the OS, but it holds anACCESS EXCLUSIVElock for the entire rewrite, blocking all reads and writes. On a big table, this can block reads and writes for the duration of the rewrite.
For routine maintenance, autovacuum should keep up so freed space gets reused.
Use VACUUM FULL when a table has bloated badly and you must reclaim disk.
pg_repack performs that rewrite online, holding ACCESS EXCLUSIVE only briefly
at setup and at the final swap (not for the whole copy).
Indexes bloat too; rebuild them with REINDEX (use REINDEX CONCURRENTLY,
available since PostgreSQL 12, to avoid the strong lock).
How do I prevent bloat?
Section titled “How do I prevent bloat?”- Keep autovacuum ahead of write volume. The defaults are conservative for a
busy table; lower
autovacuum_vacuum_scale_factor/ raise the cost limits for hot tables so cleanup keeps pace. - Watch
n_dead_tupandlast_autovacuumon your most-updated tables. - Batch large deletes/updates so a single statement does not create a huge burst of dead tuples autovacuum then struggles to catch up on.
Why does this catch AI-generated code out?
Section titled “Why does this catch AI-generated code out?”Your AI coding agent writes the UPDATE or the bulk DELETE that is correct
for the task, with no view of how often it runs or how the table is vacuumed. A
nightly “update every row’s status” job is fine in isolation and a bloat
machine in production. Whether autovacuum keeps up is an operational property
of your write volume and settings, invisible in the source.
What causes table bloat in PostgreSQL?
MVCC. An UPDATE writes a new row version and marks the old one dead; a DELETE marks the row dead. Those dead tuples stay in the table until vacuum reclaims them, so heavily updated or deleted tables accumulate dead space. That is bloat.
Does VACUUM return disk space to the operating system?
Generally no. A plain VACUUM (and autovacuum) marks dead-tuple space reusable inside the table, so the file usually stays the same size and new rows reuse the freed space. The one exception is truncation: vacuum returns fully empty pages at the end of the table to the OS, unless vacuum_truncate is disabled. Reclaiming space trapped in the middle of the file requires VACUUM FULL or a tool like pg_repack.
What is the difference between VACUUM and VACUUM FULL?
VACUUM reclaims dead tuples for reuse and runs without blocking reads or writes. VACUUM FULL rewrites the entire table into a new file and returns space to the OS, but it takes an ACCESS EXCLUSIVE lock for the whole operation, blocking all access.
How do I measure table bloat?
Check n_dead_tup versus n_live_tup in pg_stat_user_tables for a quick ratio, or use the pgstattuple extension for a precise dead_tuple_percent and free_percent. A high dead-tuple ratio on a big table is the signal.