Postgres autovacuum, demystified — the tuning that prevents 3am wraparound panic

Transaction ID wraparound is at the top of my list of Postgres failure modes that have personally cost me sleep. The morning is always the same: a customer's Postgres has gone read-only at 2am, every write rejected with "database is not accepting commands to avoid wraparound data loss," and the only path forward is a VACUUM FREEZE against a 300 GB table that's going to take six hours.

Autovacuum has existed since 2005. The defaults are tuned for "small database, small server" and are wrong on any meaningfully large database in predictable ways.

Per-table tuning for large, churned tables

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 1000,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_freeze_max_age = 100000000,
  autovacuum_vacuum_cost_limit = 2000
);

Any table over 10 GB or 10M rows gets per-table tuning. The defaults of 20% scale factor and 200M XID age mean autovacuums are too rare and too large on big tables.

The full write-up covers:

• What autovacuum is actually doing (dead tuple cleanup vs XID freezing)
• The three metrics to watch (dead_ratio, last_autovacuum, xid_age)
• Why default autovacuum_vacuum_cost_limit = 200 is comically low on SSDs
• Proactive freezing to prevent anti-wraparound autovacuums during business hours
• Index bloat and REINDEX CONCURRENTLY on a schedule
• pg_repack for tables that need disk space returned to the OS

We ship this baseline on every managed Postgres database.