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

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

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. Missing one means your emergency VACUUM FREEZE will complete but the XID age will not actually decrease — because the horizon is still pinned by a blocker you did not find.

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.

How myDBA.dev Shows It

myDBA.dev's XID Wraparound page consolidates all of these checks into a single view. Per-table XID age is displayed alongside consumption percentage, color-coded by severity. The critical addition is blocker identification: the page automatically correlates long-running transactions, prepared transactions, and replication slots against the current XID horizon and identifies which specific blocker is preventing vacuum progress.

Instead of "XID age is 1.5 billion" you see "prepared transaction txn_2024_q4 held by user app_batch is the oldest blocker at XID age 1.48 billion — it has been open for 19 days." That is the difference between knowing you have a problem and knowing how to fix it.

The recovery runbook generates the exact SQL commands you need, in the right order. It does not just tell you to run VACUUM FREEZE — it tells you to first roll back the prepared transaction, then terminate the blocking session, then run VACUUM FREEZE on the three highest-priority tables. Each step includes the specific SQL, and the priority ordering ensures you address the root cause before spending hours on a vacuum that would not actually help.

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;

myDBA.dev continuously monitors all three blocker categories alongside XID age trends, surfacing the earliest warning when vacuum progress stalls. The key to preventing wraparound is not running VACUUM FREEZE harder — it is ensuring nothing blocks autovacuum's freeze cycle from completing on schedule.