Autovacuum Freeze Triggering at Inopportune Time

Scenario

A production database experiences severe I/O spikes at random intervals during business hours, causing query latency to spike 10×. The DBA correlates the spikes with pg_stat_activity showing autovacuum: VACUUM orders (to prevent wraparound). The freeze vacuum cannot be stopped and is consuming all disk I/O.

How to Identify

Conditions:

• pg_stat_activity shows autovacuum: VACUUM tablename (to prevent wraparound)
• I/O spikes at unpredictable intervals during peak hours
• age(relfrozenxid) approaching autovacuum_freeze_max_age for one or more tables
• pg_stat_progress_vacuum shows large tables being frozen mid-day

Analysis Steps

-- 1. Identify anti-wraparound autovacuum workers
SELECT pid, query, now() - query_start AS duration
FROM   pg_stat_activity
WHERE  query LIKE '%to prevent wraparound%';

-- 2. Check transaction age per table
SELECT
    n.nspname AS schema,
    c.relname AS table,
    age(c.relfrozenxid)          AS xid_age,
    current_setting('autovacuum_freeze_max_age')::INT AS freeze_max_age,
    pg_size_pretty(pg_total_relation_size(c.oid)) AS size
FROM   pg_class c
JOIN   pg_namespace n ON n.oid = c.relnamespace
WHERE  c.relkind = 'r'
ORDER  BY age(c.relfrozenxid) DESC
LIMIT  20;

-- 3. Check per-database transaction age
SELECT datname, age(datfrozenxid) AS db_xid_age
FROM   pg_database
ORDER  BY age(datfrozenxid) DESC;

-- 4. Monitor vacuum progress
SELECT relid::regclass, phase, heap_blks_scanned,
       heap_blks_total, heap_blks_vacuumed
FROM   pg_stat_progress_vacuum;

Pitfalls

• Anti-wraparound autovacuums cannot be disabled. They run even when autovacuum = off. They run with elevated priority. There is no way to stop them once triggered — only prevent them from triggering at bad times through proactive maintenance.
• Do not kill autovacuum anti-wraparound workers — PostgreSQL will immediately restart them.
• The root cause is failure to run regular VACUUM FREEZE proactively during off-peak hours.
• autovacuum_freeze_max_age is a safety valve — if you’re hitting it, your proactive vacuum schedule is insufficient.

Resolution Approach

The fix is proactive: schedule VACUUM FREEZE on aging tables during off-peak hours before PostgreSQL is forced to do it during peak hours. Monitor age(relfrozenxid) continuously and alert when tables approach a safe threshold (e.g., 500M transactions).