Postgres Transaction ID Wraparound: How It Happens and How to Prevent It

Transaction ID wraparound is the Postgres failure that sounds theoretical until it takes a busy database offline. Then Postgres refuses to run any write until you vacuum — on a large table, that can mean hours of downtime. It's entirely preventable once you understand the mechanism.

Why transaction IDs wrap

Every transaction that writes gets a transaction ID (XID). Each row version stores the XID that created it (xmin) and, if deleted or updated, the one that removed it (xmax). Visibility rules compare these to decide which rows your snapshot sees. (Postgres MVCC Explained)

XIDs are 32-bit, and comparisons are done modulo 2³²: for any XID, about 2.1 billion XIDs are "in the past" and 2.1 billion are "in the future". As the counter advances, an old row's xmin would eventually flip from "past" to "future" — and the row would vanish from every query. That's wraparound data loss.

Freezing is the fix

VACUUM freezes old rows: it marks their tuples as frozen so they're treated as older than every normal XID, permanently, regardless of the counter. Each table tracks relfrozenxid — every XID older than this is guaranteed frozen. Each database tracks datfrozenxid, the oldest across its tables.

As long as freezing keeps up, the counter can wrap forever without harm.

The settings that drive it

Setting	Default	Meaning
vacuum_freeze_min_age	50M	Rows older than this get frozen when vacuum visits their page
vacuum_freeze_table_age	150M	Past this table age, vacuum scans the whole table (aggressive)
autovacuum_freeze_max_age	200M	Past this, autovacuum forces an anti-wraparound vacuum, even if disabled
vacuum_failsafe_age	1.6B	Emergency mode: vacuum skips index cleanup and cost delays to freeze as fast as possible

There are equivalent multixact_* settings: multixact IDs (used when several transactions lock the same row) wrap too and are frozen the same way.

Recent releases keep improving this: Postgres 18 added eager freezing during normal vacuums so that aggressive whole-table scans have less work to do. (Postgres VACUUM and Bloat)

Monitor it

Database level:

SELECT datname, age(datfrozenxid) AS xid_age,
       mxid_age(datminmxid) AS mxid_age
FROM pg_database
ORDER BY xid_age DESC;

The oldest tables:

SELECT c.oid::regclass AS table_name,
       age(c.relfrozenxid) AS xid_age,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS size
FROM pg_class c
WHERE c.relkind IN ('r', 'm', 't')
ORDER BY age(c.relfrozenxid) DESC
LIMIT 20;

Alert well before trouble: for example, warn when xid_age passes 500 million and page someone at 1 billion. Normal is "a bit above autovacuum_freeze_max_age", sawtoothing down after each anti-wraparound vacuum. Also watch the XID consumption rate — a sudden jump in write volume shortens your runway.

What stops freezing

Vacuum can only freeze rows older than the oldest XID anything might still need. These hold that horizon back:

• Long-running transactions — including an idle-in-transaction session someone left open in a GUI. (Postgres Timeouts)
• Abandoned replication slots, whose xmin/catalog_xmin pins the horizon. (Postgres Replication Slots)
• Forgotten prepared transactions (pg_prepared_xacts).
• hot_standby_feedback from a replica running long queries.

Find them:

SELECT pid, state, xact_start, backend_xmin, left(query, 60)
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC
LIMIT 10;

SELECT slot_name, active, xmin, catalog_xmin FROM pg_replication_slots;
SELECT * FROM pg_prepared_xacts;

Set idle_in_transaction_session_timeout so abandoned sessions don't silently pin the horizon.

Help autovacuum keep up

• Don't disable autovacuum on tables. Tune it instead: more autovacuum_max_workers, a higher autovacuum_vacuum_cost_limit on fast disks.
• Lower autovacuum_freeze_max_age per table for huge, append-heavy tables so freezing happens in smaller, earlier passes.
• Run a manual VACUUM (FREEZE, VERBOSE) on the biggest table during a quiet window if it's trending badly.
• Partition very large append-only tables; old partitions freeze once and stay frozen. (Postgres Table Partitioning)

If it's already happening

As the remaining XIDs run low, Postgres logs warnings ("database must be vacuumed within N transactions"). If it gets within a few million of the limit, it stops assigning new XIDs — every write fails — to protect your data.

1. Don't panic-restart in single-user mode first. Modern versions let you connect normally; read-only queries and VACUUM still work.
2. Remove the blocker: end long transactions, drop dead replication slots, roll back orphaned prepared transactions.
3. VACUUM the oldest tables (from the query above) — plain VACUUM, not VACUUM FULL. Failsafe mode will speed it up.
4. Watch age(datfrozenxid) drop, then fix the monitoring gap that let it get there.

The summary

• XIDs are 32-bit; without freezing, old rows would become invisible after ~2 billion transactions.
• Autovacuum freezes rows; long transactions, slots and prepared transactions block it.
• Monitor age(datfrozenxid) and age(relfrozenxid), alert early, and keep autovacuum healthy.

---

EasySpawn runs Postgres on each server with autovacuum enabled and daily backups, and Claude Code can run these health queries and explain what they show. See how it works or join the waitlist.

Related: The Postgres WAL Explained · Postgres HOT Updates and Fillfactor · Postgres Planner Statistics · Postgres Memory Tuning