liveBook · Manning

Partition boundary

As explained in PostgreSQL Mistakes and How to Avoid Them

A partition boundary is a set of value limits that determines which partition-key values a table partition accepts.

A partition boundary defines the value limits accepted by a table partition. In PostgreSQL, declarative partitioning combines a partitioning method, one or more partition keys, and these boundaries. Boundaries determine placement, pruning, and whether newly declared partitions overlap. section 4.2 section 4.3

The misleading branch-range partition

Suppose an energy-usage table uses RANGE (reading_time, branch_id). A January partition declared FROM ('2024-01-01', 1) TO ('2024-02-01', 10) already covers reading_time values through January 31 for a range of branch_id values. Declaring a second January partition FROM ('2024-01-01', 11) TO ('2024-02-01', 20) fails because PostgreSQL detects overlapping multidimensional bounds. A composite boundary is not two independent filters and does not create month-then-branch levels. section 4.3

Use sub-partitioning when the intended structure is one partition per month, with branch partitions inside each month. Define the parent with RANGE (reading_time), make each monthly partition partitioned by branch_id, and then declare branch bounds beneath it.

Keep these boundary rules in mind:

  • Treat multidimensional RANGE bounds as a combined boundary, not as independent ranges for each key. section 4.3

How PostgreSQL Mistakes and How to Avoid Them covers it

How deep it goes
Explains single- and multi-key bounds, overlap errors, and sub-partitioning, but stops short of exhaustive boundary syntax.
Examples
Uses monthly energy-usage and payment partitions, overlapping RANGE declarations, EXPLAIN output, and dropping an obsolete partition.
What you can do after
Build it from scratch by declaring non-overlapping bounds or a month-then-branch sub-partition hierarchy.
How it connects
Connects partition boundaries with partition keys, partition pruning, declarative partitioning, and sub-partitioning.
Cover of PostgreSQL Mistakes and How to Avoid Them
liveBookmanning.com
Read PostgreSQL Mistakes and How to Avoid Them on liveBook Get the book at manning.com

This explainer was generated by Manning's knowledge graph from the text of PostgreSQL Mistakes and How to Avoid Them. The section links point to the parts of the book it draws on.