xmin, xmax, and a Row's Lifetime

Every row version carries the id of the transaction that created it and the id of the transaction that expired it. Every transaction that writes anything is handed a monotonically increasing 32-bit id from a cluster-wide counter. A commit log on disk records which of those ids committed and which aborted. Those three facts are the entire machinery of MVCC.

Chapter 5 laid the header fields out as bytes on a page. What makes this topic worth an hour at a real terminal is that the same fields are selectable from SQL, in any session, without an extension. Two psql windows and one UPDATE are enough to watch a row version be born and another be marked for death, and the mental model that comes from seeing it does not go away.

Transaction Ids, and Who Gets One

An id is not allocated when a transaction starts. It is allocated the first time the transaction writes. Until then the transaction is identified by a virtual id built from the backend's process number and a counter local to that backend, printed as 4/12532 in pg_locks, and it consumes nothing that the rest of the cluster has to account for. This is not a micro-optimization. It is the reason the analytics dashboard on pg-replica-a, which runs thousands of aggregates a day and writes nothing, advances the cluster's counter by exactly zero.

BEGIN;
SELECT pg_current_xact_id_if_assigned();   -- NULL: nothing written yet

SELECT count(*) FROM inventory;            -- still nothing written
SELECT pg_current_xact_id_if_assigned();   -- still NULL

UPDATE inventory SET on_hand = on_hand
 WHERE product_id = 4471;                  -- now it writes
SELECT pg_current_xact_id_if_assigned();   -- 487355026
COMMIT;

The two functions differ in exactly that respect: pg_current_xact_id() assigns an id on the spot if the transaction does not have one, while pg_current_xact_id_if_assigned() reports the truth and returns null when there is nothing to report. Reach for the second one when investigating, because the first changes the thing you are measuring. Both return the 64-bit xid8 form that carries the wraparound epoch alongside the 32-bit value stored in the row.

Reading the Fields From SQL

Every table has system columns that do not appear in SELECT * but can be named explicitly. Three of them matter here: xmin, the transaction that created this version; xmax, the transaction that expired it, or zero while it is live; and ctid, the physical address of the version as a page number and slot. Query them alongside the ordinary columns and MVCC stops being an argument about semantics.

-- session 1
SELECT xmin, xmax, ctid, on_hand FROM inventory
 WHERE product_id = 4471 AND warehouse_id = 2;
--   xmin    | xmax |  ctid   | on_hand
-- 487355012 |    0 | (17,3)  |      12

-- session 2
BEGIN;
UPDATE inventory SET on_hand = on_hand - 1
 WHERE product_id = 4471 AND warehouse_id = 2;
-- not committed yet

-- session 1 again: unchanged, because session 2 has not committed
-- 487355012 |    0 | (17,3)  |      12

-- session 2 commits, then session 1 re-queries
--   xmin    | xmax |  ctid   | on_hand
-- 487355035 |    0 | (17,48) |      11

The row the second query returns is a different physical object. Its xmin is the updating transaction's id, its ctid points at a different slot, and its on_hand is one lower. The version that used to answer this query is still on the page at (17,3) with its xmax now set to 487355035, invisible to any snapshot taken after that commit and waiting for vacuum. Nothing was modified in place; a second row was written and the first was retired.

One reading habit is worth forming immediately. A non-zero xmax does not mean "deleted". It means some transaction claimed this version — which includes a transaction that expired it and then rolled back, and includes a plain SELECT … FOR UPDATE that only locked it. Which of the three happened is recorded in pg_xact, not in the row.

The Commit Log and Hint Bits

An xmin of 487355035 tells you which transaction wrote the version. It does not tell you whether that transaction committed, and the tuple has no room for an answer that was not known when it was written. The answer lives in the commit log under pg_xact in the data directory, two bits per transaction, and every visibility check that meets an unresolved id has to go and look. pg_xact_status() exposes the same lookup from SQL, returning in progress, committed or aborted for an id recent enough to still be recorded.

Going to the commit log for every row of every scan would be intolerable, so the first reader that resolves an id writes the answer back into the tuple's info-mask — the hint bits Chapter 5 read off the page, and the reason a plain SELECT produces write I/O on a table nobody has modified. The half that belongs here is what the arrangement does to the price of a visibility check. A tuple that has already been hinted is decided from bytes the scan is holding anyway. One that has not costs a lookup in pg_xact, which is a memory read for a recent id and a disk read for an old one, so two scans of the same rows can differ by an order of magnitude with nothing about the query or the data having changed.

Rollback Leaves Rows Behind

A rolled-back INSERT still wrote its tuples. Nothing goes back to erase them: the commit log records the abort, every visibility check that meets those rows sees a creating transaction that failed, and the rows are skipped. They occupy their bytes until vacuum removes them like any other dead version.

This is why an aborted 4,000-row warehouse batch costs disk even though it changed nothing anyone can see, and why ROLLBACK on a transaction that touched a million rows returns instantly instead of grinding through an undo log. Postgres makes rollback nearly free at the moment it happens and settles up later, which is the exact inverse of the engines this book keeps comparing it to. Those 4,000 tuples stay where they were written until a vacuum reaches them.

Subtransactions and Savepoints

SAVEPOINT starts a subtransaction, and so does every PL/pgSQL block with an EXCEPTION clause, whether or not an exception is ever raised. A subtransaction that only reads gets a virtual id; one that writes is assigned its own id, called a subxid, and the parent's id is always lower than any of its children's. The outcome of the child is recorded separately, which is what lets the parent survive a failed statement.

The cost is real and sharp-edged: up to 64 open subxids are cached in shared memory per backend, and past that point every visibility check on those rows has to consult pg_subtrans on disk, with the I/O that implies. A loader that wraps each row of a 4,000-row batch in its own exception handler creates 4,000 open subtransactions in one transaction and falls off that cliff sixty-four rows in. The symptom looks like a locking problem: high latency, backends waiting, no obvious lock. Handling errors at the batch level and re-running the whole batch keeps the open subxid count at one.

A Counter That Wraps

The id stored in a row version is 32 bits, and the counter wraps every four billion transactions. Comparison is modulo arithmetic rather than absolute: at any moment roughly two billion ids count as being in the past and two billion in the future, which means an id left untouched for long enough would eventually appear to be in the future and its rows would vanish from every snapshot. The 64-bit xid8 type used by the SQL-level functions carries an epoch alongside the 32-bit value and does not wrap during the life of an installation, but the bytes on the page are still 32 bits.

That is not an abstraction leak to shrug at. It is the reason freezing exists, the reason vacuum has a second job unrelated to space, and the reason Chapter 7 ends with a cluster refusing writes to protect itself. Only transactions that write consume ids, so the arithmetic that matters for Cartwheel is the checkout rate and the ingest rate, not the dashboard's query count. A reporting replica running flat out moves the cluster no closer to the boundary.