calricon.
Systems & Architecture

Fix PostgreSQL Transaction ID Wraparound and Autovacuum Stalls

Nov 18, 2026/4 min read/Verified Production Playbook

calricon / field note

Prevent PostgreSQL write lockouts and transaction ID wraparound

Nov 18, 2026/Systems & Architecture
366
Advertisement

Fix PostgreSQL Transaction ID Wraparound and Autovacuum Stalls

In high-write PostgreSQL clusters, production databases can abruptly reject all INSERT, UPDATE, and DELETE statements with ERROR: database is not accepting commands to avoid wraparound data loss, following days of ignored WARNING: database must be vacuumed within N transactions log entries. Even when autovacuum = on, tables continue aging until the entire database enters read-only protection mode.

The verified root cause is transaction ID (XID) exhaustion caused by autovacuum starvation or oldest xmin horizon pinning—where lingering sessions, orphaned replication slots, undersized maintenance_work_mem, or disabled per-table autovacuum settings prevent PostgreSQL from freezing old tuple XIDs within its 2-billion-transaction 32-bit window.



Problem Diagnosis & Root Cause Architecture

PostgreSQL assigns a 32-bit transaction ID to every write transaction, giving roughly 2.14 billion active unfrozen transactions before older rows would appear to lie in the future. Autovacuum prevents wraparound by freezing old tuples, but four conditions block freezing:




  • Orphaned Replication Slots & Long Transactions: An abandoned logical or physical replication slot, an idle-in-transaction backend, or an unresolved two-phase commit (pg_prepared_xacts) pins the global xmin horizon, forcing VACUUM to skip freezing newer tuples.

  • Autovacuum Worker Saturation: When long-running anti-wraparound vacuums occupy all autovacuum_max_workers slots on giant tables, smaller active tables starve and accumulate unfrozen XIDs.

  • Undersized maintenance_work_mem: When maintenance_work_mem cannot hold all tuple pointers for a large table in a single pass, autovacuum repeats expensive index scans multiple times, extending vacuum duration for days.

  • Forgotten Tables with Autovacuum Disabled: Even an unused archive or staging table with autovacuum_enabled = false retains its old relfrozenxid; because XIDs advance globally, that single forgotten table drags the entire database toward wraparound.



Turning on autovacuum does not guarantee XID freezing; a single orphaned replication slot or forgotten table with autovacuum disabled can stall the entire cluster's freeze horizon.


Why Standard Implementations Fail

1. Raising autovacuum_freeze_max_age Only Delays the Outage

Increasing autovacuum_freeze_max_age pushes back the trigger threshold for anti-wraparound workers, allowing more unfrozen bloat to accumulate and shrinking the emergency recovery window once warnings start.



2. Running VACUUM FULL Under Production Load

Executing VACUUM FULL acquires an ACCESS EXCLUSIVE lock that blocks all reads and writes on the table for hours. Standard VACUUM (FREEZE) freezes XIDs online without blocking concurrent queries.



3. Ignoring Lock Contention Between DDL and Autovacuum

Although anti-wraparound autovacuum yields to conflicting locks, repeated DDL migrations or LOCK TABLE statements continuously cancel normal autovacuum workers before they can advance relfrozenxid.



Step-by-Step Resolution Playbook

Step 1: Measure Database and Per-Table XID Age

Query pg_database and pg_class to identify which database and specific tables hold the oldest unfrozen transaction IDs:



-- Check XID age per database (critical threshold is 2,000,000,000)
SELECT datname,
age(datfrozenxid) AS xid_age,
2147483647 - age(datfrozenxid) AS remaining_xids
FROM pg_database
ORDER BY xid_age DESC;

-- Top 10 oldest unfrozen tables in the current database
SELECT c.oid::regclass AS table_name,
age(c.relfrozenxid) AS xid_age,
pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_class c
WHERE c.relkind IN ('r', 'm', 't')
ORDER BY age(c.relfrozenxid) DESC
LIMIT 10;


Step 2: Clear Oldest xmin Pinning Blockers

Inspect active backends, replication slots, and prepared transactions that prevent VACUUM from advancing the freeze horizon:



-- Find long-running transactions or idle-in-transaction sessions
SELECT pid, usename, state, now() - xact_start AS duration, left(query, 80)
FROM pg_stat_activity
WHERE backend_xid IS NOT NULL OR backend_xmin IS NOT NULL
ORDER BY xact_start ASC;

-- Find inactive replication slots holding back xmin
SELECT slot_name, active, xmin, catalog_xmin
FROM pg_replication_slots
WHERE NOT active;

-- Terminate a blocking session or drop an orphaned slot (verify before running)
SELECT pg_terminate_backend(<pid>);
SELECT pg_drop_replication_slot('<orphaned_slot_name>');


Step 3: Boost Autovacuum Throughput and Freeze Oldest Tables

Increase maintenance_work_mem and autovacuum_vacuum_cost_limit so workers complete in a single pass, then run a targeted online VACUUM (FREEZE, VERBOSE) on the oldest tables:



-- Session-level boost for immediate manual recovery
SET maintenance_work_mem = '2GB';
SET vacuum_cost_limit = 2000;

VACUUM (FREEZE, VERBOSE) public.your_oldest_table;

Persist higher autovacuum throughput in postgresql.conf and reload:



autovacuum_max_workers = 6
autovacuum_vacuum_cost_limit = 1000
maintenance_work_mem = 1GB


Step 4: Verify XID Age Reduction and Re-Enable Table Autovacuum

Confirm that no tables have autovacuum_enabled=false and verify that age(relfrozenxid) drops cleanly after vacuuming:



-- Re-check tables with custom autovacuum disabled
SELECT relname, reloptions
FROM pg_class
WHERE reloptions::text LIKE '%autovacuum_enabled=false%';

-- Confirm database XID age dropped below autovacuum_freeze_max_age
SELECT datname, age(datfrozenxid) AS updated_xid_age
FROM pg_database
ORDER BY updated_xid_age DESC;

Confirm that updated_xid_age drops by hundreds of millions of transactions and monitor age(datfrozenxid) rate of change in Prometheus or your database dashboard to catch future pinning regressions early.


Advertisement
Engineering Advisory 48-Hour Audit Turnaround

Experiencing this issue in your production systems?

Calricon conducts senior-level VoIP, SIP trunking, Asterisk, and cloud infrastructure audits. We review your live configs, identify packet drops or contention bottlenecks, and provide exact remediation runbooks.