What is this issue?
The autovacuum_freeze_max_age setting controls when PostgreSQL forces
a vacuum to freeze old transaction IDs:
- Default: 200,000,000 (200 million)
- Maximum safe: ~200 million
- Values higher increase wraparound risk
With a higher value:
- Vacuum happens less frequently for freezing
- Tables accumulate older unfrozen XIDs
- Less buffer before emergency vacuum
- Higher risk if vacuum is blocked
Why it matters
Reduced Safety Margin
Less time to react to vacuum problems
Larger Emergency Vacuums
More work needed when finally triggered
XID Wraparound Risk
Closer to the 2 billion XID limit
Recovery Time
Longer to recover from vacuum backlogs
How PG Pilot detects it
```sql SELECT name, setting FROM pg_settings WHERE name = 'autovacuum_freeze_max_age'; ```
How to fix it
1
Reset to default
Use the recommended value:
ALTER SYSTEM SET autovacuum_freeze_max_age = 200000000;
SELECT pg_reload_conf();
2
Check current XID ages
Verify tables aren't already at high ages:
SELECT relname, age(relfrozenxid)
FROM pg_class
WHERE relkind = 'r'
ORDER BY age(relfrozenxid) DESC
LIMIT 10;Prevention
- Keep freeze_max_age at or below 200 million
- Monitor XID ages with PG Pilot
- Tune per-table settings instead of global