How PostgreSQL delivers ACID: WAL and MVCC row versions

The editor never splices a reel in place. Each recut is printed as a new reel, and the old reel is labelled with the cut that replaced it. Screening rooms run all day, and the vault clerk recycles old reels.

In the cutting room	In Postgres
a recut printed as a new reel; the old reel labelled "replaced by cut 7"	an UPDATE's new version; xmax on the old one (step 2)
each reel's label: the cut that made it, the cut that replaced it	xmin and xmax (step 3)
a screening shows the reels that were final when it started	a query's snapshot (step 4)
the editor recuts during a screening; neither stops for the other	readers and writers do not block (step 5)
an abandoned cut's reel goes straight to the bin	ROLLBACK leaves dead versions (step 6)
every splice first written in the edit log in the fireproof safe	the WAL (step 7)
the clerk recycles replaced reels no running screening could show	VACUUM (steps 8–9)
a screening room left running all night with the door locked	a forgotten open transaction pinning versions (step 10)

In real terms: a change adds a version and labels the old one; each query picks versions by its snapshot, and VACUUM reclaims the ones no open snapshot or writer can still need.

Breaks down when: a clerk can look into a screening room and see which reels are on the projector. VACUUM cannot see what an open transaction will read next, so it keeps every version that transaction might still see. Wrong inference: "VACUUM keeps only the versions an open transaction has actually read." It keeps them all, in every table.

Contrasting case: one tape edited while it is on air: every viewer waits for each splice. That is a lock without versions, which Postgres uses only when asked (FOR UPDATE, in the lost-update pack).

4.1 ExamHall: the report that never committed

EXH-1810: during an ECON-201 sitting, the proctor console's live feed updated five candidates' status rows twelve times each. The nightly integrity report had opened a transaction, written its run row, and hung on an export without committing.

EXH-1810 report open (idle in transaction, holds a transaction id: true) after 60 status updates
  VACUUM tuples: 0 removed, 65 remain, 60 are dead but not yet removable
report transaction ended
  VACUUM tuples: 60 removed, 5 remain, 0 are dead but not yet removable

The report had written, so every version that died after that stayed (steps 9–10), although it never read candidate_status. Ending it freed all 60. MR !2010 makes the report commit its run row at once, and gives the report_bot role idle_in_transaction_session_timeout, so the server ends a forgotten transaction itself:

MR !2010 report_bot idle_in_transaction_session_timeout = 1s; the same hang, 60 more updates
  server ended the idle transaction: true · VACUUM tuples: 60 removed, 0 are dead but not yet removable

4.2 Proof domain, e-commerce catalog: one price change on the page

A catalog admin changes kettle-3's price from 40 to 45. The script reads the table's page with pageinspect at each moment; transaction ids are shown as T1, T2.

kettle-3 inserted          page: (0,1) xmin T1 xmax 0
UPDATE sent, not committed page: (0,1) xmin T1 xmax T2 | (0,2) xmin T2 xmax 0
  WAL insert position moved forward during the UPDATE: true
COMMIT                     page: (0,1) xmin T1 xmax T2 | (0,2) xmin T2 xmax 0 · SELECT returns price 45
VACUUM                     page: (0,2) xmin T2 xmax 0 · tuples: 1 removed, 1 remain

The UPDATE wrote a second version and stamped T2 into the first one's xmax (step 2). The WAL grew before anything committed (step 7). After COMMIT a query returns only the new price, but both versions stay on the page until VACUUM removes the dead one (step 8).