Six lessons across three areas, mixed on purpose. Interleaving and retrieval are what convert fluency into durable memory — so this whole lesson is a test, not a read.
You've built three things: the storage & MVCC pillar
(0001–0004),
the transactions thread (0005),
and the planner thread (0006). This
review mixes them deliberately — the jumps between topics are the desirable difficulty.
Answer everything from memory; the course map has
the keystones if you want to check after.
Part 1 — Cross-pillar quiz
Mixed order, no scaffolding. Feedback is immediate.
A table only ever UPDATEd keeps growing on disk. The direct cause is:
Never-update-in-place (0001): each UPDATE leaves the old version as a dead tuple until VACUUM reclaims the space. That's bloat.
Two SELECTs in one transaction return different rows. The isolation level is:
Read Committed (0005) takes a fresh snapshot per statement, so a concurrent commit between the two is visible to the second. RR/Serializable hold one snapshot.
In EXPLAIN, rows=5 on a plain (non-ANALYZE) plan is:
Plain EXPLAIN (0006) shows the planner's estimate of rows emitted after filtering — not scanned, not actual. Actuals need ANALYZE.
An append-only table with zero dead tuples still needs VACUUM in order to:
Freezing is VACUUM's third job (0004). Even with no dead tuples, old XIDs must be frozen or they eventually cross the wraparound horizon.
An Index Only Scan shows Heap Fetches: 900. The fix is usually to:
Heap fetches happen on pages whose all-visible bit isn't set (0003). VACUUM maintains the visibility map, so vacuuming drives Heap Fetches toward zero.
Under Repeatable Read, updating a row a concurrent txn already changed:
PG's RR (0005) is optimistic: it can't touch a row changed after its snapshot, so it raises "could not serialize access due to concurrent update" (40001). Gap locks are MySQL's approach.
The pointer a Postgres index entry stores into the heap is the:
Every index is secondary and points at a physical tuple by ctid (0001/0003). Storing the PK as the pointer is InnoDB's design, not Postgres's.
Estimated rows are wildly off from actual in an EXPLAIN ANALYZE. First move:
Estimates come from statistics gathered by ANALYZE, which autovacuum also runs (0006 ↔ 0002). Stale stats are the usual cause — refresh them first.
The anomaly Serializable prevents but Repeatable Read allows is:
RR/snapshot isolation (0005) already blocks dirty and non-repeatable reads and phantoms, but permits write skew. Serializable's SSI watchdog catches it.
In Buffers: shared hit=200 read=48, the slow part is:
hit = served from cache (fast); read = missed and went to disk (slow). On a cold cache, read is what hurts (0006).
Part 2 — Diagnose it
Real-shaped scenarios. Think through your answer before expanding each one —
the effort is the point.
1 · A dashboard query on a busy table used to be instant; now it's slow. EXPLAIN (ANALYZE, BUFFERS) shows an Index Only Scan with Heap Fetches: 412000 and high read=. What's happening, and what do you do?
The table is churning faster than autovacuum keeps up. Heavy writes keep clearing pages' all-visible bits, so the "index-only" scan is actually visiting the heap 412k times (0003), and those visits miss the cache (read=). The index-only optimization has quietly degraded to a regular index scan. Fix: make autovacuum more aggressive on this table (lower autovacuum_vacuum_scale_factor, per-table — from 0002) so the visibility map stays fresh, or vacuum it manually. This is the 0002↔0003 link in the wild: index-only scans are only fast on a well-vacuumed table.
2 · A nightly batch job at Repeatable Read intermittently fails with ERROR: could not serialize access due to concurrent update. The dev wants to "fix the bug." Is it a bug?
No — it's the model working as designed (0005). Repeatable Read holds one snapshot for the whole transaction, so if another committed transaction changed a row this job then tries to update, PG refuses rather than silently clobbering — that's the 40001 optimistic-concurrency contract. The correct handling is a retry loop: catch SQLSTATE 40001 and re-run the whole transaction from the top. (A dev coming from InnoDB, where RR blocks on gap locks instead of erroring, wouldn't have needed one — the migration trap.)
3 · Monitoring pages you: database is not accepting commands to avoid wraparound data loss. Writes are failing. What happened, and how do you recover?
The database's oldest unfrozen XID crept to within ~3 million of the wraparound point and Postgres stopped assigning new XIDs to protect your data (0004) — reads still work, writes fail. Something blocked freezing for a long time (autovacuum off/overwhelmed, a very long-running transaction, or an unvacuumable table). Recover by running VACUUM (freeze) on the offending database/tables — often in single-user mode — to advance relfrozenxid back below the danger line. Prevent by watching age(relfrozenxid) against autovacuum_freeze_max_age (200M) and keeping autovacuum healthy.
Part 3 — Synthesis
Say it in one breath
Out loud, in under a minute: "Because Postgres never updates a row in place…" —
and derive the rest: dead tuples, VACUUM's three jobs, why every index is secondary, why an
index scan touches the heap, what the visibility map buys, how XIDs power both freezing and
snapshots, and why an isolation level is just snapshot timing. If any link breaks, that's your
next re-read. This single chain is the whole course so far.
Part 4 — The wisdom mile: test it on real people
You've got knowledge and skills; wisdom comes from real-world exchange. When you hit a plan
you can't explain or a bloat/wraparound mystery, take it to where the experts are:
PostgreSQL mailing lists —
pgsql-performance for a real EXPLAIN (ANALYZE, BUFFERS), pgsql-general
for the rest. Highest signal anywhere; core developers answer.
When you post an EXPLAIN, include (ANALYZE, BUFFERS) output and your
table definitions — the exact habit this course built.
Keep for reference
The course map & review plan has every keystone
on one page and a spaced-review schedule. Come back to it in a few days and re-take Part 1
cold — spacing is what makes this stick.