Recipe

Postgres VACUUM + AUTOVACUUM tuning

Prevent transaction ID wraparound and reclaim dead tuples before they tank query performance.

Why this matters

Postgres uses MVCC — updates and deletes leave dead tuples behind. Without regular VACUUM, tables bloat, indexes fragment, and the transaction ID counter risks wraparound (which forces a shutdown).

Quick diagnostic

SELECT relname, n_dead_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

Tuning parameters

autovacuum_vacuum_scale_factor→ 0.01 (trigger sooner on large tables)
autovacuum_vacuum_cost_limit→ 2000 (less throttling)
autovacuum_max_workers→ 4 (parallel workers)
autovacuum_naptime→ 15s (check more often)

Per-table overrides

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.005,
  autovacuum_vacuum_cost_limit = 4000
);

Manual VACUUM

VACUUM (VERBOSE, ANALYZE) orders;

Use VACUUM FULL only during maintenance windows — it rewrites the entire table and takes an ACCESS EXCLUSIVE lock.

Monitoring

Watch pg_stat_progress_vacuum for running vacuums. Alert if any table exceeds 200M dead tuples or if age(datfrozenxid) approaches 1.5 billion.

← Back to docs