Chapter 25 — Quiz

12 questions. Answers at the bottom.

Multiple choice

Q1. Partitioning splits: - A) A database into many databases - B) One logical table into many physical partitions (queried as one) - C) A column into many columns - D) An index into many indexes only

Q2. The most common partitioning strategy for time-series-like data is: - A) Hash - B) List - C) Range (by date) - D) None

Q3. Partition pruning means: - A) Deleting old partitions automatically - B) The query scans only the partitions that can match the filter on the partition key - C) Removing indexes - D) Compressing data

Q4. For pruning to help, the query must filter on: - A) Any column - B) The partition key - C) The primary key only - D) An indexed column

Q5. On a partitioned table, the primary key must: - A) Be a single column - B) Include the partition key - C) Be auto-generated - D) Be a natural key

Q6. Dropping a year of old data on a monthly-partitioned table is: - A) A slow DELETE of millions of rows - B) A near-instant DROP/DETACH of the relevant partitions - C) Impossible - D) The same cost as on a monolithic table

Q7. Creating an index on the partitioned parent: - A) Does nothing - B) Propagates a matching index to all partitions - C) Only indexes the first partition - D) Drops the partitions

Q8. Hash partitioning is used when you want: - A) Date ranges - B) Discrete category values - C) Even distribution across N partitions - D) No partitioning

Q9. Partitioning by a key your queries don't filter on: - A) Still enables pruning - B) Adds complexity with no pruning benefit - C) Is required - D) Speeds all queries

Q10. Range partition bounds use: - A) Inclusive both ends - B) Half-open [FROM, TO) (FROM inclusive, TO exclusive) - C) Exclusive both ends - D) Random assignment

True/False

Q11. Every large table should be partitioned. (True / False)

Q12. Thousands of tiny partitions can add planning and management overhead. (True / False)

Short answer

Q13. You have a 2-billion-row events table queried by date range, with a 90-day retention policy. Explain how partitioning helps both queries and retention.

---

Answer key

Q1 — B. One logical table, many physical partitions, queried transparently as one.

Q2 — C. Range by date.

Q3 — B. Scan only partitions that can match the partition-key filter.

Q4 — B. The partition key (else no pruning).

Q5 — B. The PK/unique constraints must include the partition key.

Q6 — B. DROP/DETACH is a fast metadata operation vs. a slow DELETE.

Q7 — B. It propagates to all (current and future) partitions.

Q8 — C. Even distribution across N partitions.

Q9 — B. No pruning benefit; just complexity.

Q10 — B. Half-open ranges (avoids gaps/overlaps).

Q11 — False. Only genuinely large, key-bounded tables; an index suffices for most.

Q12 — True. Too many tiny partitions hurt planning/management; pick a sensible granularity (often monthly).

Q13. Partition the table by RANGE (event_time), e.g., monthly. Queries filtering by date prune to only the relevant month(s) instead of scanning 2B rows — dramatically less work. Retention: enforcing the 90-day policy becomes DROP/DETACH of partitions older than 90 days — a near-instant metadata operation that frees storage immediately, instead of a slow, lock-heavy DELETE of hundreds of millions of rows. Maintenance (VACUUM/ANALYZE, indexes) also runs per smaller partition.

Scoring: 10–12 you can scale tables; 7–9 review pruning and the maintenance win; below 7, redo Exercises A–B.