VACUUM and table bloat

Where dead tuples come from

Why does a table grow to 9 GB when it holds only 4,000 live rows? The mechanism is built into every write you do. The databases track’s MVCC chapter derives this in depth; the SQL author needs the consequence. Under MVCC, Postgres never overwrites a row in place. An UPDATE writes a new row version and marks the old one dead; a DELETE just marks the row dead. The old version lingers because some still-running transaction’s snapshot might need to see it. So every UPDATE and DELETE produces a dead tuple — a row that’s invisible to new queries but still physically occupies a page on disk.

Until those dead tuples are cleaned, they cost you twice: they take space, and every sequential or index scan still has to read the pages they sit on and skip them. A table that’s 90% dead tuples does roughly 10× the I/O for the same live data.

-- See the damage on any table:
SELECT relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_autovacuum
FROM pg_stat_user_tables
WHERE relname IN ('orders', 'events', 'line_items')
ORDER BY n_dead_tup DESC;

Autovacuum: what triggers it

autovacuum is a background process that runs VACUUM automatically. It does not shrink the file — it marks dead-tuple space as reusable so new rows fill the holes instead of extending the table. It also runs the ANALYZE that keeps statistics fresh (lesson 03) and updates the visibility map.

It triggers per table when:

n_dead_tup  >  autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × n_live_tup

Defaults: threshold = 50, scale_factor = 0.2. So a table autovacuums when dead tuples exceed 20% of live rows. That 0.2 is fine for a 10k-row table (vacuum at 2k dead) but terrible for a 100M-row table — it waits for 20M dead tuples, by which time scans are already bloated. The production rule: lower scale_factor per large/hot table, e.g. ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0.02); so it vacuums at 2% churn.

The visibility map and index-only scans

VACUUM maintains the visibility map: a bitmap marking pages where every tuple is visible to all transactions. This is what makes an index-only scan possible — if the page is all-visible, Postgres can answer from the index alone without a heap fetch to check visibility (the databases indexes chapter goes deep). A bloated, under-vacuumed table has a stale visibility map, so “index-only” scans silently degrade into heap fetches. Vacuuming isn’t just garbage collection; it’s what keeps your fastest scan type fast.

VACUUM vs VACUUM FULL — and the prod trap

• VACUUM (plain) — reclaims dead space in place for reuse. Takes only a SHARE UPDATE EXCLUSIVE lock; reads and writes continue. This is what autovacuum runs. It does not return disk to the OS, but it stops the bloat from growing.
• VACUUM FULL — rewrites the entire table into a new compact file, returning space to the OS. But it takes an ACCESS EXCLUSIVE lock for the whole rewrite — every read and write blocks until it finishes. On a large table that’s minutes of total outage. Never run VACUUM FULL on a live production table.

To actually reclaim disk on a bloated production table, use pg_repack (an extension): it rebuilds the table into a fresh copy online, taking the exclusive lock only for a brief final swap. Same compaction, no outage.