Partitioning: How Declarative Partitioning Prunes Work

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.

Partition pruning: the main event

Pruning is the planner/executor eliminating partitions that cannot match a query's filters. It happens in two places:

• Plan-time pruning — when the filter uses constants (WHERE created_at >= '2026-05-01'), the planner discards non-matching partitions before execution. EXPLAIN shows only the surviving partitions.
• Execution-time pruning — when the value is only known at run time (a parameter, or the result of a join), the executor prunes during execution. EXPLAIN ANALYZE shows (never executed) on skipped partitions.

EXPLAIN SELECT count(*) FROM events
WHERE created_at >= '2026-05-01' AND created_at < '2026-06-01';
-- Only events_2026_05 appears in the plan.

Partition-wise joins and aggregates

When two tables are partitioned the same way and joined on the partition key, PostgreSQL can perform a partition-wise join — joining matching partitions pairwise instead of the whole tables — controlled by enable_partitionwise_join. Similarly enable_partitionwise_aggregate can aggregate per partition then combine. Both reduce memory and enable more parallelism, but are off by default because they increase planning cost.

Layer 3 — Watch it happen on your own database

-- Plan-time pruning: constant in the filter
EXPLAIN SELECT count(*) FROM events
WHERE created_at >= '2026-05-01' AND created_at < '2026-06-01';
--   only events_2026_05 appears in the plan

-- Execution-time pruning: value known only at run time
EXPLAIN (ANALYZE) SELECT * FROM events WHERE created_at = $1;
--   skipped partitions show "(never executed)"

-- The anti-pattern: no partition key in the filter
EXPLAIN SELECT * FROM events WHERE payload->>'user' = '42';
--   every partition is scanned -> Append over all children

Run the first query and confirm only one child appears — that is pruning working. Then run the third (no partition key) and watch the plan fan out into an Append across every partition: proof that the partition key must be in the predicate. (never executed) in EXPLAIN ANALYZE is the signature of execution-time pruning on parameterised or join-driven values.

Layer 4 — The levers this hands you

Used well, partitioning gives operational superpowers; used carelessly it adds overhead. The levers and the traps:

• Instant retention. DROP TABLE events_2023_01 or DETACH PARTITION removes a month in milliseconds versus a multi-hour DELETE + vacuum.
• Independent maintenance. Each partition vacuums, analyzes, and reindexes as a smaller unit, with less bloat impact; range-by-time pairs beautifully with BRIN indexes.
• Partition-wise joins/aggregates (enable_partitionwise_join, enable_partitionwise_aggregate) cut memory and unlock parallelism — off by default because they raise planning cost.
• Mind the traps: thousands of partitions inflate planning time; the partition key must be in every unique constraint (no global uniqueness on a non-key column); and automate partition creation (pg_partman) so you never run out of future partitions.

Layer 5 — What an Oracle DBA should expect vs what they get

Partitioning is one area where Oracle has a long head start, and the gaps matter:

• RANGE/LIST/HASH map directly, but Postgres has no interval partitioning — Oracle auto-creates the next range partition on insert; in Postgres you pre-create partitions or run pg_partman.
• Pruning ≈ partition elimination. Plan-time and execution-time pruning correspond to Oracle's static and dynamic partition pruning; the (never executed) marker is the Postgres tell.
• Global indexes don't exist. Oracle supports global indexes spanning all partitions; PostgreSQL indexes are always local (per-partition), which is why a unique constraint must include the partition key.
• Routing is declarative, not trigger-based. Modern Postgres routes inserts via ExecFindPartition() — no inheritance triggers like the pre-10 (or old Oracle 8-style) era.

Key takeaway

Declarative partitioning splits one logical table into children by RANGE/LIST/HASH; inserts route via ExecFindPartition() and queries that filter on the partition key prune away irrelevant partitions at plan or execution time. The real payoff is operational — instant retention with DROP/DETACH and independent maintenance — provided you choose the key to match your dominant filter, keep partition counts sane, and automate partition creation.