Ask a PostgreSQL database administrator what kills databases in production and you’ll hear about wraparound — the event where a table’s transaction ID counter ages past the point where the oldest rows can no longer be compared safely, forcing the database into a protective single-user shutdown. It’s rare, it’s dramatic, and it dominates the horror stories. But it has also made generations of operators afraid of the wrong thing. Wraparound is a tail risk with clear early-warning signals. The condition that actually degrades most databases slowly and silently is ordinary dead-tuple accumulation: bloat that grows one UPDATE at a time until queries slow, indexes thicken, and autovacuum falls permanently behind.
Both problems trace back to the same root — PostgreSQL’s MVCC design, in which every UPDATE writes a new row version and old versions linger until something reclaims them. That “something” is VACUUM, and the art of running a healthy PostgreSQL instance is mostly the art of keeping vacuuming ahead of your write workload. This post walks through how MVCC creates the dead tuples, how autovacuum decides when and how aggressively to clean them, why insert-heavy tables need special handling, and which operational signals tell you the truth about your database’s bloat — before it becomes an emergency.
Why every UPDATE leaves a corpse
PostgreSQL implements multiversion concurrency control by storing multiple versions of a row in the same table. When you update a row, PostgreSQL writes a complete new tuple and marks the old one superseded; concurrent readers keep seeing the old version until your transaction commits. Nothing is deleted in place. The old tuple — now invisible to new transactions — becomes a dead tuple, and dead tuples are only physical garbage: they occupy heap space, they occupy index entries, and they keep occupying both until VACUUM reclaims them.
Write-heavy tables accumulate dead tuples at roughly the rate of their update traffic. A table updated 10,000 times per second generates up to 10,000 dead tuples per second. Whether that becomes bloat depends entirely on whether cleanup keeps pace, and the heap isn’t even the worst of it: unless an update touches an indexed column in a way that permits a Heap-Only Tuple (HOT) update, every new row version also inserts new entries into every index on the table. A five-index table being updated constantly accumulates dead index entries across all five. VACUUM has to clean the heap and all those indexes, and until it does, every index scan walks through dead entries, growing slower and slower.
HOT updates deserve a digression because they’re the single most effective bloat-prevention mechanism in the design. When a HOT update happens — new version fits on the same page and no indexed column changed — PostgreSQL skips index inserts entirely and links the old version to the new one through a page-local chain. That makes the update dramatically cheaper for indexes, and it means the dead tuple can be reclaimed cheaply during page pruning. You can increase the odds of HOT updates by leaving free space on pages via the table’s fillfactor storage parameter. Tables that are updated heavily but rarely re-indexed benefit enormously from a fillfactor of 70–90 instead of the default 100.
How autovacuum decides to act
You almost never run VACUUM by hand; the autovacuum daemon does it. Every autovacuum_naptime (one minute by default), it wakes up, looks at the statistics for each database, and launches a limited number of workers (three by default, configured by autovacuum_max_workers) on the tables that most need attention. The launcher’s decision for each table is a threshold formula, not a schedule:
vacuum when: dead tuples > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × live tuples
With defaults of 50 and 0.2, a 10-million-row table needs about 2 million dead tuples before autovacuum bothers. That default is fine for an append-mostly table and disastrous for a hot working set: two million dead rows on a 10-million-row table means 20% bloat before cleanup begins, and a scan-heavy workload will feel it. This is why the most common and most effective autovacuum tuning is not a global knob change but a per-table override for your hottest tables:
-- A hot 10M-row table shouldn't wait for 2M dead tuples
ALTER TABLE orders SET (
autovacuum_vacuum_scale_factor = 0.01,
autovacuum_vacuum_threshold = 1000
);
-- Analyze more eagerly too, so the planner sees current data
ALTER TABLE orders SET (autovacuum_analyze_scale_factor = 0.005);The second lever is cost-based throttling. Vacuum operations are charged against a budget (autovacuum_vacuum_cost_limit, with per-operation costs), and when the budget is exhausted the worker sleeps for autovacuum_vacuum_cost_delay milliseconds. This throttling exists so vacuuming doesn’t crowd out production traffic — but on a large, busy server the defaults are so polite that a worker can spend hours cleaning one big table. Modern servers with fast storage generally tolerate raising the cost limit substantially, or driving the delay toward zero, and letting vacuum finish fast rather than trickle.
Note that the worker count multiplies the budget: the cost limit is shared among workers, so adding workers without raising the limit makes each one slower. This is a common tuning mistake — more workers on the same budget is not more vacuuming.
Insert-only tables and the silent freeze problem
For years, autovacuum had a blind spot. Its trigger formula counts dead tuples — and an append-only table (an event log, a time-series table, an audit trail) never has any. Yet those tables still needed vacuuming, for a subtle reason: every row carries transaction-ID bookkeeping that must eventually be “frozen” (marked as ancient so the visibility comparison can’t overflow). Without any dead tuples, the trigger never fired, the rows never got frozen, and the table silently aged toward the wraparound horizon.
PostgreSQL fixed this with an insert-driven trigger: autovacuum_vacuum_insert_threshold (1,000 inserted tuples) plus autovacuum_vacuum_insert_scale_factor (0.2 of the table). An append-only table now gets vacuumed purely for freezing purposes once it has grown enough, even with zero updates and zero dead rows. If you operate a large insert-only table on any modern PostgreSQL version, leave this mechanism enabled — it is the reason such tables no longer need periodic manual VACUUM FREEZE marathons.
The freeze horizon itself is governed by autovacuum_freeze_max_age (200 million transactions by default): when a table’s oldest unfrozen transaction ID exceeds this age, an aggressive anti-wraparound vacuum kicks in regardless of tuple counts. The absolute limit — where the system shuts down to protect data integrity — is around two billion transactions, which sounds distant until you multiply a busy write rate by years. The operational lesson is that the age of your oldest transaction ID, not your row counts, is the wraparound metric, and it’s visible long before it’s dangerous.
VACUUM FULL, and why it’s not the tool you want
Ordinary VACUUM reclaims dead tuples and marks that space reusable within the table — but it doesn’t shrink the table’s footprint on disk or defragment it. When bloat is already severe and you want the space back, the options trade convenience against locking, and the trade is steep:
VACUUM FULLrewrites the entire table compacted, reclaiming all space — while holding anACCESS EXCLUSIVElock. The table is unreadable and unwritable for the duration. On a large table this is an outage, not maintenance.pg_repack, the popular external extension, rebuilds the table online by copying rows into a new file while triggers capture concurrent changes, then swaps them in a brief window. It achieves most of whatVACUUM FULLdoes with only short locks, at the cost of extra disk space during the rebuild.- Prevention — HOT-friendly fillfactors and per-table autovacuum tuning — avoids the need for either. Almost every
VACUUM FULLin production is an autopsy of a bloat-prevention failure.
Plain VACUUM has accumulated useful options for targeted situations. VACUUM (SKIP_LOCKED) skips tables currently locked by other sessions instead of waiting — good for maintenance scripts that shouldn’t queue behind production traffic. PROCESS_TOAST false skips the TOAST table when you know it’s clean, and INDEX_CLEANUP OFF skips index vacuuming for a fast heap-only pass when indexes aren’t the bottleneck.
The signals that actually matter
PostgreSQL exposes everything you need in pg_stat_user_tables, and a working monitoring setup watches four numbers per table:
SELECT
relname,
n_live_tup,
n_dead_tup,
round(n_dead_tup * 100.0 / greatest(n_live_tup, 1), 1) AS dead_pct,
last_autovacuum,
autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;n_dead_tup and its ratio to n_live_tup are the bloat gauge. last_autovacuum answers “is the daemon keeping up?” — a hot table whose last autovacuum timestamp is days old is falling behind. autovacuum_count shows the cumulative cadence. Complement this with n_ins_since_vacuum (high values on append-only tables mean freezing work is piling up) and n_mod_since_analyze (high values mean the planner’s statistics are going stale, which shows up as bad query plans rather than bloat).
For wraparound watch, the query is different — it reads the transaction-age view rather than tuple stats:
SELECT
relname,
age(relfrozenxid) AS xid_age
FROM pg_class
WHERE relkind IN ('r', 't', 'm')
ORDER BY age(relfrozenxid) DESC
LIMIT 10;Age values climbing past a few hundred million mean anti-wraparound vacuums are imminent; values stuck growing on one table mean that table’s rows aren’t being frozen — often because autovacuum can’t get to it or is being throttled. Alert on age relative to autovacuum_freeze_max_age, not on absolute row counts. (For the full story on transaction-ID comparison and how wraparound actually unfolds, the routine vacuuming chapter of the manual is the authoritative reference.)
Wrapping up
MVCC gives PostgreSQL its excellent concurrency, and dead tuples are the price — a price paid continuously rather than at disaster time. The databases that run cleanly for years aren’t the ones whose operators fear wraparound; they’re the ones where HOT updates keep indexes lean, per-table autovacuum settings match actual write rates, insert-only tables age toward freezing on schedule, and dashboards watch n_dead_tup ratios and transaction ages instead of waiting for alarms. Tune the cleanup to the workload, watch the signals, and both bloat and wraparound become routine maintenance instead of incidents.