XID Wraparound Recovery: The Runbook Your Database Needs Before It's Too Late

If you have been running PostgreSQL in production for long enough, you have probably seen a blog post or conference talk about XID wraparound. You nodded along, thought "I should set up monitoring for that," and then went back to shipping features. This article is the runbook you will wish you had written before the 3 AM page.

The Problem

Transaction ID wraparound is PostgreSQL's nuclear option. Every transaction gets a 32-bit XID. When the counter approaches 2 billion, PostgreSQL forces itself into single-user mode to prevent data corruption. No reads, no writes, no connections -- until you manually run a vacuum that can take hours on large tables.

The insidious part: it sneaks up. XID age grows slowly, day by day, invisible unless you are actively watching. A database consuming 10 million XIDs per day has 200 days before hitting the 2-billion limit. That feels like plenty of time until you realize that autovacuum has been silently blocked for the last 3 weeks.

Three things block vacuum from advancing the XID horizon:

• Long-running transactions -- a single idle in transaction session prevents vacuum from freezing any rows newer than that transaction's snapshot.
• Abandoned prepared transactions -- PREPARE TRANSACTION creates a durable transaction that survives server restarts. If nobody commits or rolls it back, it holds the XID horizon indefinitely.
• Lagging replication slots -- logical replication slots retain WAL and prevent the server from advancing past the slot's confirmed LSN. A slot that falls behind holds the XID horizon for the entire cluster.

Each of these blockers has a different detection method and a different fix. The danger is that you need to check all three, in sequence, and understand how they interact.

How to Detect It

Start with the database-level XID age, then drill into per-table ages and potential blockers:

-- Database-level XID age
SELECT datname, age(datfrozenxid) AS xid_age,
       round(100.0 * age(datfrozenxid) / 2147483647, 2) AS pct_wraparound
FROM pg_database
WHERE datname NOT IN ('template0', 'template1')
ORDER BY age(datfrozenxid) DESC;

-- Top 10 tables holding back the XID horizon
SELECT schemaname, relname, age(relfrozenxid) AS xid_age,
       pg_size_pretty(pg_relation_size(oid)) AS table_size
FROM pg_class JOIN pg_stat_user_tables USING (relname)
WHERE relkind = 'r'
ORDER BY age(relfrozenxid) DESC LIMIT 10;

Now check all three blocker categories:

-- Long-running transactions holding back vacuum
SELECT pid, age(backend_xmin) AS xmin_age, state, query,
       now() - xact_start AS duration
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC LIMIT 5;

-- Orphaned prepared transactions
SELECT gid, prepared, owner, database,
       age(transaction) AS xid_age
FROM pg_prepared_xacts
ORDER BY age(transaction) DESC;

-- Replication slots preventing XID advancement
SELECT slot_name, slot_type, active,
       age(xmin) AS slot_xmin_age,
       age(catalog_xmin) AS slot_catalog_xmin_age
FROM pg_replication_slots
WHERE xmin IS NOT NULL OR catalog_xmin IS NOT NULL
ORDER BY greatest(age(xmin), age(catalog_xmin)) DESC;

The problem with manual detection: you need to run all four queries, correlate the results, and determine which specific blocker is the root cause. If a prepared transaction has XID age 1.4 billion and the worst table has XID age 1.4 billion, the prepared transaction is your blocker -- but that correlation is not obvious when you are running queries one at a time under pressure.

Monitoring for XID Wraparound

The key to effective XID monitoring is not just tracking the age number -- it is automatically correlating blockers with the current horizon. A good monitoring setup should:

• Track per-database and per-table XID ages as time series, not snapshots
• Identify which specific blocker (transaction, prepared transaction, or replication slot) is holding the horizon
• Generate actionable recovery steps in the correct order
• Alert at meaningful thresholds: 500 million (25%) for early warning, 1 billion (50%) for urgent action

Without automated correlation, you are left running queries one at a time during an emergency and hoping you check the right thing first.

How to Fix It

Follow this sequence. Order matters -- clearing blockers before vacuuming prevents wasted work.

Step 1: Remove prepared transaction blockers

-- List and roll back orphaned prepared transactions
SELECT gid, prepared, owner FROM pg_prepared_xacts;
ROLLBACK PREPARED 'txn_2024_q4';

Step 2: Terminate long-running transaction blockers

-- Kill sessions holding the XID horizon
SELECT pg_terminate_backend(12345);

Step 3: Drop inactive replication slots

-- Check if the slot is genuinely needed before dropping
SELECT slot_name, active, age(xmin) FROM pg_replication_slots;
SELECT pg_drop_replication_slot('stale_subscriber_slot');

Step 4: Run targeted VACUUM FREEZE on the worst tables

-- Freeze the tables with the highest XID age first
SET vacuum_cost_delay = 0;  -- Remove throttling for emergency
VACUUM FREEZE large_events_table;
VACUUM FREEZE user_sessions;
VACUUM FREEZE audit_log;
RESET vacuum_cost_delay;

-- Verify XID age decreased
SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database WHERE datname = current_database();

Vacuuming without clearing blockers first is the most common mistake. The vacuum will complete, consuming hours of I/O, but the database XID age will not decrease because the horizon is still pinned by the blocker.

How to Prevent It

Set alerts on XID age at meaningful thresholds. Alert at 500 million (25% of capacity) for early warning, and at 1 billion (50%) for urgent action. The default autovacuum_freeze_max_age of 200 million triggers aggressive vacuum early, but only if autovacuum is not blocked.

Prevent blocker accumulation:

-- Auto-terminate forgotten sessions
ALTER SYSTEM SET idle_in_transaction_session_timeout = '10min';
-- Monitor prepared transactions (should normally be empty)
SELECT count(*) FROM pg_prepared_xacts;
-- Check replication slot health daily
SELECT slot_name, active, age(xmin) FROM pg_replication_slots;

The key to preventing wraparound is not running VACUUM FREEZE harder -- it is ensuring nothing blocks autovacuum's freeze cycle from completing on schedule.