Transactions and MVCC

XID wraparound and freezing

Transaction IDs are 32-bit counters that go round in a circle. Old rows must be "frozen" before the counter comes round again, or the database stops accepting writes to protect your data.

In plain words

Every writing transaction gets a number, the transaction ID (XID), and every row remembers the XID that created it. XIDs are only 32 bits, about 4.3 billion values, so the counter is treated as a circle: for any transaction, the 2.1 billion numbers behind it are "the past" and the 2.1 billion ahead are "the future".

That works only if no row is older than 2.1 billion transactions. Otherwise its XID would suddenly look like it is in the future, and the row would become invisible. To prevent this, VACUUM freezes old rows: it marks them as "older than everybody", which needs no number at all.

Autovacuum takes care of this by itself. It goes wrong when freezing cannot keep up or is blocked: a transaction left open for days, a forgotten replication slot or prepared transaction, or a huge table that autovacuum never finishes. PostgreSQL then warns, and as a last resort it refuses new writes until someone runs VACUUM. Try it: turn on the old transaction and raise the XID rate.

For DBAs and operators

Ages and cutoffs

Each table has pg_class.relfrozenxid: every XID older than it is frozen. Each database has datfrozenxid, the minimum over its tables. age(x) is the number of XIDs since x.

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

SELECT c.oid::regclass, age(c.relfrozenxid),
       pg_size_pretty(pg_total_relation_size(c.oid))
  FROM pg_class c WHERE c.relkind IN ('r','m','t')
 ORDER BY 2 DESC LIMIT 10;

vacuum_freeze_min_age (50M): when VACUUM visits a page holding an XID older than this, it freezes the page (all eligible tuples on it together); since PostgreSQL 16 it may also freeze younger pages opportunistically when that is cheap.
vacuum_freeze_table_age (150M): above this age, a vacuum is aggressive: it reads every page that is not all-frozen, instead of skipping all-visible ones.
autovacuum_freeze_max_age (200M): autovacuum forces a vacuum to prevent wraparound, even with autovacuum off for the table. It is not cancelled by lock conflicts.
vacuum_failsafe_age (1.6B): VACUUM drops its cost delay, skips index vacuuming and truncation, and just freezes as fast as it can. It logs WARNING: bypassing nonessential maintenance of table "app.public.events" as a failsafe after 0 index scans DETAIL: The table's relfrozenxid or relminmxid is too far in the past.

The real messages (PostgreSQL 18)

When fewer than 40 million XIDs remain, every XID assignment warns:

WARNING: database "app" must be vacuumed within 39985967 transactions HINT: To avoid transaction ID assignment failures, execute a database-wide VACUUM in that database. You might also need to commit or roll back old prepared transactions, or drop stale replication slots.

At 3 million remaining, writes stop:

ERROR: database is not accepting commands that assign new transaction IDs to avoid wraparound data loss in database "app" HINT: Execute a database-wide VACUUM in that database. You might also need to commit or roll back old prepared transactions, or drop stale replication slots.

Read-only queries keep working. Recovery: first remove what holds the horizon back (check pg_stat_activity.backend_xmin, pg_prepared_xacts, pg_replication_slots.xmin/catalog_xmin, standbys with hot_standby_feedback), then run plain VACUUM on the oldest tables (or database-wide, for example vacuumdb --all --jobs=N). Do not use VACUUM FULL (it needs an XID) or VACUUM FREEZE (more work than needed). Single-user mode is no longer required or recommended.

Eager freezing (PostgreSQL 18)

Normal vacuums skip all-visible pages, so unfrozen-but-all-visible pages pile up until one huge aggressive vacuum must read them all. Since 18, a normal vacuum also eagerly scans some all-visible pages to freeze them, giving up in a region once too many attempts fail; vacuum_max_eager_freeze_failure_rate (0.03, 0 disables, per-table option too) caps the failures as a fraction of the table. Log lines show it as "N eagerly scanned".

Multixacts

MultiXact IDs (row locks shared by several transactions, see locking) are also 32-bit and wrap the same way: relminmxid, mxid_age(), autovacuum_multixact_freeze_max_age (400M) and the matching messages "must be vacuumed before … more MultiXactIds are used". The member storage can also run out: autovacuum reacts to members usage earlier than the age suggests.

Since PostgreSQL 13 the 64-bit "full" XID (epoch + XID) is visible via pg_current_xact_id(), but tuples still store 32 bits, so freezing remains necessary.

For developers: inside the source

Where it lives

varsup.c: XID assignment and the limits. GetNewTransactionId() compares the new XID with the limits; SetTransactionIdLimit() computes them.
vacuum.c: cutoffs (vacuum_get_cutoffs(), vacuum_xid_failsafe_check()), vac_update_datfrozenxid() and vac_truncate_clog().
vacuumlazy.c (scan strategy, eager scanning, failsafe), heapam.c and pruneheap.c (freezing), multixact.c (the multixact twin).

Key data structures

TransamVariablesData (shared, protected by XidGenLock): nextXid (a FullTransactionId), oldestXid, xidVacLimit, xidWarnLimit, xidStopLimit, xidWrapLimit, oldestXidDB.
struct VacuumCutoffs: OldestXmin, FreezeLimit, MultiXactCutoff and the table's current relfrozenxid/relminmxid.
HeapPageFreeze: per-page freeze state; FreezePageRelfrozenXid and NoFreezePageRelfrozenXid track the oldest XID left behind either way, which becomes the new relfrozenxid.
A frozen xmin is not a special XID: it is HEAP_XMIN_FROZEN (HEAP_XMIN_COMMITTED | HEAP_XMIN_INVALID) in t_infomask, tested by HeapTupleHeaderXminFrozen().

How the limits are computed

xidWrapLimit = oldest_datfrozenxid + (MaxTransactionId >> 1);
xidStopLimit = xidWrapLimit - 3000000;
xidWarnLimit = xidWrapLimit - 40000000;
xidVacLimit  = oldest_datfrozenxid + autovacuum_freeze_max_age;

Past xidVacLimit, GetNewTransactionId() signals the postmaster (PMSIGNAL_START_AUTOVAC_LAUNCHER) once every 65536 XIDs, which is how anti-wraparound vacuums start even with autovacuum = off. Past the warn and stop limits it raises the WARNING and ERROR quoted above.

Call flow

heap_vacuum_rel() → vacuum_get_cutoffs() (aggressive or not) → heap_vacuum_eager_scan_setup() (PostgreSQL 18 eager scanning) → lazy_scan_heap() → lazy_scan_prune() → heap_page_prune_and_freeze(), which uses heap_prepare_freeze_tuple() and heap_freeze_prepared_tuples() and WAL-logs pruning and freezing in one record. Every few pages lazy_check_wraparound_failsafe() can switch on the failsafe. At the end vac_update_relstats() stores the new relfrozenxid; vac_update_datfrozenxid() → vac_truncate_clog() truncates pg_xact, commit_ts and multixact, then calls SetTransactionIdLimit() and SetMultiXactIdLimit().

Experiment

src/test/modules/xid_wraparound provides consume_xids() and consume_xids_until() to burn XIDs quickly; its TAP tests (001_emergency_vacuum.pl, 002_limits.pl, 003_wraparounds.pl, 004_notify_freeze.pl) only run with PG_TEST_EXTRA=xid_wraparound. Use pg_visibility's pg_check_frozen() and pg_visibility_map_summary() to inspect results. SetTransactionIdLimit() logs the wrap limit at DEBUG1.

Parameters that shape this

autovacuum_freeze_max_age vacuum_freeze_min_age vacuum_freeze_table_age vacuum_failsafe_age vacuum_max_eager_freeze_failure_rate autovacuum_multixact_freeze_max_age vacuum_multixact_freeze_min_age vacuum_multixact_freeze_table_age vacuum_multixact_failsafe_age