The one thing to understand first
A partitioned table is one logical table split into many physical child tables by a partition key. The win is not magic speed on every query — it is the ability to skip entire partitions (pruning), to drop old data instantly (DROP/DETACH a partition instead of a massive DELETE), and to vacuum and index partitions independently. Declarative partitioning lives in src/backend/partitioning/.
Partitioning only pays off when your queries filter on the partition key — that is what lets the planner prune; a query without the key must scan every partition. Choose the key around your dominant access pattern, and the operational wins (instant retention, independent maintenance) follow.
Three partitioning methods
- RANGE — by value ranges, ideal for time-series (
created_at per month).
- LIST — by discrete values (
region IN ('EU') per partition).
- HASH — by a hash of the key, for even distribution when there is no natural range/list.
CREATE TABLE events (id bigint, created_at timestamptz, payload jsonb)
PARTITION BY RANGE (created_at);
CREATE TABLE events_2026_05 PARTITION OF events
FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');
Insert routing
On INSERT, the executor computes the partition key and routes each row to the correct child via ExecFindPartition(). Routing is fast (a binary search for range, a hash for hash), but it does require the key to be present and non-null according to the partition scheme. This is why the partition key must be part of the primary key / unique constraints on a partitioned table.