Chapter 22 — Exercises
Design/planning exercises. (answer in Appendix) = worked solution in Answers. ⭐ = stretch.
Group A — Migrations basics
22.1 Write an up/down migration pair that adds a wishlists table to Mercado. (answer in Appendix)
22.2 Why are migrations version-controlled and ordered? What problem does that solve across dev/staging/prod?
22.3 Name three migration tools and one sentence on each. (answer in Appendix)
Group B — Safe vs dangerous
22.4 Classify each as safe or dangerous on a large, live table, and why: add nullable column; drop column; CREATE INDEX (plain); CREATE INDEX CONCURRENTLY; rename column; add NOT NULL. (answer in Appendix)
22.5 Why is renaming a column dangerous even though it's "just metadata"?
22.6 ⭐ How do NOT VALID + VALIDATE CONSTRAINT make adding a check/FK safer on a big table?
Group C — Expand-contract
22.7 Write the three-phase expand-contract plan to rename customers.phone to phone_number with zero downtime. State what each app deploy does. (answer in Appendix)
22.8 Apply expand-contract to change a column's type (e.g., qty from text to integer). Outline the phases.
22.9 ⭐ Use expand-contract to split full_name into first_name and last_name without downtime.
Group D — Data migrations
22.10 Write a batched backfill that copies phone into phone_number 10,000 rows at a time, re-runnably. (answer in Appendix)
22.11 Why batch a large data migration instead of one big UPDATE? (Tie to locks/WAL, Chapters 14/27.)
22.12 ⭐ Why separate "add the column" from "backfill" from "enforce NOT NULL" into different migrations?
Group E — Testing & rollback
22.13 Why must you test migrations on production-like data, not just an empty dev DB? (answer in Appendix)
22.14 When can't you write a clean "down" migration, and what's the rollback plan then?
22.15 ⭐ Why set lock_timeout before a risky ALTER, and what happens without it on a busy table?
Group F — Progressive project
22.16 Convert your project's schema.sql into an ordered set of migrations (or adopt a tool).
22.17 Plan one breaking change in your domain with expand-contract; write the three phases.
22.18 ⭐ Write a batched, re-runnable data migration for a backfill your design needs.
Self-check. If you can take any breaking change and break it into safe, backward-compatible expand-contract steps — and you batch and test data migrations — you can evolve a production database without fear. Next: making it fast (indexing).