Transaction ID Wraparound Risk

Scenario

At 2 AM, a DBA receives alerts showing WARNING messages in PostgreSQL logs: "database 'analytics' must be vacuumed within 10,000,000 transactions". Later, the database enters a read-only mode with the message: "database is not accepting commands to avoid wraparound data loss". The analytics database has several large tables that have never had VACUUM FREEZE run on them, and age(datfrozenxid) is approaching 2 billion.

How to Identify

Conditions:

• PostgreSQL logs show WARNING: database "X" must be vacuumed within N transactions
• age(datfrozenxid) in pg_database approaching autovacuum_freeze_max_age (default 200M) or beyond
• age(relfrozenxid) in pg_class shows individual tables near the 2-billion transaction limit
• Autovacuum workers running with "(to prevent wraparound)" label in pg_stat_activity
• Logs show "database is not accepting commands to avoid wraparound data loss" (emergency shutdown)

Analysis Steps

-- 1. Check database-level transaction ID age (most critical metric)
SELECT datname,
       age(datfrozenxid)                              AS xid_age,
       current_setting('autovacuum_freeze_max_age')::INT AS freeze_max_age,
       2000000000 - age(datfrozenxid)                 AS txids_remaining
FROM   pg_database
ORDER  BY age(datfrozenxid) DESC;

-- 2. Find tables with the oldest (most dangerous) relfrozenxid
SELECT n.nspname AS schema,
       c.relname AS table_name,
       age(c.relfrozenxid)                              AS xid_age,
       pg_size_pretty(pg_relation_size(c.oid))          AS table_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 autovacuum freeze configuration
SHOW autovacuum_freeze_max_age;
SHOW vacuum_freeze_min_age;
SHOW vacuum_freeze_table_age;

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

-- 5. Check pg_stat_progress_vacuum for tables being frozen now
SELECT p.relid::regclass AS table_name,
       p.phase,
       p.heap_blks_total,
       p.heap_blks_scanned,
       round(p.heap_blks_scanned::numeric /
             nullif(p.heap_blks_total, 0) * 100, 1)  AS pct_done
FROM   pg_stat_progress_vacuum p;

Pitfalls

• Transaction ID wraparound is a database-stopping emergency. When a table’s relfrozenxid age reaches 2,147,483,647, PostgreSQL will shut down the database to prevent data corruption. Rows that appear to belong to the future become invisible.
• Do NOT kill autovacuum anti-wraparound workers. These run even when autovacuum = off and cannot be safely stopped. Killing them delays the freeze and worsens the situation.
• VACUUM FREEZE scans the entire table regardless of how recently rows were modified. It is an I/O-intensive operation — schedule it during off-peak hours.
• The 2-billion limit is per-transaction-ID (per XID), not per row. A database that has processed 2B transactions total is at risk, even if individual tables look fine.
• Monitoring alert thresholds: Alert at age(datfrozenxid) > 500,000,000; emergency at > 1,500,000,000.

Resolution Approach

Transaction ID wraparound is a critical risk requiring immediate and proactive action: