TIL: MVCC — why reads don't block writes

Databases like Postgres and MySQL/InnoDB don't make readers and writers fight over the same row. The mechanism is MVCC (multi-version concurrency control): instead of overwriting a row in place, an UPDATE or DELETE creates a new version of the row, tagged with the transaction that made it. The old version isn't destroyed — it just gets marked as expired.

In Postgres this is literally two hidden columns on every row: xmin (the transaction that created this version) and xmax (the transaction that expired it, if any). When your transaction reads a row, it checks those against its own snapshot — was xmin's transaction committed before I started, and is xmax either unset or from a transaction that hasn't committed yet? If so, this version is visible to you.

Here's what that looks like across three transactions touching the same row:

Row id=1, balance column
 
Time ──────────────────────────────────────────────────────────────►
 
 T1: BEGIN; INSERT balance=500;  COMMIT
     └─ creates Version A: [balance=500, xmin=T1, xmax=NULL]
 
                    T2: BEGIN  (snapshot taken here)
                    │
                    │           T3: BEGIN
                    │           │
                    │           │  UPDATE balance=400 WHERE id=1
                    │           │  └─ Version A gets xmax=T3 (expired)
                    │           │  └─ creates Version B: [balance=400, xmin=T3, xmax=NULL]
                    │           │
                    │           COMMIT
                    │
                    │  SELECT balance FROM accounts WHERE id=1;
                    │  └─ sees Version A (balance=500)
                    │     because T2's snapshot predates T3's commit
                    │
                    COMMIT
 
                                T4: BEGIN  (snapshot taken after T3 committed)
                                │
                                │  SELECT balance FROM accounts WHERE id=1;
                                │  └─ sees Version B (balance=400)
                                COMMIT
 
Row versions on disk right now:
  Version A: balance=500  xmin=T1 xmax=T3  (dead, waiting on VACUUM)
  Version B: balance=400  xmin=T3 xmax=NULL  (live)

T2 and T3 never blocked each other — they just read/wrote different versions of the same logical row. Version A sticks around, invisible to new transactions but still valid for T2's in-flight snapshot, until something reclaims it.

That something is VACUUM. Dead row versions don't disappear the moment xmax is set — deleting them immediately could break a transaction like T2 above, which might still need that old version for its snapshot.

Postgres runs VACUUM automatically in the background (autovacuum wakes up roughly once a minute and checks each table — once dead versions pass ~20% of a table's rows, that table gets vacuumed). For each dead version it finds, it applies one rule:

A dead version can be deleted once every transaction still running started after that version died.

The reasoning: a transaction only ever sees the world as it looked the moment it started. If a version died before you started, you were never going to ask for it — you'd already see its replacement. So once nobody left running remembers that version being alive, it's safe to throw away.

9:00  Version A created (balance=500)
 
9:05  Transaction X starts
      → X's view of the world: "as of 9:05"
 
9:10  Transaction Y updates the row → Version A dies, Version B created (balance=400)
9:11  Y commits
 
9:15  VACUUM runs, looks at dead Version A. Asks:
      "Is any currently-running transaction older than 9:10 (when A died)?"
      → Yes — X started at 9:05, before A died.
      → NOT safe to delete yet.
 
9:20  X finally commits and goes away.
 
9:21  VACUUM runs again, asks the same question:
      "Is any currently-running transaction older than 9:10?"
      → No — nothing running is that old anymore.
      → SAFE. Version A gets deleted, space reused.

That "start-time of the oldest transaction still running," the number every dead version gets checked against, has a name: Postgres calls it the xmin horizon (or OldestXmin). And the live list of every running transaction it's computed from is an internal structure called the ProcArray.

Two practical footguns that fall out of this:

• Table bloat: if VACUUM can't keep up with the write rate, dead versions pile up on disk and scans get slower.
• Long-running transactions block cleanup: a transaction left open for hours (a forgotten COMMIT, an app holding a connection idle-in-transaction) pins an old snapshot, which stops VACUUM from reclaiming anything newer than that snapshot — even on unrelated tables.

This is also why SELECT ... FOR UPDATE matters: it's opting out of "just read a snapshot" and into "lock the current version so nobody else can create a new one until I'm done." See pessimistic vs optimistic locking for how that gets used.