PostgreSQL in Practice #5 VACUUM and autovacuum: How Dead-Tuple Cleanup Works, and Tuning It

Infrastructure PostgreSQL Database
4 min read

The bill foreshadowed in basics chapter 6 has arrived. MVCC handles UPDATE and DELETE as “add a new version, mark the old one,” so the marked old versions — dead tuples — keep accumulating. VACUUM is what clears them, and if PostgreSQL operational knowledge has a single center, this is it. “Untuned autovacuum plus a large, heavily updated table” sits at the top of the operational-incident charts.

VACUUM’s three jobs #

  • Marking space reusable: space held by dead tuples is marked “fine to write over.” Note the file does not shrink — the space becomes reusable, that’s all. A table that has already ballooned stays that size.
  • Refreshing the visibility map: recording “every row in this block is visible to everyone.” Last chapter, this map’s freshness was exactly the condition for Index Only Scans to land.
  • Preventing transaction ID wraparound: the transaction IDs MVCC uses to tell versions apart are finite and need periodic “freezing.” Let that fall behind and, in the worst case, the database refuses writes. This is the decisive reason you never turn autovacuum off.

Autovacuum also runs statistics collection (ANALYZE) alongside. That’s why basics chapter 5’s “if estimated rows are off, run ANALYZE” normally takes care of itself.

Autovacuum’s threshold: the bigger the table, the later the cleanup #

Autovacuum wakes per table “when dead tuples cross a threshold.” The default threshold is the seed of the problem.

Autovacuum threshold formula 
threshold = autovacuum_vacuum_threshold(50) + autovacuum_vacuum_scale_factor(0.2) × row count

A proportional threshold of 20% of rows is fine for small tables — but on a 100-million-row table it means cleanup starts only after 20 million dead tuples pile up. Meanwhile the table and its indexes balloon, and scans swim across dead rows. The structure means bigger tables get cleaned later, so for large, heavily updated tables you lower the threshold per table.

Adjust scale_factor 
-- start cleaning this table at just 1% dead tuples
ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.01);

The direction is “often, and a little at a time.” The shorter the cleanup cycle, the lighter each pass, and bloat never gets room to grow. PostgreSQL has also kept making VACUUM itself faster version by version (17’s memory-structure improvements, 18’s asynchronous I/O, and so on) — but “when it wakes up” is still governed by the threshold above, so this tuning keeps its value.

The cleanup blocker: long-running transactions #

A day comes when VACUUM runs and dead tuples still don’t shrink. Knowing the principle, it’s obvious: a version some transaction can still see cannot be removed. One transaction holding an hours-old snapshot preserves every dead tuple since that moment — across all tables. This is why practice #3 warned about idle in transaction. Diagnosis is that same routine — find the oldest transaction in pg_stat_activity; prevention is the idle_in_transaction_session_timeout setting plus the code rule of “no external waits inside a transaction.”

Inspecting and remediating bloat #

Start with the statistics view.

Check dead tuple counts 
SELECT relname, n_live_tup, n_dead_tup,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 10;

If n_dead_tup is disproportionate to n_live_tup, or last_autovacuum is ancient, suspect the two causes above — threshold, then long transactions — in order. To actually reclaim space in a badly ballooned table, the table must be rewritten, and the standard command VACUUM FULL holds an exclusive lock on the entire table for the duration — effectively unusable on a live service. In practice you use an extension like pg_repack, which rewrites online without the lock, or you schedule a maintenance window. The best move, of course, is not getting here — the prevention above.

Summary #

  • VACUUM does three jobs: marking space reusable, refreshing the visibility map, freezing transaction IDs. Autovacuum is something you tune, never something you turn off.
  • The default 20% threshold cleans big tables later. Lower scale_factor per table for large, heavily updated ones.
  • The direction is “often, a little at a time.” Frequent light cleanups beat rare heavy ones.
  • Long transactions block cleanup across every table. Prevent with idle-in-transaction timeouts and code discipline.
  • VACUUM FULL takes an exclusive lock — not for live services. Next chapter is the locking story proper: locks and concurrency.
X