Declarative Partitioning

A partitioned table is a first-class object with no data of its own. You declare a strategy and a key, you create partitions as real tables underneath it, and Postgres routes every insert to the partition whose bounds accept the row. Indexes and constraints declared on the parent are materialized on every child, including children attached years later. From the application's side it is one table with one name.

The illusion holds until you look at a plan, a lock, or the ALTER TABLE you were hoping would convert delivery_events in place. That last one does not exist: the manual states outright that a regular table cannot be turned into a partitioned table or the reverse. Everything here is about declaring the structure correctly the first time, because the second time is a copy of 1.2 billion rows.

Three Strategies

RANGE assigns each partition a non-overlapping band of key values, which is the natural fit for anything with a time-based retention policy, because the retention boundary and the partition boundary become the same line. LIST assigns each partition an explicit set of values, for keys that are discrete and enumerable: a region code, a tenant, a small set of statuses. HASH assigns partitions a modulus and a remainder and spreads rows evenly by the hash of the key, which buys nothing for retention and nothing for range queries; it exists to spread write contention or per-partition size when there is no natural range at all.

The key can span several columns for range and hash, up to 32 of them at the limit set when the server is built, while list partitioning takes a single column or expression. Cartwheel's choice writes itself. Events are read by time window, deleted by month, and reported by month, so RANGE (occurred_at) is the only strategy that makes the retention policy free. A hash partitioning of the same table would have spread the writes and left the eleven-hour delete exactly where it was.

Declaring It

The parent declares the columns and the strategy; each child declares the slice of key space it accepts. Range bounds are inclusive at the lower end and exclusive at the upper, so consecutive months are written as first-of-month to first-of-next-month and no value can land in two partitions. That asymmetry is the reason a month boundary needs no arithmetic anywhere: the upper bound of August is the lower bound of September, written identically.

CREATE TABLE delivery_events (
    id          bigint      NOT NULL,
    order_id    bigint      NOT NULL,
    event_type  text        NOT NULL,
    occurred_at timestamptz NOT NULL,
    payload     jsonb,
    PRIMARY KEY (id, occurred_at)          -- must contain the key
) PARTITION BY RANGE (occurred_at);

CREATE TABLE delivery_events_2026_08 PARTITION OF delivery_events
    FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');   -- [Aug, Sep)

CREATE TABLE delivery_events_default PARTITION OF delivery_events DEFAULT;

The last line looks like insurance and behaves like a debt. A DEFAULT partition accepts any key value no other partition claims, so an insert for a month that was never created succeeds instead of failing, and the rows accumulate somewhere no query expects them. The manual is explicit that adding a new partition to a table that has a default forces a scan of the default to prove it holds no rows belonging in the new range, so the tidy-up is a table scan under a lock and it gets slower the longer the mess is left. A default partition also rules out the concurrent detach Topic 59 turns into the standing retention procedure.

Indexes and Constraints Cascade

An index created on the parent is virtual in the same sense the parent is: the real index objects live on the partitions, one per child, and Postgres creates a matching one on every partition attached from then on. This is the mechanism that keeps a partition set consistent over years of monthly relations, and it is the reason indexes belong on the parent rather than in the partition-creation script. Constraints behave the same way: a CHECK or a NOT NULL on the parent is enforced by every child.

Uniqueness is where the model shows through. Each partition's index can only prove uniqueness within itself, so a unique or primary key constraint on a partitioned table must include every partition key column, and the partition key may not contain expressions or function calls. That is a hard rule, not a default that can be argued with. For delivery_events it means the primary key becomes (id, occurred_at), and any application code that assumed id alone identified an event has to be checked. The same rule reaches exclusion constraints, which must also carry the partition key columns compared with equality.

Foreign Keys, Both Directions

A partitioned table can reference an ordinary table, and since 12 an ordinary table can reference a partitioned one; before that, only the first direction worked, and before 11 neither did. Both are implemented per partition rather than by one constraint object, which has two consequences worth knowing before the review. The referencing side still needs its own index, exactly as it always did, because the constraint checks a lookup and nothing creates that index for you. And the per-partition implementation means the constraint count grows with the partition count: twelve monthly partitions each carrying delivery_events.order_id → orders.id is twelve constraint objects, all validated independently. Since 18 those can be added NOT VALID on a partitioned table and validated afterwards, which is the same fast path Chapter 3 used for constraints on orders.

Migrating an Existing Table

There is no in-place conversion, so the migration is a copy, and Chapter 3's vocabulary covers all of it: create beside, backfill in bounded batches, attach what lines up, swap under a short lock held with lock_timeout and retried. Create the partitioned table under a working name with its partitions already in place. Backfill month by month rather than in one statement, so no single transaction pins the vacuum horizon and no single burst of WAL lands on pg-replica-a. Have the application dual-write during the copy, or accept a cutover window and copy the tail last.

-- 1. new parent + partitions exist as delivery_events_p
-- 2. batched backfill, one month at a time, one commit per batch
INSERT INTO delivery_events_p
SELECT * FROM delivery_events
 WHERE occurred_at >= '2026-07-01' AND occurred_at < '2026-08-01';

-- 3. the swap: milliseconds of ACCESS EXCLUSIVE, retried until it wins
BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE delivery_events   RENAME TO delivery_events_old;
ALTER TABLE delivery_events_p RENAME TO delivery_events;
COMMIT;

The copy gets shorter when the old data is already segmented. If historic months live in separate tables (an archive of monthly dumps, or an inheritance scheme built in 2019) each can be attached directly as a partition rather than copied through, which is Topic 59's subject and comes with a validation scan you can avoid. And the tail is what costs elapsed time, not correctness: the closed months are static and can be copied over a week, while only the current month has to be reconciled at the cutover.

Reading a Partitioned Plan

The plan for a partitioned table has an Append node, or a Merge Append when the result has to come back in order, with one scan node beneath it per partition that survived pruning. Chapter 9 taught how to read the nodes; the partition-specific skill is arithmetic. Count the scan nodes and compare that number against how many partitions the predicate should have left standing. A query for one month showing one scan is pruning working. The same query showing twelve means the predicate never reached the planner in a form it could use.

EXPLAIN SELECT count(*) FROM delivery_events
 WHERE occurred_at >= '2026-08-01' AND occurred_at < '2026-09-01';

 Aggregate  (cost=2411880.12..2411880.13 rows=1 width=8)
   ->  Seq Scan on delivery_events_2026_08 delivery_events
         Filter: ((occurred_at >= '2026-08-01…') AND (occurred_at < '2026-09-01…'))

When exactly one partition survives, the Append disappears entirely and the plan reads like a query against a single table, which is worth recognizing: a plan with no Append in it is the best possible pruning result rather than evidence that partitioning did not take effect. The Filter line stays even though the partition's own bounds already guarantee it; the executor does not skip a redundant check, and the cost of that check is nothing next to the eleven partitions it did not open. Topic 58 takes this apart properly, including the case where the value is a parameter and the plan cannot show you anything at all.