What is this issue?
PostgreSQL uses 32-bit transaction IDs (XIDs) that wrap around after about 4 billion
transactions. To prevent "seeing into the future," PostgreSQL uses VACUUM to "freeze"
old tuples, marking them as visible to all transactions.
The danger zones:
- autovacuum_freeze_max_age (default 200 million): Triggers aggressive anti-wraparound vacuum
- 2 billion XIDs: Database enters emergency mode, refusing all writes
When approaching wraparound:
1. PostgreSQL logs warnings about remaining XIDs
2. Emergency autovacuum kicks in (can cause performance issues)
3. If unchecked, the database shuts down to prevent corruption
Why it matters
Database Shutdown
PostgreSQL will refuse all new transactions to prevent data loss
Emergency Vacuum
Anti-wraparound vacuum can cause severe performance degradation
Application Outage
No writes means your application is effectively down
Long Recovery
Vacuuming old tables can take hours or days
How PG Pilot detects it
```sql
SELECT
datname,
age(datfrozenxid) AS xid_age,
current_setting('autovacuum_freeze_max_age')::bigint AS freeze_max_age,
round(100.0 * age(datfrozenxid) /
current_setting('autovacuum_freeze_max_age')::bigint, 2) AS percent_towards_emergency,
round(100.0 * age(datfrozenxid) / 2147483647, 2) AS percent_towards_wraparound
FROM pg_database
ORDER BY age(datfrozenxid) DESC;
```
How to fix it
Identify the oldest tables
Find which tables are causing the high XID age:
SELECT
relname,
age(relfrozenxid) AS xid_age,
pg_size_pretty(pg_relation_size(oid)) AS size
FROM pg_class
WHERE relkind = 'r'
AND relnamespace NOT IN (
SELECT oid FROM pg_namespace
WHERE nspname IN ('pg_catalog', 'information_schema')
)
ORDER BY age(relfrozenxid) DESC
LIMIT 20;Check for blockers
Identify what's preventing vacuum from freezing:
-- Long-running transactions
SELECT pid, age(backend_xmin), state, query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;
-- Prepared transactions
SELECT * FROM pg_prepared_xacts;
-- Replication slots
SELECT slot_name, age(xmin), age(catalog_xmin)
FROM pg_replication_slots;
Run aggressive vacuum
Manually vacuum the oldest tables with freezing enabled:
VACUUM FREEZE VERBOSE oldest_table;For very large tables, this can take hours. Monitor progress:
SELECT * FROM pg_stat_progress_vacuum;Remove blockers
If blockers exist:
- Terminate long-running transactions (carefully)
- Commit or rollback prepared transactions
- Drop unused replication slots
- Disable hot_standby_feedback on replicas if safe
Prevention
- Monitor XID age with PG Pilot alerts
- Tune autovacuum for large tables
- Avoid long-running transactions
- Don't let replication slots fall behind
- Set statement_timeout to prevent runaway queries