Chapter 25 — Key Takeaways

The big idea

Partitioning splits one logical table into many physical partitions (queried transparently as one) — for tables too large for a single heap even with good indexes. It solves what indexes can't: scanning less data (pruning) and cheap lifecycle operations (retention/maintenance).

Declarative partitioning

  • CREATE TABLE ... PARTITION BY RANGE/LIST/HASH (key), then create partitions.
  • Range (by date) is the common case; list (categories); hash (even spread).
  • The partition key must be in the primary key; range bounds are half-open [FROM, TO).
  • PostgreSQL routes inserts to the right partition automatically.

The two big wins

  1. Partition pruning — queries filtering on the partition key scan only the matching partition(s), automatically. This is the performance point — so partition by the key your hot queries filter on. (Case Study 2: wrong key → no pruning.)
  2. Maintenance/retention — dropping old data is a near-instant DROP/DETACH (no slow DELETE, no WAL flood/bloat); VACUUM/ANALYZE and indexes run per smaller partition. (Case Study 1: the killer feature for time-series/log/retention data.)

Indexing partitioned tables

Index the parent; it propagates to all (current and future) partitions. Partitioning + indexing are complementary: pruning narrows to partitions, the per-partition index finds rows within them.

When to partition — and when not

  • Yes: genuinely large (≈100M+ / unbounded growth) and queries/retention bounded by a key (especially time).
  • No: modest tables (an index suffices), or no single key matches your query/retention patterns. (Case Study 2: a 500M-row table queried by customer_id just needed an index, not date partitioning.)
  • Avoid too many tiny partitions (planning/management overhead) — pick a sensible granularity (often monthly).

You can now…

  • ☐ Explain when partitioning beats indexing alone.
  • ☐ Build range/list/hash partitioned tables (declarative).
  • ☐ Rely on pruning (and match the partition key to your queries).
  • ☐ Use partition DROP/DETACH for cheap retention and bulk archive.
  • ☐ Index partitioned tables and judge when not to partition.

Looking ahead

Chapter 26 — Transaction Management. From performance to correctness under concurrency: ACID, BEGIN/COMMIT/ROLLBACK, isolation levels, and PostgreSQL's MVCC — keeping data correct when many operations touch it at once.

One sentence to carry forward: Partition a genuinely huge table by the key your hot queries and retention actually use — then pruning makes reads fast and DROP makes retention instant; partition by the wrong key and you get all the complexity and none of the benefit.