MVCC: what a transaction physically is

Here's a situation your app creates constantly: one request is reading a group's expenses to render a screen while another request is editing one of those expenses. In a naive database, the reader and the writer fight over the row — one waits, holding a lock, while the other finishes. Do that at scale and your reads and writes serialize into a queue, and throughput dies.

Postgres almost never makes readers and writers wait for each other, and the way it pulls that off is one of the most important ideas in the whole system: MVCC, Multi-Version Concurrency Control. The trick, stated in one sentence: Postgres never changes a row in place — it keeps multiple versions of each row, and each query is shown whichever version its snapshot says it may see. (What a snapshot covers — one statement or a whole transaction — is a setting, and we'll watch both behaviors side by side below.) Everything in this lesson — why UPDATE is secretly an insert, why deleted data still takes up space, why VACUUM is a background job you have to care about — falls out of that one design choice. And it's built directly on the xmin/xmax tuple-header fields we planted back in the pages lesson.

An UPDATE is an insert in disguise

Start with the mental model you have to unlearn. You think UPDATE expenses SET amount = 500 WHERE id = 'x' finds the row and overwrites its amount. It does not. Physically, Postgres:

1. Writes a brand-new tuple (a whole new row version) with amount = 500 into a page — a fresh insert, exactly like D1 described.
2. Marks the old tuple as ended by stamping its xmax with the current transaction's ID.

The old version is still sitting there on the page. It hasn't been erased — it's just been labelled "valid up until transaction N." A DELETE is even simpler: it doesn't remove anything, it just stamps xmax on the row to say "this version ends here." Nothing is overwritten and nothing is immediately erased. This is the physical reality behind MVCC, and it's why the tuple header carries those two transaction-ID fields:

• xmin — the ID of the transaction that created this version. The version becomes visible once that transaction commits.
• xmax — the ID of the transaction that ended this version (via UPDATE or DELETE), or 0 if it's still live.

So every row version carries a visibility window: "alive from transaction xmin until transaction xmax." A row that's been updated three times leaves four physical tuples on disk (three dead, one live), each with its own window.

Snapshots: deciding which version you see

If multiple versions of a row exist, which one does your query see? The answer is your snapshot — essentially the set of transaction IDs that had already committed at the instant the snapshot was taken. A row version is visible to you if its xmin is in your snapshot (the creator committed before your snapshot) and its xmax is not (it either hasn't been deleted, or was deleted by a transaction that hadn't committed at snapshot time).

When the snapshot is taken is the part people get wrong, so pin it down now: at Postgres's default isolation level (Read Committed), every individual statement gets its own fresh snapshot — two SELECTs in the same transaction can see different data. Only at the stricter Repeatable Read level does the transaction take one snapshot at its first statement and hold it to the end. Both are demonstrated below.

That's the whole visibility rule, and it produces the magic: a reader doesn't need to lock anything. It just walks the row versions and picks the one its snapshot says is current. A writer creating a new version doesn't disturb the old one the reader is looking at. Hence the two invariants people quote about Postgres:

• Readers never block writers, and writers never block readers. They're working on different physical versions of the row.
• But two writers to the same row do block each other — only one transaction can hold the right to stamp xmax on a given live tuple at a time. Concurrency is free for reads; it's contended only when two writers target the same row.

Seeing it: two psql sessions, twice

This is best believed by watching it — twice, because the snapshot's lifetime is the variable. First, plain BEGIN, i.e. the default, Read Committed. Two psql sessions on a row with amount = 100 (numbers and interleaving illustrative):

-- SESSION A (Read Committed)         -- SESSION B

BEGIN;                                BEGIN;
SELECT amount FROM expenses
  WHERE id = 'x';
--  amount = 100
                                      UPDATE expenses SET amount = 500
                                        WHERE id = 'x';
                                      -- writes a NEW tuple (amount=500),
                                      -- stamps xmax on the old (amount=100)
                                      COMMIT;

SELECT amount FROM expenses
  WHERE id = 'x';
--  amount = 500   ← changed! A's second statement took a
--  FRESH snapshot, and B had committed by then.
COMMIT;

A's two reads disagree inside one transaction — that's a non-repeatable read, and Read Committed permits it by design: every statement sees the latest committed state. But notice what did not happen: A never waited for B, and at no point could A have seen B's in-flight, uncommitted 500 — a read that lands while B is mid-transaction is shown the old version. The versions did that, not locks.

Now the same interleaving with one line changed:

-- SESSION A (Repeatable Read)        -- SESSION B

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT amount FROM expenses
  WHERE id = 'x';   -- amount = 100
                                      BEGIN;
                                      UPDATE expenses SET amount = 500
                                        WHERE id = 'x';
                                      COMMIT;
SELECT amount FROM expenses
  WHERE id = 'x';
--  amount = 100   ← still 100! One snapshot, taken at A's
--  first statement, held for the WHOLE transaction — and the
--  old version still physically exists on the page to serve it.
COMMIT;

-- a new transaction now sees 500.

Same sessions, same timing, different answer — the only difference is whether the snapshot lives for a statement or for the transaction. And both behaviors ride on the same physical fact: the old amount = 100 tuple is still sitting in its page with a visibility window stamped on it, so Postgres can hand it to any snapshot that calls for it, nobody blocking anybody. Which lifetime you get, and which concurrency bugs each choice lets through, is exactly the subject of the isolation lesson.

The bill comes due: dead tuples and bloat

Never overwriting has an obvious cost. Every UPDATE and DELETE leaves dead tuples — old versions that no live transaction can see anymore but that still occupy space in their pages. A table that's updated heavily accumulates dead tuples faster than you'd guess: a row updated 100 times is 1 live version and 100 corpses, all taking up page space, all making scans read more pages to find the live rows. This is bloat — the table (and its indexes, which also gained an entry per new version) grows far larger than the live data justifies, and every query pays by reading more pages (straight back to D1's "I/O is page-granular" cost).

Why VACUUM exists: garbage collection

Something has to reclaim the space from dead tuples, and that something is VACUUM. Think of it exactly as a garbage collector for a language runtime: it scans for tuples that are dead and no longer visible to any running transaction, and marks their space free for reuse. This is why a Postgres database that's never vacuumed slowly rots — the disk fills with corpses and everything gets slower with no schema change to blame.

You rarely run VACUUM by hand because autovacuum — a background process — does it automatically, kicking in when a table has accumulated enough dead tuples. Two things worth knowing tie back to earlier lessons:

• VACUUM is also what maintains the visibility map from the B-tree lesson — the structure that decides whether an index-only scan can skip the heap. A well-vacuumed table has an up-to-date visibility map and gets fast index-only scans; a bloated, under-vacuumed one silently falls back to heap fetches. The lessons are connected: vacuum health directly affects index performance.
• Plain VACUUM reclaims space for reuse by the table but usually doesn't shrink the file on disk. Actually returning space to the OS needs VACUUM FULL, which rewrites the table and takes a heavy lock — a different, more disruptive tool.

The scary edge case: transaction ID wraparound

Now the part that has taken down real companies. Transaction IDs (xid) in Postgres are 32-bit — about 4 billion values — and they're handed out sequentially. Visibility is decided by comparing xids ("did this transaction happen before mine?"). But 4 billion isn't that many for a busy database, and a 32-bit counter wraps around. If comparisons were naive, once the counter wrapped, transactions from the distant past would suddenly look like they're in the future, and rows that should be visible would vanish — catastrophic silent data loss.

Postgres prevents this by freezing: VACUUM marks very old, still-live tuples with a special "frozen" flag meaning "this is in the past for everyone, forever," removing them from the wraparound comparison entirely. As long as vacuum keeps up, wraparound never bites. The danger is when it doesn't keep up — a huge write workload, autovacuum disabled or throttled too hard, or long-running transactions holding back the horizon. Postgres will start screaming warnings, and if the remaining xid budget runs out, it does the only safe thing: it shuts down writes and forces you to vacuum before it will accept new transactions. An availability outage, chosen deliberately over corruption.

HOT: the optimization that saves you from yourself

If every UPDATE writes a new tuple, and every index has an entry per tuple, then every update should also mean rewriting index entries — expensive write amplification (D2's warning). Postgres softens this with HOT (Heap-Only Tuple) updates. When you update a row without changing any indexed column, and there's free space on the same page for the new version, Postgres does a HOT update: it writes the new version on the same page and chains it off the old one, and crucially does not touch any index. The index entries still point at the old tuple's slot, and Postgres follows the chain on the page to reach the current version. No index writes, and the dead version can even be cleaned up by a lightweight page-level prune without a full vacuum. The practical lesson: updating a non-indexed column (like a status flag, if it isn't indexed) is far cheaper than updating an indexed one, and leaving a little free space per page (the fillfactor setting) buys you more HOT updates. It's the same theme as the whole lesson — the physical shape of your data determines the cost of touching it.