PostgreSQL in Practice

PostgreSQL Vacuum, Freezing, and Bloat

Vacuum turns versions no snapshot can see into reusable space, maintains visibility and planner metadata, and freezes old transaction IDs before wraparound. Safe operation manages cleanup horizons and debt continuously instead of scheduling emergency rewrites.
  • Pruning, vacuuming, truncating, and rewriting reclaim different things.
  • The visibility map enables index-only scans and vacuum skipping.
  • Autovacuum is threshold plus scale factor.
  • Freezing prevents transaction-ID and multixact wraparound.
  • Analyze and vacuum solve different feedback loops.
  • Bloat is an evidence question.
Cleanup and freeze lifecycle
Intervention ladder
SignalFirst actionEscalation
oldest xmin/slot horizonfind owner and business needcancel/terminate or advance/drop only with approved data-loss analysis
dead tuples risingcheck autovacuum runs, locks, worker saturationper-table thresholds/cost; manual VACUUM
stale estimatesANALYZE and inspect modificationsraise statistics target selectively
index growth/poor densityverify workload and index useREINDEX CONCURRENTLY with capacity plan
heap file must shrinkconfirm disk/RTO/lock budgetonline repack tool governance or VACUUM FULL rewrite
Monitor debt with PostgreSQL 18 views
SELECT relid::regclass, n_live_tup, n_dead_tup,
       last_autovacuum, autovacuum_count, last_autoanalyze, autoanalyze_count
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 20;

SELECT relname, age(relfrozenxid), mxid_age(relminmxid)
FROM pg_class WHERE relkind IN ('r','m') ORDER BY age(relfrozenxid) DESC LIMIT 20;

SELECT pid, datname, backend_xmin, state, xact_start
FROM pg_stat_activity WHERE backend_xmin IS NOT NULL ORDER BY xact_start;