CLOG, XID wraparound, and MultiXact: deep visibility internals

Postgres has 32-bit transaction ids. At 2 billion, they wrap — and old xmin values would suddenly appear newer than they are, making every old row invisible. Postgres solves this with freezing, autovacuum’s highest-priority task. Sentry hit this in 2017 and had to freeze tables manually under fire.

Snapshot visibility, bit by bit

The visibility decision Postgres makes on every tuple it scans is implemented in src/backend/utils/time/snapmgr.c and src/backend/access/heap/heapam_visibility.c. The key function is HeapTupleSatisfiesMVCC(HeapTuple tuple, Snapshot snapshot, Buffer buffer).

It walks a decision tree across t_infomask bits:

• HEAP_XMIN_COMMITTED / HEAP_XMIN_INVALID
• HEAP_XMAX_COMMITTED / HEAP_XMAX_INVALID

These hint bits short-circuit the common cases without touching the commit log. When the bits are not set (the row was committed but the hint bit has not yet been written by a later scan), the function consults the CLOG (commit log) to determine commit status, then writes back the hint bit so the next visit is fast.

Hint-bit propagation effect: a SELECT immediately after a long INSERT can be slower than the same SELECT a second later. The first scan touches every tuple’s CLOG entry and writes hint bits; the second scan reads the bits directly. This is not a bug — it is a one-time cost per new tuple.

Two different transactions cannot disagree on visibility because the rule is a pure function of the tuple’s xmin/xmax and the reader’s snapshot.

The CLOG (commit log)

Each committed-or-aborted transaction’s outcome is recorded in pg_xact/ (formerly pg_clog/): two bits per transaction id, packed into 8 KB pages. At 2 bits per transaction, one page covers 16,384 transactions.

The CLOG is heavily cached (the clog_buffers setting controls how many pages live in shared buffers). After a transaction commits, its CLOG bits are durable on disk thanks to WAL — the COMMIT record on disk is what makes the transaction’s effect visible to future readers.

This is why fsync = on and synchronous_commit = on are non-negotiable for durability: a commit acknowledged before the WAL hit disk could leave CLOG inconsistent on recovery.

Transaction id wraparound and freezing

Postgres transaction ids are 32-bit. At roughly 2 billion active transactions the counter would wrap, and old xmin/xmax values would suddenly appear newer than they are — a correctness disaster.

Postgres prevents this by freezing: once a tuple is old enough (older than autovacuum_freeze_max_age, default 200 million transactions), VACUUM rewrites its t_xmin to the special FrozenTransactionId value, which is always visible to any snapshot.

Wraparound prevention vacuum is the highest-priority autovacuum task — it runs even when autovacuum = off. On a database that approaches 2 billion transactions without enough freezing, Postgres refuses new connections and shuts down with:

database is not accepting commands to avoid wraparound data loss

Operations monitoring:

SELECT datname, age(datfrozenxid) FROM pg_database;

• Alert at 1 billion (age = 1,000,000,000)
• Page at 1.5 billion

Real outage: Sentry 2017 — autovacuum fell behind on a cluster; they ended up doing manual freezing under fire to recover.

MultiXact and shared row locks

When more than one transaction wants to hold a non-exclusive lock on the same row (e.g., two transactions both running SELECT ... FOR SHARE), Postgres cannot fit multiple xids into a tuple’s single t_xmax field. It instead allocates a MultiXactId — a 32-bit id pointing into pg_multixact/ that lists the participating transactions and their lock modes.

MultiXacts have their own wraparound (also 2 billion), tracked separately and frozen on a separate cadence. A workload that uses heavy SELECT ... FOR KEY SHARE (the implicit lock from foreign-key checks) can hit MultiXact wraparound long before XID wraparound.

SELECT datname, age(datfrozenxid), age(datminmxid) FROM pg_database;

Both ages need monitoring; the lower of the two determines your effective safety margin.