PostgreSQL XID Wraparound and Freezing

The idea: a 32-bit clock that wraps around

Every PostgreSQL transaction that writes gets a transaction ID (XID). Each row version stores the XID that created it in xmin. A row is visible to you only if its xmin committed before your snapshot. XIDs are 32-bit numbers, so after about 4.29 billion transactions the counter starts again at 3. A busy server can get there in weeks.

The canvas shrinks the circle to 64 XIDs so you can see it turn. The orange hand is the next XID. Green cells are in the past, red cells in the future. The blue dots are the rows of table orders, each placed at its xmin.

Comparing XIDs modulo 2³²

PostgreSQL never compares XIDs with a plain <. TransactionIdPrecedes(a, b) computes (int32)(a - b) < 0. So for any XID, the 2³¹ (about 2.1 billion) XIDs behind it are older and the 2³¹ ahead of it are newer. The comparison has no fixed "zero": it turns with the counter.

XIDs 0, 1 and 2 are special: InvalidTransactionId, BootstrapTransactionId and FrozenTransactionId. Normal XIDs start at 3, and after 2³² − 1 the counter skips back to 3.

That is fine for new rows, but a row that stays untouched for 2.1 billion transactions has a problem. Its xmin does not change, the circle turns, and one day the xmin is more than half a circle behind. It now compares as newer than every running transaction. The row would be treated as not yet committed and would vanish from every query (Demo: no freezing: rows vanish (what if)).

Freezing a row

The fix is to freeze old rows. Once every possible snapshot can see a row, VACUUM marks it frozen: since PostgreSQL 9.4 it sets both HEAP_XMIN_COMMITTED and HEAP_XMIN_INVALID in the tuple header (together they mean HEAP_XMIN_FROZEN) and keeps the old xmin for debugging. Older releases overwrote xmin with FrozenTransactionId (2). Either way a frozen row is older than every normal XID, forever. On the canvas a frozen row leaves the circle for the frozen shelf.

Which rows does a VACUUM freeze? Those with an xmin older than its FreezeLimit = next XID − vacuum_freeze_min_age (default 50 million). Young rows are left alone because they may still be updated or deleted soon, and freezing them would be wasted work. VACUUM FREEZE uses age 0 and freezes everything it can (Demo: VACUUM vs VACUUM FREEZE). Nothing can be frozen past the oldest running transaction's XID (OldestXmin).

relfrozenxid, datfrozenxid and age()

After a VACUUM that scans every page that is not all-frozen, PostgreSQL knows that no xmin in the table is older than some XID. It stores that in pg_class.relfrozenxid. pg_database.datfrozenxid is the oldest relfrozenxid in the database (the canvas has one table, so they are equal). age(relfrozenxid) is how many XIDs have been used since: the distance to trouble. When a VACUUM moves relfrozenxid forward, the age drops and every limit below moves with it.

The settings and the limits

From the oldest datfrozenxid the server computes four limits (SetTransactionIdLimit()). The canvas uses smaller numbers in the same order:

What	Real value	Canvas	What happens
vacuum_freeze_min_age	50,000,000	4	VACUUM freezes rows older than this
autovacuum_freeze_max_age	200,000,000	16	an anti-wraparound autovacuum is forced, even with autovacuum = off
xidWarnLimit	40,000,000 XIDs left	8 left	WARNING: database "app" must be vacuumed within N transactions
xidStopLimit	3,000,000 XIDs left	3 left	no new XIDs: every write fails
xidWrapLimit	datfrozenxid + 231 − 1	+ 31	where the oldest unfrozen xmin would flip

In Demo: anti-wraparound autovacuum the age reaches 16 and the autovacuum launcher forces a VACUUM public.orders (to prevent wraparound). It freezes the old rows and relfrozenxid jumps forward. Since PostgreSQL 14 the warning starts at 40 million XIDs left and the stop at 3 million (before: 10 million and 1 million). vacuum_failsafe_age (1.6 billion) also makes a VACUUM skip index cleanup and cost-based delays to finish sooner.

What stops freezing in production

Autovacuum usually keeps up. Wraparound trouble almost always means something holds OldestXmin back, so VACUUM runs but can't freeze or move relfrozenxid:

• a session left idle in transaction for days (Demo: idle transaction: warnings, then stop);
• a forgotten prepared transaction (PREPARE TRANSACTION, see pg_prepared_xacts);
• a stale replication slot, or a standby with hot_standby_feedback = on holding an old xmin;
• a table that autovacuum keeps cancelling or can't finish (huge table, too much throttling).

The fix is the same in every case: end what holds the horizon back, then VACUUM the oldest tables (Demo: fix it: end the idle txn, VACUUM). VACUUM does not need an XID, so it still runs after the stop.

-- how close is each database / table?
SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;
SELECT relname, age(relfrozenxid) FROM pg_class WHERE relkind = 'r' ORDER BY 2 DESC LIMIT 10;
-- who holds the horizon back?
SELECT pid, state, backend_xid, backend_xmin, xact_start FROM pg_stat_activity
 WHERE backend_xmin IS NOT NULL ORDER BY age(backend_xmin) DESC;

See also How PostgreSQL Runs a Query for xmin, xmax and VACUUM, MVCC and isolation levels for snapshots, pg_xact (CLOG) and hint bits for how commit status is looked up, and the free space map and visibility map for the all-frozen bit that lets VACUUM skip pages.

What the page leaves out

Real XIDs are 2³², not 64, and only writing transactions get one (readers use virtual transaction IDs). The page has one table; a real database has many, and datfrozenxid is the oldest of them, across all databases including template0. It leaves out vacuum_freeze_table_age (when a VACUUM becomes aggressive), the visibility map's all-frozen bit, page-level freezing in PostgreSQL 16, the separate multixact ID counter (relminmxid, autovacuum_multixact_freeze_max_age), truncation of pg_xact behind datfrozenxid, and the 64-bit "epoch" that pg_current_xact_id() adds for display.

References

PostgreSQL docs: Preventing Transaction ID Wraparound Failures

PostgreSQL docs: vacuum_freeze_min_age and related settings

PostgreSQL source: varsup.c (GetNewTransactionId, SetTransactionIdLimit)