VACUUM, Autovacuum, Bloat, Freezing and Wraparound

In one line

VACUUM removes dead tuples so their space can be reused, updates the visibility map (enabling index-only scans) and freezes old tuples. Autovacuum runs it automatically based on dead-tuple thresholds. Neglect leads to bloat, and ultimately transaction ID wraparound, where PostgreSQL stops accepting writes to protect data.

0/5 · 0%

Think of it like this

A warehouse that marks old stock as "discarded" but never clears the shelves. Soon new stock has nowhere to go and pickers walk past empty boxes. VACUUM is the cleaning crew; freezing is stamping very old boxes "permanent" so the date labels can be reused.

Key ideas

01
Plain VACUUM: reclaims space within pages for reuse (the file rarely shrinks), prunes index entries pointing to dead tuples, updates the free space and visibility maps. It runs concurrently with reads and writes.
02
VACUUM FULL rewrites the table compactly but takes an ACCESS EXCLUSIVE lock; use pg_repack for online compaction. VACUUM ANALYZE also refreshes planner statistics.
03
Autovacuum triggers when dead tuples > autovacuum_vacuum_threshold (50) + scale_factor (0.2) × rows, so a 1B-row table waits for 200M dead tuples. Lower the scale factor per large table; raise autovacuum_vacuum_cost_limit so workers aren't throttled.
04
Freezing: xids are 32-bit (~4 billion) and compared modulo 2^32, so tuples older than ~2 billion transactions would appear to be in the future. VACUUM marks old tuples frozen (visible to all). Aggressive anti-wraparound vacuums run when age(relfrozenxid) exceeds autovacuum_freeze_max_age (200M).
05
If freezing can't keep up (blocked by long transactions or slots), PostgreSQL warns, then at ~3 million xids from wraparound refuses new xids. Writes stop until a manual VACUUM completes, which is a severe outage on large tables.

Code & diagrams

vacuum-health.sqlsql

-- tables most in need
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 ORDER BY n_dead_tup DESC LIMIT 10;

-- wraparound risk
SELECT datname, age(datfrozenxid) AS xid_age FROM pg_database ORDER BY 2 DESC;
SELECT relname, age(relfrozenxid) FROM pg_class WHERE relkind = 'r'
ORDER BY 2 DESC LIMIT 5;

-- what is holding back the xmin horizon?
SELECT pid, state, xact_start, backend_xmin, left(query, 50)
FROM pg_stat_activity WHERE backend_xmin IS NOT NULL ORDER BY age(backend_xmin) DESC LIMIT 5;

-- per-table autovacuum tuning for a big, busy table
ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0.01,
                        autovacuum_vacuum_insert_scale_factor = 0.01,
                        autovacuum_vacuum_cost_limit = 2000);

Interview problem

The problem

Wraparound warning on a 3 TB table

Logs show WARNING: database "app" must be vacuumed within 38000000 transactions. The biggest table is 3 TB and receives 20K writes/sec. What do you do now and afterwards?

When it breaks

Disabling autovacuum on a table "because it slows writes"

What you see

Dead tuples accumulate, queries slow, index-only scans stop working (visibility map stale), and eventually wraparound forces an emergency.

Fix & prevent

Never disable it; tune thresholds and cost limits per table and give it more workers and I/O budget.

Explain it without notes

Why does PostgreSQL need to freeze tuples?

Practice

Compute when autovacuum triggers on a 400M-row table with default settings, and propose a better scale factor.

Trade-offs

↔
Aggressive autovacuum uses I/O continuously; lazy autovacuum saves I/O until bloat and wraparound make it an emergency.

Done when you can

I can monitor bloat and xid age and tune autovacuum to prevent wraparound.

Command Palette