Chapter 28 — Exercises

A mix of conceptual and hands-on (some use pg_stat_user_tables, EXPLAIN (ANALYZE, BUFFERS)). (answer in Appendix) = worked solution in Answers. ⭐ = stretch.

Group A — Storage

28.1 What is a page, how big is it in PostgreSQL, and why does the database read/write per page rather than per row? (answer in Appendix)

28.2 What is the heap? What is TOAST, and when does it kick in?

28.3 ⭐ A table is "2 GB." In terms of pages, what does that mean, and why does "how many pages an operation touches" predict its cost?

Group B — Buffer pool

28.4 Run a query cold, then again, with EXPLAIN (ANALYZE, BUFFERS). Explain the difference in disk reads vs. cache hits. (answer in Appendix)

28.5 Why is the first run of a query often slow and the second fast?

28.6 ⭐ Why is having enough RAM for the "working set" one of the biggest performance levers?

Group C — WAL & durability

28.7 Explain the write-ahead rule and how it delivers durability. Why is the WAL a sequential write? (answer in Appendix)

28.8 How does PostgreSQL recover after a crash? What role does the WAL play?

28.9 ⭐ The WAL also enables two other capabilities. Name them (hint: copies and time travel).

Group D — MVCC, dead tuples, VACUUM

28.10 Why does an UPDATE create a dead tuple? What removes dead tuples? (answer in Appendix)

28.11 UPDATE many rows, then check n_dead_tup in pg_stat_user_tables; run VACUUM and watch it change. (answer in Appendix)

28.12 Explain why a long-running transaction causes table bloat. (Tie to dead-tuple visibility.)

28.13 ⭐ What's the difference between VACUUM and VACUUM FULL, and why isn't VACUUM FULL routine?

Group E — Connect it back

28.14 Explain, using internals, why an index speeds reads but slows writes. (answer in Appendix)

28.15 ⭐ Explain why dropping a partition (Ch. 25) is far cheaper than DELETE-ing the same rows, in terms of pages/dead tuples/VACUUM.

Group F — Progressive project

28.16 Demonstrate cold vs. warm cache on one of your queries with EXPLAIN (ANALYZE, BUFFERS).

28.17 Create and then clean up dead tuples on one of your tables; observe n_dead_tup.

28.18 ⭐ Pick one earlier tuning decision and explain it in terms of pages / WAL / dead tuples.

Self-check. If you can explain durability via the WAL, the dead-tuple→VACUUM→bloat chain, and why cold/warm cache differ — and connect each back to a tuning rule — you understand why the database behaves as it does. Next: connecting applications.