Chapter 25 — Further Reading

Official reference (everyone)

  • PostgreSQL Docs: "Table Partitioning." The complete guide — declarative partitioning, range/list/hash, partition pruning, attach/detach, partitioned indexes, and limitations (the partition-key-in-PK rule). https://www.postgresql.org/docs/current/ddl-partitioning.html
  • PostgreSQL Docs: "Partition Pruning" — exactly when and how the planner prunes (and how to confirm with EXPLAIN).

Operational tooling (🏗️ DBA)

  • pg_partman — automates creating future partitions and dropping/retaining old ones (the automation Case Study 1 needs). Widely used in production.
  • pg_cron — schedule the partition-maintenance jobs.
  • "Partition management best practices" — granularity choices, pre-creating partitions, avoiding too-many-partitions.

Design & pitfalls (💻 Developer · 🏗️ DBA)

  • "Choosing a partition key" articles — matching the key to query and retention patterns (Case Study 2's lesson).
  • Sub-partitioning / composite partitioning — range-then-hash and similar, for workloads with multiple access patterns.
  • "When NOT to partition" — the honest counterpoint; an index often suffices.

Context (📊 Analyst · 🔬 CS Student)

  • Time-series databases (TimescaleDB) — Chapter 36; TimescaleDB builds automatic time-partitioning ("hypertables") on top of PostgreSQL. A natural next step if your data is heavily time-series.
  • Data warehousing (Chapter 34) — partitioned fact tables are standard in analytical schemas.

Reference (this book)

  • Chapter 23 — Indexing and Chapter 24 — Query Optimization: confirm pruning and per-partition index use with EXPLAIN.
  • Chapter 28 — Internals: why partition DROP avoids the WAL/bloat/VACUUM cost of a DELETE.
  • Chapter 36 — Specialized Databases: time-series engines built on partitioning.

Do, don't just read

  • Build a partitioned copy of orders by month; insert rows and confirm with EXPLAIN that a date-filtered query prunes to one partition.
  • Demonstrate the wrong-key problem (Case Study 2): query the date-partitioned table by customer_id and watch EXPLAIN scan every partition.
  • Practice retention: DROP an old partition and note it's instant vs. a DELETE of the same rows.

Next: Chapter 26 — Transaction Management: ACID and keeping data correct under concurrency.