Skip to content

What is table bloat in PostgreSQL and how do I fix it?

Last updated

View as Markdown

Quick answer: Bloat is dead row versions left behind by UPDATE and DELETE under 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 need VACUUM FULL (an ACCESS EXCLUSIVE rewrite) or an online tool like pg_repack.

PostgreSQL never updates a row in place. Under MVCC (multi-version concurrency control):

  • An UPDATE writes a new row version and marks the old one dead.
  • A DELETE marks 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.

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_ratio
FROM pg_stat_user_tables
ORDER 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 VACUUM removes 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 (unless vacuum_truncate is off). It runs online (SHARE UPDATE EXCLUSIVE, reads and writes continue).
  • VACUUM FULL rewrites the whole table into a fresh file and does return space to the OS, but it holds an ACCESS EXCLUSIVE lock 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).

  • 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_tup and last_autovacuum on 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.