Partition Pruning

Partition pruning skips partitions that cannot match a query predicate, turning a full-table scan into a scan of only the relevant time or key ranges.

databases

Definition

Partition pruning is the query planner's ability to exclude partitions whose bounds cannot satisfy a query's filter. In a table partitioned by month, a query for one day should read one partition, not every month. Pruning depends on the predicate referencing the partition key in a form the planner can evaluate at plan time or run time.

Context and scope

Pruning matters for large time-series, event, and audit tables, where most queries target a recent window. It reduces I/O and lock scope, and makes retention a matter of dropping old partitions instead of deleting rows.

Common misconceptions

Declaring partitions is enough — a query that does not filter on the partition key scans every partition
A function on the partition key preserves pruning — wrapping the key in a function can prevent the planner from using bounds
Dropping a partition is always instant and safe — dependent indexes, foreign keys, and concurrent queries need consideration

Anti-patterns

Partitioning by created_at but filtering with an expression that is not sargable
Creating one partition per day for a low-volume table, producing thousands of tiny partitions
Forgetting default partitions, so rows that do not match a range fail or land in an unbounded catch-all

Related concepts

Related examples

Authoritative source

https://www.postgresql.org/docs/current/ddl-partitioning.html

Scan the right slice

Partitioning splits a logical table into physical pieces by a key, commonly a date range. Pruning is what makes the split useful: the planner reads the partition bounds and excludes pieces that cannot contain matching rows. A query for created_at >= '2026-10-01' AND created_at < '2026-11-01' on a monthly partitioned table should touch one partition.

CREATE TABLE events (
  id bigint GENERATED ALWAYS AS IDENTITY,
  created_at timestamptz NOT NULL,
  payload jsonb NOT NULL
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_10 PARTITION OF events
  FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');

Verify pruning with EXPLAIN: the plan should list only the relevant partitions. If it lists all of them, the predicate is not sargable, the partition key is missing from the filter, or a parameter type prevents plan-time evaluation. EXPLAIN (ANALYZE, BUFFERS) shows actual reads and confirms the difference.

Keep predicates clean

WHERE date_trunc('day', created_at) = '2026-10-15' applies a function to the partition key and can defeat pruning. Rewrite it as a range: created_at >= '2026-10-15' AND created_at < '2026-10-16'. The same applies to casting a column or comparing it to a string with an incompatible type. Sargable predicates help both pruning and index use.

Runtime pruning handles parameterized queries where the value is known only at execution. It is powerful but not a substitute for a good key and clean predicates. Check the plan with representative parameters, not only with literals.

Retention and operations

Partitioning makes retention cheap: detach and drop an old partition instead of deleting rows one by one. That avoids bloat and long transactions, but it is a destructive operation that needs backups, dependency checks, and coordination with replicas. Create future partitions ahead of time; a missing partition causes inserts to fail or land in a default partition. Monitor partition count and size; thousands of tiny partitions increase planning time and catalog overhead.

When to use it

Use partitioning when a large table has a natural range key, queries target recent slices, and retention or maintenance benefits from partition-level operations. The advantage is reduced I/O and simpler retention; the drawback is planning complexity, partition management, and queries that cross many partitions. Compared to an index alone, partitioning also reduces the physical scope of maintenance and deletion, but it is not a substitute for a selective index.