VACUUM, autovacuum, table bloat, and transaction wraparound

Postgres MVCC keeps old row versions for transactions that might still be reading them. VACUUM reclaims that space. Without functioning vacuum, tables bloat, indexes bloat, and eventually you face transaction ID wraparound — Postgres’ “must-stop-everything-and-vacuum” mode.

The MVCC + dead-tuples model

When you UPDATE or DELETE a row, Postgres doesn’t immediately remove the old version. It marks it with the transaction ID that deleted it (xmax) and inserts a new version. Old versions stay visible to transactions whose snapshot pre-dates the deletion.

UPDATE users SET email='new' WHERE id=42;

-- Before: row v1 (xmax = 0, visible to everyone)
-- After:  row v1 (xmax = <tx>, dead)
--         row v2 (xmin = <tx>, current)

Dead tuples accumulate. VACUUM reclaims their space (returns it to the table’s free space map). VACUUM FULL actually shrinks the table file but takes an exclusive lock and rewrites the table.

What the numbers mean

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_vacuum, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Dead-pct > 20% in a hot table is a red flag. > 50% is a fire.

autovacuum

Postgres runs autovacuum as a background process; it scans tables, decides which need vacuuming, and runs VACUUM (or ANALYZE, or both) per table.

Trigger threshold (default):

autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * n_live_tup
= 50 + 0.2 * n_live_tup       -- 20% dead + 50 baseline

So a 100M row table needs ~20M dead tuples before autovacuum kicks in. That’s way too lazy for hot OLTP. Tune per-table:

ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.02,    -- 2% instead of 20%
    autovacuum_vacuum_threshold = 1000,
    autovacuum_analyze_scale_factor = 0.01
);

Or globally lower the defaults in postgresql.conf for an OLTP workload.

Diagnosing bloat

SELECT
    schemaname || '.' || tablename AS table,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
    pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) AS table_size,
    n_dead_tup, n_live_tup
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 20;

For an estimate of bloat (vs theoretical minimum):

-- The pgstattuple extension is the precise way
CREATE EXTENSION pgstattuple;
SELECT * FROM pgstattuple('orders');
-- dead_tuple_percent, free_percent, etc.

VACUUM FULL — when and why not

VACUUM FULL orders;

Rewrites the table tighter. Takes ACCESS EXCLUSIVE lock — no reads or writes during the operation. Don’t run on a hot table in business hours.

Alternatives:

• pg_repack — non-blocking table rewrite extension. Runs concurrently with normal traffic. Standard tool for de-bloating without downtime.
• Wait for autovacuum to catch up + scale-factor tuning so it doesn’t get this bad again.

Index bloat

UPDATE-heavy tables also bloat their indexes — old index entries pointing to dead row versions. VACUUM reclaims index space, but doesn’t shrink the index file. For severe index bloat:

REINDEX INDEX CONCURRENTLY idx_orders_user_id;     -- 12+ — non-blocking

REINDEX CONCURRENTLY (Postgres 12+) rebuilds the index alongside the old one, swaps atomically. Use this; REINDEX without CONCURRENTLY takes an exclusive lock.

Symptom of index bloat: index size grows while table row count stays constant.

Transaction ID wraparound

Postgres tracks transactions with a 32-bit transaction ID (xid). Every 2 billion transactions, the counter cycles. To prevent rows from “moving into the future” (a row’s xmin appearing newer than the current transaction), Postgres freezes old rows — sets a flag meaning “this row is older than the current xid range, always visible.”

VACUUM does the freezing.

If VACUUM doesn’t run, the xid counter approaches wraparound. Postgres issues warnings; eventually it goes into emergency vacuum mode and stops accepting new transactions. This has caused major outages.

-- How close are you?
SELECT datname, age(datfrozenxid)
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

The age is how many transactions since the oldest frozen xid. Hit 2 billion and Postgres refuses to accept transactions. Tune autovacuum_freeze_max_age (default 200M); set monitoring alerts at, say, 1B.

This is the most famous Postgres production gotcha. Slow autovacuum + high transaction rate + ignored alerts = mid-incident “we can’t write to the DB and we have to wait for vacuum to finish.”

TOAST tables and large objects

Big column values (>2KB by default) get stored in a sidecar “TOAST” table, compressed if it helps. TOAST tables also need vacuum. Their bloat is invisible in main-table size queries — check pg_total_relation_size not just pg_relation_size.

Tuning levers

For a busy OLTP DB, raising maintenance_work_mem and lowering autovacuum_vacuum_scale_factor per-table is the typical baseline.

Long-running transactions: the silent killer

A transaction that stays open holds the xmin horizon — vacuum cannot reclaim rows that this transaction might still see. A 30-minute SELECT on a hot table blocks bloat cleanup across the whole DB.

SELECT pid, usename, datname, state, query_start, NOW() - query_start AS duration, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start
LIMIT 20;

Look for long-running transactions and idle in transaction sessions — open transactions waiting on the client. Long-lived clients (forgotten cursors, app holding a connection across user thinking time) are the #1 cause.

Common production fixes

1. Tune autovacuum aggressiveness per-table on hot OLTP tables.
2. Kill idle in transaction long sessions with idle_in_transaction_session_timeout = '10min'.
3. Use pg_repack to de-bloat without downtime.
4. Alert on n_dead_tup ratio, autovacuum lag, and age(datfrozenxid).
5. Connection pooling — fewer connections = fewer chances of idle in transaction.
6. vacuum verbose when investigating to see what’s actually getting reclaimed.

Interview angle

• “What does VACUUM do?” — reclaims space from dead tuples (rows deleted/updated but not yet removed under MVCC), updates the free space map so future inserts can reuse the space, and freezes old rows to prevent transaction ID wraparound.
• “What’s autovacuum and how does it decide when to run?” — background process. Per-table threshold: autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * live_tuples. Defaults are 50 + 20% — too lazy for hot OLTP. Tune per-table.
• “What’s transaction ID wraparound?” — Postgres uses a 32-bit xid. Without VACUUM freezing old rows, after 2 billion transactions the counter wraps and rows look “in the future”. Postgres goes into emergency single-user mode to prevent corruption. Major outage cause.
• “How do you find table bloat?” — n_dead_tup / (n_live_tup + n_dead_tup) from pg_stat_user_tables; > 20% is a flag. Or the pgstattuple extension for accurate measurement. Compare pg_total_relation_size over time to see growth without row growth.
• “How do you de-bloat without downtime?” — pg_repack (extension). VACUUM FULL takes ACCESS EXCLUSIVE lock and is blocked-on by every reader/writer. REINDEX CONCURRENTLY (Postgres 12+) for indexes.
• “What’s an idle in transaction and why does it matter?” — a client connection with an open transaction not actively running a query. Holds the xmin horizon, preventing vacuum from reclaiming dead tuples until the transaction ends. Cause of mystery bloat. Set idle_in_transaction_session_timeout.
• “You see n_dead_tup = 10M on a 100M-row table. What do you do?” — check last_autovacuum, verify autovacuum is running, check for long transactions blocking xmin horizon. Tune per-table scale factor down. If acute: pg_repack to de-bloat now.