MVCC - How PostgreSQL Does Concurrency

Day 10 taught you ACID isolation as a product promise. Day 20 taught you that VACUUM unlocks “Heap Fetches: 0.” Today: the mechanism underneath both - Multi-Version Concurrency Control. UPDATE never overwrites a row. It creates a new version. Readers see a snapshot. Dead versions pile up until VACUUM reclaims them.

Day 10’s isolation levels answer what you see under concurrency. Day 20’s visibility map answers whether an index-only scan can skip the heap. Both depend on the same engine: MVCC - Multi-Version Concurrency Control. Understanding MVCC turns “VACUUM matters” from a tip into a causal chain you can reason about under load.

The problem: readers and writers want the same row

Imagine a product page: one process updates price, a thousand processes read it. If the database used a single lock per row for every access, every read would queue for every write. That is how some older systems behaved - and how PostgreSQL behaves only when you ask for a lock with SELECT … FOR UPDATE.

The product requirement is different:

1. Writers must not block readers. A long UPDATE should not stall every SELECT.
2. Readers must not block writers. A long report query should not freeze checkouts.
3. Each transaction needs a consistent picture of the data - Day 10’s isolation promise.

MVCC solves this by storing multiple versions of each row. A reader never waits for a writer’s new version - it reads the version that was committed when its snapshot started.

The mechanism: UPDATE creates a new row version

In PostgreSQL, an UPDATE does not overwrite bytes in place. It:

1. Marks the old row version as expired (sets xmax = the updating transaction’s ID).
2. Inserts a new row version with the new values (xmin = that same transaction ID).
3. Leaves the old version on disk until no snapshot still needs it.

-- Conceptual view of one logical row after UPDATE
-- version 1: price=10.00  xmin=100  xmax=200   ← dead after txn 200 commits
-- version 2: price=12.00  xmin=200  xmax=null  ← live

UPDATE products SET price = 12.00 WHERE id = 42;

xmin / xmax are system columns. Every row version carries the transaction IDs that created and (optionally) deleted it. A snapshot decides: “is this version visible to me?”

Snapshots: isolation without waiting

Under Read Committed (PostgreSQL’s default - Day 10), each statement gets a fresh snapshot of committed data. Under Repeatable Read / Serializable, the snapshot is fixed for the whole transaction.

Visibility rule (simplified): a version is visible if its creating transaction committed before your snapshot, and it has not been deleted by a transaction that committed before your snapshot. You never see uncommitted data (no dirty reads in PostgreSQL - even “Read Uncommitted” behaves like Read Committed).

-- Session A                          -- Session B
BEGIN;                                BEGIN;
UPDATE products                       -- still sees old price
  SET price = 12 WHERE id = 42;       SELECT price FROM products WHERE id = 42;
-- not committed yet                  -- → 10.00  (A's uncommitted version invisible)

COMMIT;                               SELECT price FROM products WHERE id = 42;
                                      -- → 12.00  (new statement, new snapshot)

That is Day 10’s isolation made physical: not magic locks on every read - versioned rows + snapshot filtering.

Readers don’t block writers - proof with FOR UPDATE

MVCC’s non-blocking property is about plain reads. The moment you take a row lock, you wait like everyone else.

Real check on PostgreSQL 18.4 (Podman til-postgres): while transaction A held SELECT … FOR UPDATE on a row:

• Transaction B’s plain SELECT succeeded immediately - no lock wait (MVCC snapshot of the last committed version).
• Transaction B’s SELECT … FOR UPDATE with lock_timeout = 500ms timed out - lock wait, not MVCC.

-- A holds the row lock
BEGIN;
SELECT * FROM lock_demo WHERE id = 1 FOR UPDATE;

-- B can still read the committed version
SELECT * FROM lock_demo WHERE id = 1;   -- succeeds (MVCC)

-- B cannot take the same exclusive lock
SET lock_timeout = '500ms';
SELECT * FROM lock_demo WHERE id = 1 FOR UPDATE;
-- ERROR: canceling statement due to lock timeout

Interview phrasing: “PostgreSQL readers don’t block writers and writers don’t block readers - unless the reader asks for a lock.”

The cost: dead tuples and bloat

Every UPDATE and DELETE leaves dead versions. They still occupy pages. Indexes still point at them until cleaned. That is table bloat.

Real benchmark: 200,000-row table, mass UPDATE of every row (30-run medians, PostgreSQL 18.4):

• After UPDATE: 200,000 dead tuples, table 14 MB → 29 MB (doubled).
• After ordinary VACUUM: dead count → 0, but size stays 29 MB (pages free for reuse, file not shrunk).
• After VACUUM FULL: size back to 14 MB - exclusive lock, full rewrite. Use sparingly.

This is why autovacuum exists. Without it, every hot table grows a graveyard of versions and every scan pays for corpses it must skip.

VACUUM + visibility map = Day 20’s “Heap Fetches: 0”

Day 20: covering indexes enable Index Only Scans only when the visibility map says a page is all-visible. VACUUM sets those bits. Dirty pages after UPDATE force heap checks even when the index has every column.

Same 200K table, covering index on (status, created_at DESC) INCLUDE (id, total), query WHERE status = ‘pending’ … LIMIT 100, 30-run median:

Condition	Median latency	Heap Fetches	Buffers (sample)
Bloated (post-UPDATE)	0.227 ms	100	shared hit=206
After VACUUM	0.155 ms	0	shared hit=6

-- Bloated (visibility map dirty)
Index Only Scan using idx_mvcc_status_created
  Heap Fetches: 100     -- must verify each row in the heap
  Buffers: shared hit=206

-- After VACUUM
Index Only Scan using idx_mvcc_status_created
  Heap Fetches: 0       -- true index-only
  Buffers: shared hit=6

1.46× faster on this warm-buffer micro-query - but the durable claim is the causal chain: MVCC versions → dirty visibility map → forced heap fetches → VACUUM restores index-only scans. Absolute sub-ms numbers are machine-local; Heap Fetches 100→0 is the proof.

What breaks? - Anti-patterns

1. Long-running transactions that never commit. VACUUM cannot remove row versions still visible to an open snapshot. One abandoned BEGIN in a pool connection freezes cleanup for the whole database. Fix: set idle_in_transaction_session_timeout; monitor pg_stat_activity.

2. Assuming VACUUM shrinks the file. Ordinary VACUUM reclaims for reuse. Disk usage stays high until VACUUM FULL or a rewrite (CLUSTER, dump/restore). Fix: measure bloat; use FULL only offline / carefully.

3. Disabling autovacuum “for performance.” You trade short-term I/O for unbounded bloat and broken index-only scans. Fix: tune autovacuum thresholds; never turn it off globally.

4. Using FOR UPDATE for “consistency” on every read path. You throw away MVCC’s non-blocking property and reintroduce reader/writer queues. Fix: reserve row locks for true critical sections (Day 10 lost-update patterns).

How it connects

• Day 10 (ACID): Isolation levels are snapshot policies on top of MVCC versions.
• Day 20 (covering indexes): Visibility map is MVCC’s “is this page safe to trust from the index alone?” bitset - maintained by VACUUM.
• Day 22 (partial indexes): Fewer indexed rows still create dead index entries on UPDATE/DELETE of matching rows - VACUUM cleans indexes too.
• Day 23 (replication): Followers replay WAL that encodes these version changes. Hot standbys also run VACUUM-related cleanup under constraints - lag and long queries interact with cleanup.
• Day 8 (pooling): Leaked transactions in a pool are the #1 real-world MVCC footgun.

Transfer questions

1. Your covering index shows Heap Fetches: 5000 after a batch job. What did the batch do to the visibility map, and what operation restores true index-only scans?
2. A dashboard query is “read only” but uses SELECT … FOR UPDATE “just in case.” How does that change concurrency behavior under MVCC?
3. After a huge UPDATE, n_dead_tup is zero post-VACUUM but pg_relation_size is still doubled. Is that a bug? What command would shrink the file, and what is the tradeoff?

What you should be able to do

• Explain that PostgreSQL UPDATE/DELETE create new versions instead of overwriting in place.
• Connect snapshots to isolation (Day 10) without claiming readers always take locks.
• State why plain SELECT does not wait on FOR UPDATE holders, but another FOR UPDATE does.
• Read n_dead_tup / table size / Heap Fetches as MVCC health signals.
• Distinguish VACUUM (mark reusable + fix visibility map) from VACUUM FULL (rewrite + reclaim disk).

Your teach step

Close this lesson. Write the “Explain like I’m 10” and the “60-second LinkedIn version” from memory. Focus on: UPDATE creates versions, readers use snapshots, dead tuples need VACUUM, and why Day 20’s Heap Fetches depend on that cleanup. Post it, and paste the link.

Questions? Ask the agent - unclear snapshots, autovacuum tuning, or how this shows up in EXPLAIN are all fair game.