Chapter 25 — Exercises

Design + hands-on. You can build a partitioned copy of orders to experiment. (answer in Appendix) = worked solution in Answers. ⭐ = stretch.

Group A — Concepts

25.1 What problems does partitioning solve that an index alone does not? Name three. (answer in Appendix)

25.2 Explain partition pruning in your own words. Why is it the main performance benefit?

25.3 Why is dropping old data far cheaper on a partitioned table than a monolithic one? (answer in Appendix)

Group B — Building partitions

25.4 Write DDL for an orders_part table partitioned by RANGE (order_date), with monthly partitions for Q1 2024. (answer in Appendix)

25.5 Why must the partition key be part of the primary key on a partitioned table?

25.6 Write a LIST-partitioned table by region, and a HASH-partitioned table with 4 partitions. (answer in Appendix)

25.7 ⭐ Insert a row into the partitioned parent and confirm (via the partition tables or EXPLAIN) it landed in the right partition.

Group C — Pruning & indexing

25.8 Write a query on orders_part that prunes to a single month, and confirm with EXPLAIN that only that partition is scanned. (answer in Appendix)

25.9 Create an index on the partitioned parent. What happens to the partitions?

25.10 ⭐ Show a query that does NOT prune (filters on a non-partition-key column) and explain why partitioning doesn't help it.

Group D — Maintenance

25.11 How would you archive/drop all of 2022's data on a monthly-range-partitioned table? Compare to a DELETE. (answer in Appendix)

25.12 ⭐ Describe attaching a new month's partition in advance and detaching an old one for archival.

Group E — Judgment

25.13 Give two situations where partitioning is warranted and two where it is overkill. (answer in Appendix)

25.14 Why is partitioning by a key your queries don't filter on a mistake?

25.15 ⭐ Why are thousands of tiny (e.g., daily, for years) partitions a problem? What granularity would you choose instead?

Group F — Progressive project

25.16 Identify a table in your domain that would benefit from partitioning (large, key-bounded). Choose the key and strategy.

25.17 Sketch the partitioned design and describe the retention/maintenance win.

25.18 ⭐ If nothing qualifies, explain why and at what scale you'd revisit.

Self-check. If you can choose a partition key that matches your queries, build range partitions, rely on pruning, and explain the retention win — and you know when not to partition — you can scale a table past a single heap. Next: correctness under concurrency.