Introduction PostgreSQL uses 32-bit transaction IDs (XIDs). When the XID counter approaches 2 billion, old tuples become invisible because their XIDs appear to be in the future. To prevent this, PostgreSQL freezes old tuples. If freezing does not happen before XID wraparound, the database forcibly shuts down and accepts only superuser connections to perform emergency vacuum.
Symptoms - Warning logs: `database mydb must be vacuumed within 12345678 transactions` - `age(datfrozenxid)` exceeding 1.5 billion - Autovacuum running with `to prevent wraparound` in the process description - Database becoming progressively slower as aggressive vacuum consumes I/O - Eventually: `FATAL: database is not accepting commands to avoid wraparound data loss`
Common Causes - Autovacuum disabled or misconfigured - Long-running transactions preventing dead tuple removal - Extremely high transaction rate (microservices generating millions of transactions) - Tables with no updates never getting vacuumed (read-only tables) - Standby servers replaying transactions without freezing
Step-by-Step Fix 1. **Check XID age for all databases": ```sql SELECT datname, age(datfrozenxid) AS xid_age, mxid_age(datminmxid) AS mxid_age, CASE WHEN age(datfrozenxid) > 1500000000 THEN 'CRITICAL' WHEN age(datfrozenxid) > 1000000000 THEN 'WARNING' ELSE 'OK' END AS status FROM pg_database ORDER BY age(datfrozenxid) DESC; ```
- 1.**Find tables with the oldest frozen XID":
- 2.```sql
- 3.SELECT
- 4.c.oid::regclass AS table_name,
- 5.age(c.relfrozenxid) AS table_xid_age,
- 6.pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
- 7.FROM pg_class c
- 8.JOIN pg_namespace n ON n.oid = c.relnamespace
- 9.WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
- 10.AND c.relkind IN ('r', 'm', 't')
- 11.ORDER BY age(c.relfrozenxid) DESC
- 12.LIMIT 20;
- 13.
` - 14.**Emergency: Run VACUUM FREEZE on critical tables":
- 15.```sql
- 16.-- Run manually on the tables with highest XID age
- 17.VACUUM (FREEZE, VERBOSE) orders;
- 18.VACUUM (FREEZE, VERBOSE) order_items;
- 19.VACUUM (FREEZE, VERBOSE) users;
- 20.
` - 21.**If database is in wraparound shutdown mode":
- 22.```bash
- 23.# Connect as superuser
- 24.psql -U postgres -d mydb
-- Vacuum all databases vacuumdb --all --freeze --verbose
-- Or vacuum specific database vacuumdb -d mydb --freeze --verbose ```
- 1.**Tune autovacuum to prevent future wraparound":
- 2.```sql
- 3.ALTER SYSTEM SET autovacuum = on;
- 4.ALTER SYSTEM SET autovacuum_max_workers = 6;
- 5.ALTER SYSTEM SET autovacuum_freeze_max_age = 200000000; -- Default: 200M
- 6.ALTER SYSTEM SET autovacuum_multixact_freeze_max_age = 400000000;
- 7.ALTER SYSTEM SET vacuum_freeze_min_age = 50000000;
- 8.ALTER SYSTEM SET vacuum_freeze_table_age = 150000000;
- 9.SELECT pg_reload_conf();
- 10.
`