Postgres Internals — MVCC, Isolation, Replication

Why this session matters

Part of a 36-topic learning series on engineering, ML, and LLM systems. Each session is a 90-minute deep-dive on one topic — designed so anyone can pick it up cold. Every two topics are followed by a revision session with recall prompts and hands-on drills.

Session 08 · DE track 🐘

Postgres is the workhorse database of modern SaaS. Knowing its internals — MVCC (multiversion concurrency control), transaction isolation levels, WAL and replication, indexes (btree/GIN/GiST/BRIN), query planner — is what separates 'wrote some SQL' from 'runs a 10 TB production Postgres.' This session goes deep.

Pre-read (30 min before session)

Watch 1–2 of these before the deep-dive:

• Postgres MVCC Explained — Bruce Momjian
• Postgres Replication — CMU DB Group
• Isolation Levels — Martin Kleppmann

Then skim the Postgres MVCC section.

Deep-dive (90 min)

1. MVCC (25 min)

How Postgres avoids read locks: every row has xmin/xmax; each transaction sees rows valid at its snapshot. Visibility rules. Consequences: no read blocks write, but bloat needs VACUUM. Long-running transactions kill autovacuum → table bloat.

2. Isolation levels (20 min)

Read Committed (default) — sees committed at each statement. Repeatable Read — snapshot at first read. Serializable — uses SSI (predicate locks). What each level prevents: dirty read (all), non-repeatable read (RR+), phantom read (SER only). Concurrency anomalies: write skew, lost update.

3. WAL + replication (25 min)

Write-Ahead Log = the source of truth. Streaming replication (async by default; sync for HA). Logical replication (per-table, cross-version, tables → topics). Point-in-time recovery. pg_stat_replication for lag monitoring.

4. Indexes + query planner (20 min)

Btree (default), Hash (special use), GIN (jsonb, full-text, arrays), GiST (geometric, ranges), BRIN (huge sequential data). Partial indexes for skewed data. Expression indexes. Covering indexes (INCLUDE clause, PG11+). ANALYZE frequency and planner stats.

Reading list

• The Internals of PostgreSQL — Hironobu Suzuki (free online)
• PostgreSQL 14 Internals — Egor Rogov (free PDF)
• Bruce Momjian's talks on MVCC and WAL

Research links

• Aurora vs Postgres — deep architectural diff
• pg_stat_statements deep dive

Hands-on drill

Spin up Postgres locally, open two psql sessions, both BEGIN;. In session A: UPDATE accounts SET balance = balance - 100 WHERE id = 1;. In session B (before A commits): SELECT balance FROM accounts WHERE id = 1; — under Read Committed vs Repeatable Read, what do you see? Now change both to SERIALIZABLE and try a write-skew scenario.

Post-session checklist

• Can you explain how MVCC lets Postgres avoid read locks in 60 seconds?
• Can you explain the difference between Read Committed and Repeatable Read in 60 seconds?
• Can you explain what WAL is and why it matters in 60 seconds?
• Did you complete the hands-on drill above?
• Did you write 3 flashcards for tomorrow's recall?
• What's the one thing you'd want to revisit in the next revision session?

What's next

Session 09 continues the series. See the hub page for the full sequence and revision pattern.