# PostgreSQL exercise backlog Exercises whose **provenance is the PostgreSQL documentation** rather than a book. Migrated from `~/cld-toys/exercises` on 2026-07-25. Read the top-level `../README.md` first — it's the format spec. Legend: engine = **surprise** / **magnitude** / **causation**. Status: `[ ]` candidate · `[x]` built & verified · `UNVERIFIED` couldn't run. --- ## Resume here (session state, 2026-07-26) **Overlap pass with `ddia/` is done** (Phase 6 of `../tasks/merge-cld-toys.md`). All seven pairs were diffed body-by-body; **none was superseded or folded** — every one resolved to a bidirectional cross-link, because in each case the two sides run *opposite* experiments on the same mechanism. `query-planner-statistics` and `wal-and-crash-recovery` were re-checked against all 76 ddia exercise titles and confirmed clean. Per-exercise verdicts are in the notes below; the pass added 27 cross-source links and touched no transcript, so nothing needs re-verifying. **Status: all seven are built and re-verified.** Every transcript was re-captured on 2026-07-26 on the declared environment (macOS 26.5.2 / arm64, Docker Engine 29.6.2, `postgres:16` = PostgreSQL 16.14, digest pinned), and each exercise now carries Provenance, Prerequisites where warranted, and an Environment block. No `UNVERIFIED` remains. **Re-verification was not a formality — four exercises were wrong on substance, not merely drifted on numbers:** - `mvcc-and-vacuum` — the **headline claim was false**. An idle *Read Committed* reader does not block `VACUUM` (it takes a fresh snapshot per statement and holds a null `backend_xmin`); only `REPEATABLE READ`, or any transaction that has written, does. The exercise now tests that sharper claim, and its production playbook checks `backend_xid` too — the old one would have missed the commonest real cause. - `wal-and-crash-recovery` — `redo done at` is **not** the pre-crash `pg_current_wal_lsn()`; it is 56 bytes lower, being the start of the last record replayed. Proven with `pg_waldump`. - `transaction-isolation` — the `SERIALIZABLE` write-skew abort fires on B's `UPDATE`, not on its `COMMIT`. - `lock-contention` — the file taught a deadlock-victim rule that §13.3.4 explicitly says must not be relied upon. Plus one fabricated-looking figure (`index-vs-seq-scan`'s "6× on a warm cache", really ~1.7×), one arithmetic error (`cgroup`'s 89%, really 85%), and transcripts that had been **systematically tidied** across the whole set — trailing `valid_lft` lines, `ping` summaries and duplicate resolver replies silently removed. All restored raw. Two conventions were fixed repo-wide while re-running: images are now pinned (bare `alpine` → `alpine:3.20`), and `-p 5432:5432` was removed from every `docker run` here. Nothing ever connected over that port — all `psql` goes through `docker exec` — and publishing it broke the exercise for anyone already running Postgres on 5432. Full migration plan and remaining phases: `../tasks/merge-cld-toys.md`. ## Built and verified - [x] **[transaction-isolation](transaction-isolation.md)** ([html](transaction-isolation.html)) — **surprise**. Two `psql` sessions walk `READ COMMITTED` → `REPEATABLE READ` → `SERIALIZABLE`; each level produces the anomaly the next one prevents. Both failures are `SQLSTATE 40001`, and under `SERIALIZABLE` the write-skew abort lands on B's `UPDATE` (`Reason code: Canceled on identification as a pivot, during write.`), not on its `COMMIT`. *Overlap resolved 2026-07-26: kept as the **ladder / entry point** for `ddia/ch08`. A "Where each Part goes deeper" map after "What you should see" sends each rung to the ch08 exercise that takes it apart — Part 1 → `dirty-read-impossible`, Part 2 → `read-skew`, its same-row edge case → `lost-update`, Part 3 → `write-skew-doctors` and `phantom-write-skew` — plus `ddia/ch10/serializable-not-linearizable` as the rung above the top. All six link back.* - [x] **[mvcc-and-vacuum](mvcc-and-vacuum.md)** ([html](mvcc-and-vacuum.html)) — **causation**. An `UPDATE` is an `INSERT` plus a tombstone; then three idle transactions that look identical in `pg_stat_activity.state` give three different answers — a Read Committed reader blocks `VACUUM` not at all, `REPEATABLE READ` blocks it completely (`0 removed … 100000 are dead but not yet removable`), and a Read Committed session that merely wrote one row to an unrelated table blocks it just as hard while showing a null `backend_xmin`. *Overlap resolved 2026-07-26: cross-linked, both kept. `ddia/ch08/update-bloat-mvcc` varies the **schema** (indexed vs not, 344× via HOT) with one connection; this varies **who else is connected** with the schema fixed. Two halves of "why did my table bloat". `ddia/ch08/mvcc-no-blocking` supplies the no-blocking premise and is linked from Go deeper.* - [x] **[index-vs-seq-scan](index-vs-seq-scan.md)** ([html](index-vs-seq-scan.html)) — **magnitude**. Four access paths priced by page count; the planner declines an index at 99% selectivity and is provably right (15,571 buffers forced vs 15,152 for the Seq Scan). *Overlap resolved 2026-07-26: cross-linked, both kept. Same visibility map, opposite directions — `ddia/ch04/index-only-scan-visibility-map` watches `VACUUM` **unlock** an index-only scan; Part 5 here watches an `UPDATE` **revoke** one while the plan node stays identical.* Re-verified 2026-07-26 on PostgreSQL 16.14 / Docker Engine 29.6.2 / macOS arm64. - [x] **[query-planner-statistics](query-planner-statistics.md)** ([html](query-planner-statistics.html)) — **magnitude**. `rows=1` vs `actual rows=1000000` buys a Nested Loop (3,016,009 shared buffer hits, a 12,744 kB external-merge spill) where a Hash Join belonged (16,060 hits, no spill) — with SQL, schema, indexes and data unchanged. Also: an identical `rows=2773` for 990,000 and 1,000 rows before any `ANALYZE`, and two accurate estimates multiplying to a 95×-low conjunction until `CREATE STATISTICS ... (dependencies)` lands. *Re-verified 2026-07-26 on `postgres:16` / Engine 29.6.2 / macOS 26.5.2 arm64.* - [x] **[lock-contention](lock-contention.md)** ([html](lock-contention.html)) — **surprise**. Block a session on purpose, then walk the chain to the exact blocking PID via `pg_stat_activity`, `pg_blocking_pids()`, `pg_locks`; the wait turns out to be on a *transaction ID*, not a row, and row locks have four strengths that don't conflict the way you'd guess. A plain `UPDATE` takes `FOR NO KEY UPDATE`, so the reflexive `SELECT ... FOR UPDATE` overshoots and stalls foreign-key inserts on the parent row; the deadlock fires at 1002.675 ms, a hair over `deadlock_timeout`. *Overlap resolved 2026-07-26: cross-linked, both kept. Same deadlock interleaving as `ddia/ch08/deadlock-detection`, which continues to the **fix** (consistent lock ordering); this one continues to the **diagnosis toolkit**. Also linked to `ddia/ch08/prepared-transaction-locks`, the blocker that toolkit can't find, and `ddia/ch08/mvcc-no-blocking` for the premise in the mental model.* *Re-verified 2026-07-26 on `postgres:16` (PostgreSQL 16.14) / Engine 29.6.2 / macOS 26.5.2 arm64.* - [x] **[wal-and-crash-recovery](wal-and-crash-recovery.md)** ([html](wal-and-crash-recovery.html)) — **causation**. `SIGKILL` a running Postgres and watch the table reconstructed from the log: `COMMIT` promises the fsynced *intent*, not the table file. Recovery replays the *uncommitted* rows onto disk too and lets MVCC hide them; with `synchronous_commit = off`, acknowledged transactions vanish (31 of 73,044 in the captured run) for ~5× throughput. *Re-verified 2026-07-26 on Docker 29.6.2 / `postgres:16` (16.14), macOS arm64.* - [x] **[replication-lag](replication-lag.md)** ([html](replication-lag.html)) — **magnitude**. A million rows on the primary; the replica reports `1` while it is 16 MB behind, and the count jumps 4000001 → 7000001 in one step rather than climbing. Freezing replay splits `flush_behind | 0 bytes` from `replay_behind | 30 MB`, and exposes that the primary's `replay_lag` is a 10-second staircase that undercounts staleness. *Overlap resolved 2026-07-26: cross-linked, both kept. `ddia/ch06/{read-your-writes,monotonic-reads}` fix the anomaly in the **client** (route to the leader, pin a user to one replica); Part 4 here fixes it in the **server** (`remote_apply`). Substrate recipe: `ddia/ch06/code/setup_replication.sh` — copy it, don't import it.* ## Candidates (not yet built) None mined yet. The Postgres docs are a deep seam — `EXPLAIN` internals, HOT updates, index-only scan tradeoffs, `pg_stat_statements` — but nothing here has been through Phase A against the bar (*is there a number or behavior a smart reader predicts wrong?*).