Vacuum basics

PostgreSQL MVCC keeps old row versions until vacuum reclaims dead tuples. Without vacuum, tables bloat, indexes swell, and transaction-id wraparound becomes a critical risk.

| Concept | Meaning | |---------|---------| | Dead tuples | Old row versions invisible to new snapshots | | Autovacuum | Background worker triggered by thresholds | | VACUUM | Reclaims space for reuse (often not to OS) | | VACUUM FULL | Rewrites table — locks, downtime risk | | Freeze | Advances xmin horizon to prevent XID wraparound |

					-- Inspect bloat signals
SELECT relname, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

-- Long transactions block vacuum cleanup
SELECT pid, state, xact_start, query
FROM pg_stat_activity
WHERE state != 'idle' AND xact_start < now() - interval '1 hour';
				

High churn tables need tuned autovacuum (`autovacuum_vacuum_scale_factor`, per-table storage parameters). Long open transactions — including idle in transaction sessions — prevent cleanup and cause bloat.

On interviews: explain why UPDATE/DELETE do not free disk immediately, what autovacuum does, and why monitoring dead tuples and oldest xmin matters.

Common pitfalls: disabling autovacuum; VACUUM FULL in production without a maintenance window; ORM sessions left open; ignoring bloat on heavily updated JSONB rows.

The trade-off is MVCC concurrency versus background maintenance — healthy vacuum policy is part of PostgreSQL operations, not an optional DBA chore.

Checklist:

• Monitor dead tuples and autovacuum lag.
• Avoid long-lived transactions.
• Tune autovacuum on hot tables.
• Plan bloat remediation without reckless VACUUM FULL.