Partitioning Is Not a Performance Feature by Default
Partitioning a PostgreSQL table does not make queries faster. Partition pruning makes queries faster — and pruning only happens when the query’s WHERE clause includes the partition key. Teams partition large tables expecting a general performance improvement, then discover that analytics queries without a date filter now touch every partition instead of one unified table, and the planner overhead makes things worse than before. Partitioning is a data management feature first; it is a performance feature only under specific, verifiable conditions.
Situation
PostgreSQL declarative partitioning (introduced in PG10, significantly improved in PG11–PG13) routes rows to child tables based on a partition key — most commonly a date column for time-series data. The mental model engineers carry is usually: “the table is split into smaller pieces, so queries run faster.” That is true only when the planner can eliminate the pieces that are not relevant.
Teams with large event, audit, order, or log tables encounter partitioning as the recommended solution to table size problems. The recommendation is often correct, but the mechanism is misunderstood. Partitioning helps with archival (you can drop a partition instantly rather than running a DELETE), parallel query (PG11+ can parallelize across partitions), and large-table DDL operations. It does not help — and can hurt — when queries touch all partitions.
The Problem
When PostgreSQL receives a query against a partitioned table, it checks whether the planner can eliminate partitions based on the WHERE clause. This is partition pruning. PostgreSQL documents two types: static pruning at planning time (for literal values in the WHERE clause) and runtime pruning during execution (for parameterized queries, available since PG11 with enable_partition_pruning = on).
Pruning requires the WHERE clause to include the partition key with a condition that maps to a subset of partitions. A range-partitioned table on created_at prunes when you write WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01'. It does not prune when you write WHERE user_id = 12345.
The failure mode: a team partitions an orders table by created_at month, creating 36 partitions for three years of data. Most OLTP queries are by order_id or user_id — neither of which is the partition key. The planner must now plan against 36 child tables instead of one, generate separate plan nodes for each, and execute the query across all of them. Parallel query on partitions helps only if the query is large enough to benefit from parallelism — for point lookups, it adds overhead without benefit.
You can verify whether pruning is happening using EXPLAIN:
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE created_at >= '2024-03-01' AND created_at < '2024-04-01';
The plan should show only the relevant partition(s) under Append or Merge Append. If you see all 36 listed, the prune did not occur.
The core question: what conditions must be true for partitioning to improve — rather than degrade — performance?
How Partition Pruning Actually Works
The planner evaluates partition constraints during planning. For a range partition on created_at, the constraint is effectively created_at >= lower_bound AND created_at < upper_bound. If the WHERE clause contains a compatible condition on created_at, the planner eliminates non-matching partitions before execution.
Two settings control this behavior:
enable_partition_pruning(default:on) — enables both static and runtime pruning. Disabling this will cause the planner to scan all partitions on every query.constraint_exclusion(default:partition) — enables exclusion based onCHECKconstraints for inheritance-based partitioning (pre-PG10 style). For declarative partitioning,partitionis the correct setting; setting this toonadds unnecessary overhead on non-partitioned tables.
When partitioning genuinely helps:
| Use case | Why partitioning helps | What to verify |
|---|---|---|
| Time-series archival | Drop old partitions instantly without a table lock | DROP TABLE orders_2021 completes in milliseconds |
| Range-filtered analytics | Prune scans to relevant time window | EXPLAIN shows only matching partitions in plan |
| Parallel query on large scans | PG11+ can assign workers per partition | EXPLAIN shows Parallel Append with multiple workers |
| Bulk data ingestion | New data lands in the current-period partition, reducing index maintenance scope | Insert throughput measured before and after |
When partitioning hurts or provides no benefit:
| Pattern | Problem |
|---|---|
| Queries filter only on non-partition-key columns | All partitions scanned; planner overhead added |
| Default partition exists | Some planners cannot prune past a default partition, causing all partitions to be scanned |
| Very high partition count (500+) | Planning time increases linearly with partition count even when pruning works |
| Foreign keys referencing a partitioned table | Foreign key checks must scan all partitions |
In Practice
PostgreSQL’s declarative partitioning documentation (postgresql.org/docs/current/ddl-partitioning.html) describes partition pruning explicitly: “The query planner will only apply partition pruning when the query’s WHERE clause contains a condition on the partition key.” The documentation also notes that runtime pruning requires enable_partition_pruning = on and is available for parameterized queries when the partition key appears in the plan’s parameter bindings.
The documented PostgreSQL behavior for DROP TABLE on a partition is that it completes in milliseconds regardless of partition size, because it removes the child table’s storage files without scanning rows — this is the principal operational benefit of partitioning for time-series data with defined retention policies.
PostgreSQL 11’s release notes document the introduction of partition-wise joins and partition-wise aggregation as explicit opt-in settings (enable_partitionwise_join, enable_partitionwise_aggregate). These are off by default because they can increase planning time significantly on highly partitioned schemas.
Where It Breaks
| Scenario | What breaks | Why |
|---|---|---|
| Query lacks partition key in WHERE | All partitions scanned; query may be slower than on a non-partitioned table of the same total size | Planner cannot eliminate any partition; must generate plan nodes for all child tables |
| Default partition prevents pruning | Even queries with the partition key may scan the default partition | Planner cannot prove a value is not in the default partition without scanning it |
| Partition key does not match primary query access pattern | Partitioning optimizes the wrong dimension; primary key and foreign key lookups cross all partitions | Design decision cannot be undone without a full table rewrite |
What to Do Next
- Problem: Partitioning a table on a date column and then running OLTP queries filtered by user ID or order ID produces a plan that scans all partitions — no pruning, more overhead.
- Solution: Validate that the most frequent WHERE clause patterns include the partition key before committing to a partitioning scheme; use
EXPLAINto confirm partition pruning in production-representative queries. - Proof:
EXPLAINoutput for a date-filtered query shows only the relevant partition(s) listed under theAppendnode — not all 36. - Action: This week, run
EXPLAINon the five highest-volume queries against any recently partitioned table and check whether the plan shows one partition or many — if the answer is many, the partitioning key is wrong for those queries.