PostgreSQL Partition Pruning: Fast Queries Only When the Planner Can Prove It

Partitioning has a way of making teams feel safe before it makes queries fast. The table is split by month, the migration is complete, and the dashboard is still slow. That is when the uncomfortable truth arrives: partitions only help when PostgreSQL can avoid most of them.

Partition pruning is the performance feature. It lets the planner or executor skip partitions that cannot contain matching rows. If the query shape hides the partition key, PostgreSQL may still touch far more partitions than anyone expected.

Good partitioning is less about the DDL and more about whether your most important queries naturally include the partition boundary.

Partition by the way users ask questions

Time-range partitioning works when most queries include time ranges. Tenant partitioning works when tenant is always present and tenant count is manageable. Partitioning by a column that users rarely filter on creates maintenance without pruning.

Use predicates the planner can reason about

Keep the partition column visible. Wrapping it in functions or calculating the boundary on the wrong side can make pruning harder.

-- Easier to prune.
SELECT count(*)
FROM events
WHERE occurred_at >= timestamp '2026-05-01'
  AND occurred_at <  timestamp '2026-06-01';

-- Harder shape for pruning and indexes.
SELECT count(*)
FROM events
WHERE date_trunc('month', occurred_at) = date '2026-05-01';

EXPLAIN should show skipped partitions

Do not assume pruning happened. Check the plan. If a query for one month scans every monthly partition, the partitioning design is not paying rent for that query.

EXPLAIN (ANALYZE, BUFFERS)
SELECT tenant_id, count(*)
FROM events
WHERE occurred_at >= timestamp '2026-05-01'
  AND occurred_at <  timestamp '2026-05-08'
GROUP BY tenant_id;

Local indexes still matter

Pruning chooses partitions. Indexes choose paths inside those partitions. A pruned query can still be slow if each remaining partition requires a broad scan.

CREATE INDEX events_2026_05_tenant_time_idx
ON events_2026_05 (tenant_id, occurred_at DESC);

Too many partitions has a cost

Tiny partitions can increase planning overhead, migration complexity, autovacuum management, and index maintenance. The partition interval should match data volume, retention, and query windows, not a calendar preference.

The pruning checklist

1. Do top queries filter on the partition key?
2. Are predicates written as ranges on the raw column?
3. Does EXPLAIN show only relevant partitions?
4. Do remaining partitions have the right local indexes?
5. Is partition count still reasonable for planning and maintenance?
6. Does retention drop partitions instead of deleting rows?

The production habit

The teams that get good at PostgreSQL performance do not chase every knob. They turn a vague complaint into a named failure mode, collect one clean measurement, make one change, and then compare the next measurement against the first. That rhythm is slower than guessing for the first hour and much faster by the end of the incident.