Lesson 0004 · Storage & MVCC

Transaction IDs, freezing & wraparound

MVCC decides visibility by comparing 32-bit transaction IDs on a circular clock. Left unmaintained, that clock wraps and old rows vanish — so VACUUM has a third job you've now met twice in passing: freezing.

~10 minWarm-up recall + quiz + InnoDB contrastCheat sheet: here

Warm-up: recall 0002–0003 first

Closed book. These are the threads this lesson ties off.

Which process sets the "all-visible" bits an index-only scan relies on?

VACUUM maintains the visibility map. That was job two; this lesson is job three — freezing. Both are reasons a table needs vacuuming beyond just reclaiming dead tuples.

A row's visibility to your snapshot is decided using data stored in the:

Visibility lives in the heap tuple, not the index (that's why index scans touch the heap). This lesson names exactly what's in that header: the inserting transaction's XID.

We've said three times now that a tuple carries its own visibility information in the heap. Time to name it. Every tuple's header stores the transaction ID (XID) that inserted it (call it xmin) and, once deleted or updated away, the XID that expired it (xmax). MVCC visibility is just a comparison: a tuple whose inserting XID is "in the future" relative to my snapshot is not visible to me. — PostgreSQL Docs: "PostgreSQL's MVCC transaction semantics depend on being able to compare transaction ID (XID) numbers: a row version with an insertion XID greater than the current transaction's XID is 'in the future' and should not be visible to the current transaction."

The clock is circular — and only 32 bits

Here's the catch that has no InnoDB equivalent. XIDs are 32-bit, so the counter has about 4 billion values and then wraps back to zero. To cope, Postgres compares XIDs with modulo-2³² arithmetic: the space is a circle with no end, and from any point, half the circle is "past" and half is "future." — PostgreSQL Docs: "Normal XIDs are compared using modulo-2³² arithmetic. This means that for every normal XID, there are two billion XIDs that are 'older' and two billion that are 'newer'; another way to say it is that the normal XID space is circular with no endpoint."

THE XID CLOCK (circular, ~4 billion values) current XID │ ◀ 2 billion "past" ┃ 2 billion "future" ▶ (visible, committed) ┃ (not yet visible) ╲ ┃ ╱ ╲──────●──────╱ A very old row drifts clockwise. Once it falls MORE than 2 billion behind, it re-enters the "future" half — and suddenly becomes INVISIBLE. That is wraparound.

If a cluster runs past ~4 billion transactions without maintenance, rows inserted long ago cross that 2-billion horizon and "all of a sudden transactions that were in the past appear to be in the future — which means their output becomes invisible." — PostgreSQL Docs — transaction ID wraparound Silent, catastrophic apparent data loss.

The fix: freezing (VACUUM's third job)

The escape is to take the oldest rows off the circular clock entirely. VACUUM marks sufficiently-old, all-visible rows as frozen: a frozen tuple is treated as inserted by a special FrozenTransactionId that is always older than every normal XID, so it stays "in the past" forever regardless of wraparound. — PostgreSQL Docs: "VACUUM will mark rows as frozen… PostgreSQL reserves a special XID, FrozenTransactionId, which does not follow the normal XID comparison rules and is always considered older than every normal XID… such row versions will be valid until deleted, no matter how long that is."

The rule that ties the pillar together

Because of this, every table must be vacuumed at least once every two billion transactions, full stop. — PostgreSQL Docs: "it is necessary to vacuum every table in every database at least once every two billion transactions." This is the punchline of the whole storage arc: VACUUM has three jobs — (1) reclaim dead tuples [0002], (2) maintain the visibility map [0003], and (3) freeze old rows to prevent wraparound [this lesson]. Job three is why even a read-only, append-only table — one with zero dead tuples — still must be vacuumed. Bloat you can neglect for a while; wraparound you cannot.

You can't turn this off

Freezing is not optional and doesn't wait for the normal 20%-dead trigger. Once a table's oldest unfrozen XID reaches autovacuum_freeze_max_age — default 200 million transactions — Postgres launches an anti-wraparound autovacuum on it, even if autovacuum is disabled. — PostgreSQL Docs: "autovacuum is invoked on any table that might contain unfrozen rows with XIDs older than the age specified by… autovacuum_freeze_max_age. (This will happen even if autovacuum is disabled.)"

The famous outage

Ignore it long enough and Postgres protects your data by refusing to move the clock. First it warns, at 40 million transactions from the edge; then, under 3 million left, it stops accepting writes:

ERROR: database is not accepting commands that assign new
transaction IDs to avoid wraparound data loss in database "mydb"

"only read-only transactions can be started. Operations that modify database records… will fail." — PostgreSQL Docs — wraparound warnings & shutdown Recovery means a database-wide VACUUM, often single-user. This is one of the best-known ways to take a Postgres database down — and it is 100% a maintenance failure, not a bug.

Why InnoDB never taught you this word

You spent a whole course on InnoDB and never once worried about "wraparound." That's not luck — it falls out of the keystone. Postgres stores every version inline in the heap, tagged with a 32-bit XID, so those tags are a finite resource that must be recycled by freezing. InnoDB keeps old versions in the undo log and reconstructs them, using wider internal transaction identifiers and continuous purge — so there's no equivalent 32-bit horizon for you to babysit. Same problem (tell old versions from new), opposite mechanism, and only one of them hands you a clock to wind.

QuestionInnoDBPostgreSQL
Where is version identity kept?Undo log + wide internal IDs32-bit XID in each heap tuple header
Is the ID space a concern?Not operationallyYes — circular, wraps at ~4 billion
What prevents disaster?Background purge (automatic)Freezing by VACUUM
Forced maintenance trigger—autovacuum_freeze_max_age (200M)
Worst case if neglectedUndo bloat / purge lagRefuses writes to avoid data loss

Check yourself

From memory. Two items reach back on purpose.

Transaction ID wraparound threatens data because XIDs are:

XIDs are 32-bit and compared modulo-2³², so an unmaintained old row eventually crosses the 2-billion horizon into the "future" half and turns invisible. That's wraparound.

Freezing a tuple makes it:

A frozen tuple is treated as inserted by FrozenTransactionId, always older than every normal XID — so it stays visible regardless of where the clock wraps.

An append-only table with zero dead tuples still needs VACUUM because:

Reclaiming dead tuples isn't the only job. Freezing is mandatory to prevent wraparound, so even a table that never accumulates dead tuples must be vacuumed periodically.

When wraparound gets dangerously close, Postgres eventually:

Under ~3 million transactions from the edge it stops assigning new XIDs — writes fail, only reads continue — to prevent silent data loss. Recovery is a database-wide VACUUM.

Beyond reclaiming dead tuples, VACUUM's other two jobs are: (recall 0003 + this)

VACUUM's three jobs: reclaim dead tuples (0002), maintain the visibility map for index-only scans (0003), and freeze old rows to prevent wraparound (this lesson).
Optional — when you have an instance

A lab to run later. It exposes the XID clock directly:

SELECT txid_current();                     -- the current transaction's XID
SELECT xmin, * FROM some_table LIMIT 5;    -- each tuple's inserting XID

-- how close is each table to needing an anti-wraparound vacuum?
SELECT relname, age(relfrozenxid) AS xid_age
FROM pg_class WHERE relkind = 'r'
ORDER BY xid_age DESC LIMIT 10;            -- compare against 200M (freeze_max_age)

VACUUM (FREEZE, VERBOSE) some_table;       -- force-freeze and watch relfrozenxid advance

age(relfrozenxid) is the number that matters in production — when it climbs toward 200 million on a big table, an anti-wraparound vacuum is coming. Bring a surprising xid_age to your teacher.

Primary source — read this next

PostgreSQL Docs — 24.1.5 Preventing Transaction ID Wraparound Failures (the whole story, with the exact warning/shutdown thresholds). For the tuple header and xmin/xmax mechanics drawn out, Suzuki's "Internals of PostgreSQL," ch. 5 (Concurrency Control).

Where this goes next

That completes the storage & MVCC pillar: heap + never-update-in-place [0001] → dead tuples, VACUUM & HOT [0002] → the visibility map & index-only scans [0003] → XIDs, freezing & wraparound [0004]. You now know how a tuple decides whether it's visible to you — by comparing XIDs. Lesson 0005 pulls that thread up a level: a snapshot is just a captured set of XIDs, and that's all an isolation level really is — Read Committed vs Repeatable Read vs Serializable (SSI), the Postgres way.

Cheat sheet → Glossary → All lessons
I'm your teacher for this — if anything's fuzzy (how xmin/xmax actually gate a read, why the horizon is 2 billion and not 4, what an "anti-wraparound" autovacuum does differently, how this compares to InnoDB's purge), ask. Bring me an age(relfrozenxid) that surprised you and we'll read it together.