Lesson 6 · Internals & data structures

MVCC

Multi-Version Concurrency Control — the idea most Postgres interviews orbit.

Your win: explain how Postgres lets many transactions read and write concurrently without blocking each other — and name the one consequence (dead tuples) that sets up VACUUM.

The problem, and Postgres's answer

Many transactions hit the same rows at once. A naive database locks a row while it's being written, blocking readers. Postgres instead uses MVCC: writers create new versions rather than overwriting, so readers can keep seeing an older consistent version.1

The mechanism (from Lesson 5) An UPDATE or DELETE never overwrites a tuple. It writes a new tuple version and marks the old one dead. Each tuple carries xmin (the transaction that created it) and xmax (the transaction that deleted/superseded it).

Snapshots: what a transaction sees

Each transaction (or statement, at READ COMMITTED) works against a snapshot — the set of transactions committed at that instant. A tuple is visible if its xmin committed before the snapshot and its xmax hasn't. So two transactions can see different versions of the same row at the same time, each internally consistent.

row "email 01H8X" over time: tuple v1 xmin=100 xmax=140 ← visible to a snapshot taken at txid 120 tuple v2 xmin=140 xmax=null ← visible to a snapshot taken at txid 150 (v1 is now a DEAD tuple → VACUUM, Lesson 9)
The proverb to memorise Readers don't block writers, and writers don't block readers. (Two writers to the same row still serialize — the second waits.) That's how Postgres gets high concurrency without read locks.

Your code is doing MVCC constantly

Anchor Every soft delete — UPDATE emails SET deleted_at = now() WHERE … — is MVCC in action: it writes a new tuple for that email_id and marks the old one dead; the row isn't physically removed (which is also why your reads filter deleted_at IS NULL). And database.ExecInTxWithRetry retries on SerializationFailure (SQLSTATE 40001) — a conflict that only arises under stricter isolation built on MVCC (Part 5).
Cross-course echo A snapshot is a consistent point-in-time view without locking — the same instinct behind Kafka's per-consumer offset and Go's "share by communicating." Different systems, one idea: give each reader a stable view instead of a shared mutable one.
Read this next

PostgreSQL — Concurrency Control (MVCC) + Rogov Internals Part I

The official MVCC chapter, then Rogov's deep, diagram-led treatment of tuples, snapshots, and visibility.

→ postgresql.org/docs — MVCC
→ PostgreSQL 14 Internals — Part I (Isolation & MVCC)

Check yourself (from memory)

Q1. In MVCC, an UPDATE…

New tuple version, old one marked dead (via xmax). Never an in-place overwrite.

Q2. MVCC's key benefit is that…

Readers and writers don't block each other. (Same-row writers still serialize; disk grows with dead tuples — hence VACUUM.)

Q3. Each transaction reads from…

A snapshot (set of committed txids) decides which tuple version is visible — a stable point-in-time view.
Explain MVCC in two sentences, and give one consequence.
recall, then click to reveal
Under MVCC an UPDATE/DELETE never overwrites — it writes a NEW tuple version and marks the old one dead (each tuple carries xmin = creating txid, xmax = deleting txid), and every transaction reads from a consistent SNAPSHOT of which transactions had committed. So readers never block writers and writers never block readers (same-row writers still serialize) — concurrency without read locks. Consequence: dead tuple versions pile up and must be reclaimed by VACUUM. Our soft-delete UPDATE … SET deleted_at = now() creates a new version every time.
Want to see xmin/xmax live (SELECT xmin, xmax, * FROM …), or how this becomes the isolation levels of Part 5? Ask me.

1. PostgreSQL — Concurrency Control; The Internals of PostgreSQL — Concurrency Control.