The naive way to make transactions safe is locks: readers lock out writers, writers lock out everyone. Run analytics on a busy OLTP database under that regime and everything stops. Every serious database abandoned that world for a stranger model: keep multiple versions of every row, give each transaction a consistent snapshot of the past, and let readers and writers proceed as if the other didn’t exist. The mechanics — visible right down to hidden columns you can SELECT — are where isolation levels, VACUUM, and several famous outages live.

Versions and Snapshots, Concretely

Postgres wears the machinery openly. Every row carries system columns: xmin (the transaction ID that created this version) and xmax (the one that deleted/superseded it, or 0):

sql
SELECT xmin, xmax, * FROM accounts WHERE id = 7;

An UPDATE never modifies in place — it writes a new row version (new xmin), stamps the old version’s xmax, and both coexist in the table. Each transaction starts with a snapshot: the set of transaction IDs committed as of its start. Visibility is then a pure function — a version exists for you if its creator committed before your snapshot and its deleter didn’t:

text
  txn 100 UPDATE:   [v1 xmin=90 xmax=100] [v2 xmin=100 xmax=0]
  txn 95 (older snapshot)  → still sees v1, no waiting
  txn 105 (newer snapshot) → sees v2

Nobody blocked anybody. Readers never block writers or vice versa — the property that makes long reports on live systems possible. Writers still block writers on the same row (someone must win), and that’s where isolation levels diverge: READ COMMITTED re-snapshots per statement and lets the loser proceed against the new version — with subtle mid-statement anomalies; REPEATABLE READ keeps one snapshot for the whole transaction and instead aborts the loser (“could not serialize access”), turning retry loops into a mandatory application pattern; Postgres’s SERIALIZABLE (SSI) goes further, detecting dangerous read-write dependency patterns and aborting those too. Same version store, increasingly strict referees.

The Bill: Dead Versions

Old versions become garbage the moment no live snapshot can see them — but something must notice and reclaim. Postgres’s answer is VACUUM (and autovacuum), and this is where MVCC stops being elegant theory and becomes operations:

  • Bloat: update-heavy tables accumulate dead versions faster than autovacuum’s defaults reclaim them; the table grows, cache hit rates fall, seq scans lengthen. pg_stat_user_tables.n_dead_tup is the gauge; tuning autovacuum per hot table is normal, not exotic.
  • Long transactions are poison: a snapshot held open for hours pins every version created since — VACUUM can reclaim nothing newer. One forgotten idle in transaction connection (or an abandoned replication slot) can bloat an entire database. Monitoring for old snapshots is table stakes.
  • Wraparound: transaction IDs are 32-bit; without periodic freezing of old tuples, the counter wraps and visibility math inverts. Autovacuum escalates to aggressive anti-wraparound mode on its own — the incidents you’ve read about (databases refusing writes to protect themselves) come from workloads that outran or disabled it.

MySQL’s InnoDB makes the opposite layout bet: rows are updated in place, old versions reconstructed from undo logs when an old snapshot needs them. Cleaner tables, no VACUUM — but long-running transactions bloat the undo history instead (history list length is InnoDB’s version of the same pathology), and reconstructing old versions costs CPU on read. Same MVCC contract, different place to keep the past; both charge rent for long snapshots.

Two design consequences worth carrying into schema work: MVCC makes hot single-row counters an anti-pattern (every increment is a new version + dead tuple — hence sharded counters or Redis in front), and it makes SELECT ... FOR UPDATE / advisory locks the honest tool when you genuinely need “read then write, atomically” — snapshots protect you from seeing chaos, not from write-write races on what you read.

Takeaways

  • MVCC = versions + snapshots: readers see a consistent past, writers append the future, neither waits for the other.
  • Postgres exposes it: xmin/xmax on every row; isolation levels are snapshot-refresh policies plus abort rules — retry loops are part of the contract above READ COMMITTED.
  • Garbage is the price: VACUUM/bloat (Postgres) or undo history (InnoDB), and long-lived snapshots pin garbage in both.
  • Wraparound freezing is why you never disable autovacuum, only tune it.
  • Snapshots don’t serialize read-modify-write: FOR UPDATE or explicit locks where the logic needs it.