PostgreSQL Partitioning and Partition Pruning

The idea: one table for SQL, many tables on disk

A big table is easier to manage in pieces: one per month, per region or per hash bucket. With declarative partitioning PostgreSQL keeps one partitioned table that queries and INSERTs use, and stores the rows in ordinary tables called partitions. The partitioned table itself has no storage. Each partition has a bound that says which values of the partition key it holds.

CREATE TABLE orders (order_date date, region text, customer_id int)
    PARTITION BY RANGE (order_date);
CREATE TABLE orders_jan PARTITION OF orders
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE orders_feb PARTITION OF orders
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
-- orders_mar, orders_apr ...
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

RANGE, LIST, HASH and DEFAULT

Method	Bound	Holds
RANGE	FROM ('2026-02-01') TO ('2026-03-01')	keys from the lower bound (included) to the upper bound (excluded)
LIST	IN ('DE', 'FR')	exactly the listed values
HASH	WITH (MODULUS 4, REMAINDER 1)	keys whose hash mod 4 is 1
DEFAULT	DEFAULT	every key no other partition holds (RANGE and LIST only)

Bounds may not overlap, so every key belongs to at most one partition. A HASH-partitioned table can't have a DEFAULT partition: every key already hashes to some remainder. In the Options group, partition by re-creates orders with each method and DEFAULT partition adds or drops orders_default.

Tuple routing on INSERT

An INSERT into orders goes through tuple routing: the executor computes the row's partition key and finds the one partition whose bound holds it. For RANGE and LIST it does a binary search over the sorted bounds; for HASH it hashes the key and takes it mod MODULUS (the page uses hash(k) = k so you can check it by eye). A key no bound holds goes to the DEFAULT partition (Demo Tuple routing on INSERT). Without one, the INSERT fails (Demo No DEFAULT partition):

ERROR:  no partition of relation "orders" found for row
DETAIL:  Partition key of the failing row contains (order_date) = (2026-06-01).

An UPDATE that changes the partition key moves the row: PostgreSQL deletes it from the old partition and inserts it into the new one, in the same statement.

Plan-time pruning

When the WHERE clause compares the partition key with a constant, the planner checks each partition's bound and drops the ones that can't hold a matching row. This is partition pruning. The pruned partitions are not in the plan at all, so EXPLAIN doesn't show them (Demo Plan-time pruning: only orders_feb is read). When a single partition is left, the planner even drops the Append node and scans that partition directly.

Three things limit plan-time pruning:

• The WHERE must be on the partition key. WHERE customer_id = 17 on a table partitioned by order_date scans every partition.
• The DEFAULT partition is kept whenever a matching value could fall outside every other bound (Demo Range WHERE also scans DEFAULT: orders_mar, orders_apr and orders_default).
• HASH partitions can be pruned by = and IN, not by a range: keys 10, 11, 12 hash to different remainders (Demo HASH).

Run-time pruning in the executor

Sometimes the value isn't known when the plan is made. Then the planner keeps every partition under the Append node and attaches pruning steps, and the executor prunes later. There are two moments:

• Initial pruning at executor startup, for parameters such as $1 in a prepared statement that runs a generic plan (one plan for any value; plan_cache_mode = force_generic_plan, or automatically after five executions if it's not more expensive). EXPLAIN prints Subplans Removed: 4 and lists only what is left (Demo Run-time pruning: $1).
• Exec pruning during execution, for values that only exist then: the result of a subquery (an InitPlan) or the outer row of a nested loop join. The skipped scans stay in the plan and EXPLAIN ANALYZE marks them (never executed) (Demo Run-time pruning: value from a subquery).

enable_partition_pruning

enable_partition_pruning (default on) switches plan-time and run-time pruning together. With it off, every partition is scanned and the WHERE is only a Filter on each scan (Demo enable_partition_pruning = off: 9 rows read for 1 result instead of 2). It is useful to measure what pruning saves, not for production. The older constraint_exclusion setting does the same job for inheritance-based partitioning; declarative partitions don't need it.

Reading EXPLAIN

Append (actual rows=1 loops=1)
  InitPlan 1
    ->  Seq Scan on promo (actual rows=1 loops=1)
          Filter: (id = 1)
  ->  Seq Scan on orders_jan orders_1 (actual rows=1 loops=1)
        Filter: (order_date = (InitPlan 1).col1)
        Rows Removed by Filter: 1
  ->  Seq Scan on orders_feb orders_2 (never executed)
        Filter: (order_date = (InitPlan 1).col1)
  ...

What you see	Meaning
a partition is missing	pruned at plan time
a single Seq Scan on orders_feb orders, no Append	plan-time pruning left one partition
Subplans Removed: N	N partitions removed by initial pruning at executor startup
(never executed)	in the plan, skipped by exec pruning while the query ran
Rows Removed by Filter	rows read in a scanned partition that didn't match

Pruning only decides which partitions are read. Inside each one the usual access paths apply: a Seq Scan here, but an index on the partitions gives Index or Bitmap scans (see PostgreSQL Bitmap Scan). How the planner costs the plans is on How a Query Planner Picks a Plan, and how the Append node pulls rows from its children is on the PostgreSQL executor page. Partitioning inside one server is not sharding across servers: compare Partitioning vs Sharding and Range vs Hash Partitioning.

What the page leaves out

Real hashing (hashint4 with a seed, so customer 17 is not in partition 1), multi-column keys and sub-partitioning, ATTACH / DETACH PARTITION (and the check that the DEFAULT partition holds no row the new bound would take), indexes and unique constraints on partitioned tables (a unique key must include the partition key), partition-wise joins and aggregates (enable_partitionwise_join), the parallel Append, the custom-plan heuristic of the plan cache, and the lock taken on every partition that survives plan-time pruning.

References

Table Partitioning, incl. Partition Pruning (PostgreSQL documentation)

CREATE TABLE: PARTITION BY and PARTITION OF (PostgreSQL documentation)

enable_partition_pruning (PostgreSQL documentation)

partprune.c: plan-time and run-time pruning steps (PostgreSQL source)