Course map & review plan

Every lesson's keystone on one page, the threads that connect them, and a spaced-review schedule to turn fluency into durable memory.

Living document10 lessons · 5 pillars · core completeContext: PostgreSQL current

Pillar 1 · Storage & MVCC (consolidated)

#	Lesson	Keystone — recall this first
0001	Heap & MVCC	Table = unordered heap; Postgres never updates in place — new version, old expired.
0002	VACUUM, autovacuum & HOT	Plain VACUUM reuses space (doesn't shrink); autovacuum ~20% dead; HOT = update with no indexed col changed + page room.
0003	Visibility map	Every index secondary → two-step heap fetch; the visibility map (set by VACUUM) earns an index-only scan.
0004	XIDs, freezing & wraparound	Visibility = compare 32-bit XIDs (circular); freezing is VACUUM's third job; neglect → write-refusal outage.

Lesson

Keystone — recall this first

0001

Heap & MVCC

Table = unordered heap; Postgres never updates in place — new version, old expired.

0002

VACUUM, autovacuum & HOT

Plain VACUUM reuses space (doesn't shrink); autovacuum ~20% dead; HOT = update with no indexed col changed + page room.

0003

Visibility map

Every index secondary → two-step heap fetch; the visibility map (set by VACUUM) earns an index-only scan.

0004

XIDs, freezing & wraparound

Visibility = compare 32-bit XIDs (circular); freezing is VACUUM's third job; neglect → write-refusal outage.

Pillar 2 · Transactions

#	Lesson	Keystone
0005	Snapshot isolation	A snapshot = a captured set of XIDs; a level just chooses when to take it. RC (default) / RR (40001 retry) / Serializable (SSI, kills write skew).

Lesson

Keystone

0005

Snapshot isolation

A snapshot = a captured set of XIDs; a level just chooses when to take it. RC (default) / RR (40001 retry) / Serializable (SSI, kills write skew).

Pillar 3 · The planner

#	Lesson	Keystone
0006	Reading EXPLAIN	Plan = tree of nodes; cost in page-fetch units; estimated vs actual rows is the #1 check; BUFFERS read = disk.
0008	Why the planner ignores your index	Cost-based: the index lost a comparison. Selectivity picks index/bitmap/seq; tune random_page_cost on SSD; stale stats → ANALYZE.

Lesson

Keystone

0006

Reading EXPLAIN

Plan = tree of nodes; cost in page-fetch units; estimated vs actual rows is the #1 check; BUFFERS read = disk.

0008

Why the planner ignores your index

Cost-based: the index lost a comparison. Selectivity picks index/bitmap/seq; tune random_page_cost on SSD; stale stats → ANALYZE.

Pillar 4 · Durability

#	Lesson	Keystone
0009	The WAL & checkpoints	Log before data; only WAL flushed at commit; REDO on recovery; checkpoints bound replay. WAL = redo-only (no undo — heap holds MVCC). synchronous_commit dial.

Lesson

Keystone

0009

The WAL & checkpoints

Log before data; only WAL flushed at commit; REDO on recovery; checkpoints bound replay. WAL = redo-only (no undo — heap holds MVCC). synchronous_commit dial.

Pillar 5 · Replication

#	Lesson	Keystone
0010	Streaming vs logical	Ship the WAL: physical (bytes → whole-cluster clone) vs logical (row changes → subset/cross-version). Async = failover loss window; sync = synchronous_commit over the network.

Lesson

Keystone

0010

Streaming vs logical

Ship the WAL: physical (bytes → whole-cluster clone) vs logical (row changes → subset/cross-version). Async = failover loss window; sync = synchronous_commit over the network.

The threads that tie it together

"Why VACUUM" runs through everything. VACUUM has three jobs — reclaim dead tuples (0002), maintain the visibility map for index-only scans (0003), and freeze old XIDs to prevent wraparound (0004) — and it also runs ANALYZE to keep the planner's estimates fresh (0006). Almost every Postgres operational story is a VACUUM story.

XIDs are the spine of MVCC. Tuples carry xmin/xmax XIDs (0004); visibility is an XID comparison against a snapshot (0004); a snapshot is a captured set of XIDs and an isolation level is when you take it (0005).

The keystone reaches the surface. "No clustered index → every index secondary" (0001) is why an index scan is two-step (0003), why non-HOT updates touch every index (0002), and why you see Index/Index-Only/Bitmap-Heap scan nodes in EXPLAIN (0006).

Spaced-review schedule

Retrieval beats re-reading. Do these closed-book, then check against the cheat sheets. Space them out — the gaps are the point.

When	Review
Now	The interleaved review: lesson 0007.
~3 days	Recite VACUUM's three jobs + the "never update in place → dead tuples → bloat → VACUUM" chain, from memory.
~1 week	Re-take the 0007 quiz cold. Re-derive: why is the wraparound horizon 2 billion, not 4?
~2 weeks	Explain, out loud, the three isolation levels as snapshot-timing choices + when each errors 40001.
~1 month	Read a real `EXPLAIN (ANALYZE, BUFFERS)` and narrate every number. Bring a surprising one to your teacher.

When

Review

Now

The interleaved review: lesson 0007.

~3 days

Recite VACUUM's three jobs + the "never update in place → dead tuples → bloat → VACUUM" chain, from memory.

~1 week

Re-take the 0007 quiz cold. Re-derive: why is the wraparound horizon 2 billion, not 4?

~2 weeks

Explain, out loud, the three isolation levels as snapshot-timing choices + when each errors 40001.

~1 month

Read a real EXPLAIN (ANALYZE, BUFFERS) and narrate every number. Bring a surprising one to your teacher.