What is this issue?
PostgreSQL tracks the oldest unfrozen transaction ID (relfrozenxid) for each table.
When this age approaches autovacuum_freeze_max_age (default 200 million), PostgreSQL
triggers an aggressive anti-wraparound vacuum that:
- Runs with higher priority than normal autovacuum
- Cannot be canceled
- May cause significant I/O and CPU load
- Can impact application performance
This is a warning sign that vacuum isn't keeping up with the table's activity.
Why it matters
Emergency Vacuum
Anti-wraparound vacuum is aggressive and cannot be stopped
Performance Impact
Emergency vacuum consumes significant I/O resources
Wraparound Risk
If not addressed, leads to XID wraparound shutdown
Maintenance Window
Better to vacuum proactively during low-traffic periods
How PG Pilot detects it
```sql
SELECT
schemaname,
relname,
age(relfrozenxid) AS xid_age,
current_setting('autovacuum_freeze_max_age')::bigint AS freeze_max_age,
round(100.0 * age(relfrozenxid) /
current_setting('autovacuum_freeze_max_age')::bigint, 2) AS pct_towards_freeze
FROM pg_stat_user_tables
JOIN pg_class ON relname = pg_class.relname
WHERE age(relfrozenxid) > current_setting('autovacuum_freeze_max_age')::bigint * 0.75
ORDER BY age(relfrozenxid) DESC;
```
How to fix it
Run manual vacuum freeze
Proactively vacuum the table with freezing:
VACUUM FREEZE VERBOSE your_table;This is less disruptive when done during low-traffic periods.
Check for vacuum blockers
Identify what might be preventing autovacuum:
-- Long-running transactions
SELECT pid, age(backend_xmin), query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;
-- Replication slots
SELECT slot_name, age(xmin), age(catalog_xmin)
FROM pg_replication_slots;
Tune autovacuum for this table
Make autovacuum more aggressive for high-write tables:
ALTER TABLE your_table SET (
autovacuum_freeze_min_age = 10000000,
autovacuum_freeze_table_age = 150000000
);Prevention
- Monitor freeze age with PG Pilot
- Tune autovacuum for high-write tables
- Avoid long-running transactions
- Schedule periodic VACUUM FREEZE during maintenance windows