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.

Why bloat happens: MVCC

Section titled “Why bloat happens: MVCC”

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 heavily updated or deleted table, dead tuples pile up faster than they are cleaned, and the table grows with dead space. That is bloat: disk and cache spent on rows nobody can see.

How do I measure it?

Section titled “How do I measure it?”

Quick ratio 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 is your signal.

The key surprise: VACUUM does not shrink the file

Section titled “The key surprise: VACUUM does not shrink the file”

This trips people up constantly:

  • 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 that is an outage.

So for ongoing health you want autovacuum keeping up (freed space gets reused). You only need VACUUM FULL when a table bloated badly and you must reclaim disk, and even then, pg_repack does the same 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_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 (Claude Code, Cursor) 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.

FAQ

Section titled “FAQ”
  • What causes table bloat?
    MVCC: UPDATE writes a new row version and marks the old dead; DELETE marks the row dead. Dead tuples accumulate until vacuum reclaims them.
  • Does VACUUM return disk space to the OS?
    Mostly no, plain VACUUM/autovacuum marks space reusable inside the table. The one exception is truncation: it returns fully empty pages at the end of the table to the OS (unless vacuum_truncate is off). Space trapped in the middle of the file needs VACUUM FULL or pg_repack.
  • What is the difference between VACUUM and VACUUM FULL?
    VACUUM reclaims dead tuples for reuse, online. VACUUM FULL rewrites the table and returns space to the OS but holds an ACCESS EXCLUSIVE lock the whole time.
  • How do I measure bloat?
    n_dead_tup vs n_live_tup in pg_stat_user_tables for a quick ratio, or pgstattuple for exact dead_tuple_percent / free_percent.
Section titled “Related”