Partition Key Design for Pruning #

A table was partitioned by created_at in monthly partitions to make queries faster. A year later the plans show Append over all 36 partitions for nearly every query, because the application filters by tenant_id and status, and only reporting jobs filter by date. The partitioning added maintenance work, planning overhead and a statistics trap, and removed nothing from the scans.

How pruning appears in plans is covered in partition pruning analysis. This page is about choosing the key so that pruning happens at all.

The Condition #

Pruning eliminates a partition when the query’s conditions prove it cannot contain matching rows. That requires:

• A condition on the partition key. No condition, no pruning. Conditions on other columns never prune.
• A form the planner can evaluate. Constants and stable expressions prune at plan time; parameters and values from the outer side of a join prune at execution time, shown as Subplans Removed — the distinction in runtime vs plan-time partition pruning.
• An unwrapped key column. WHERE date_trunc('month', created_at) = … does not prune a table partitioned on created_at, for the same reason it cannot use an index — see function-wrapped columns and sargability.

So the key must be a column the hot queries filter on directly. Choosing it is therefore a question about the workload, not about the data model:

• Time-series with retention — partition by time, because retention (dropping old partitions) is the main benefit and queries usually carry a time range.
• Multi-tenant with large tenants — partition by tenant (list or hash), because every query carries the tenant.
• Both — partition by tenant and subpartition by time, or by a composite key, accepting more partitions.

Size matters as much as the column. Partitions should be large enough that their count stays modest — planning cost grows with it, as covered in planning overhead with many partitions — and small enough that scanning one is meaningfully cheaper than scanning the whole table. A few hundred partitions is comfortable; a few thousand needs care.

Annotated EXPLAIN Evidence #

Partitioned by the wrong column for the workload:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total FROM orders WHERE tenant_id = 8812 AND status = 'open';

Append  (actual time=0.4..3104.6 rows=412 loops=1)
  ->  Seq Scan on orders_2024_01 orders_1  (actual rows=12 loops=1)
        Filter: ((tenant_id = 8812) AND (status = 'open'::text))
        Rows Removed by Filter: 9204110
  ->  … 35 more partitions, each scanned in full …
Execution Time: 3110.2 ms
-- no condition on created_at: nothing to prune, and 36 scans instead of one

Partitioned by tenant, list partitioning with a hash fallback:

Append  (actual time=0.03..0.09 rows=412 loops=1)
  Subplans Removed: 63
  ->  Index Scan using orders_p12_tenant_status_idx on orders_p12 orders_1  (actual rows=412 loops=1)
        Index Cond: ((tenant_id = 8812) AND (status = 'open'::text))
Execution Time: 0.11 ms
-- 63 of 64 partitions pruned; one index scan inside the surviving partition

And the reporting query, which still prunes on time within the tenant partition:

EXPLAIN SELECT sum(total) FROM orders WHERE tenant_id = 8812 AND created_at >= '2026-09-01';

Aggregate → Append (Subplans Removed: 63) → Index Scan using orders_p12_created_idx
-- the time range is served by an index inside the partition rather than by pruning

Step-by-Step Resolution #

1. Inventory the filters. From pg_stat_statements, list the columns the highest-traffic statements filter on and how often. The key candidate is a column present in nearly all of them.

2. Check the retention requirement. If old data must be removed cheaply, time must be in the key — possibly as a subpartition level.

3. Choose the partitioning strategy:

   • RANGE for time and ordered keys;
   • LIST for a small, stable set of values;
   • HASH for even distribution over a high-cardinality key such as tenant id, when no natural grouping exists.

4. Size the partitions. Aim for a partition count in the tens or low hundreds, and partitions large enough to be worth scanning independently. For time partitions, that usually means monthly rather than daily for moderate volumes.

5. Ensure queries carry the key unwrapped. Adding tenant_id to queries that logically imply it — because a join already restricts it — is often the difference between pruning and not.

6. Index within partitions for the remaining predicates; pruning selects the partition, the index selects rows inside it.

7. Analyze the parent after building, because inherited statistics are not maintained automatically — see statistics on partitioned tables.

Before and After #

-- BEFORE: monthly partitions, tenant-filtered workload
Append over 36 partitions, each scanned in full                    Execution Time: 3110.2 ms

-- AFTER: hash partitioning on tenant_id, 64 partitions
Append  Subplans Removed: 63 → Index Scan on one partition          Execution Time: 0.11 ms

When not partitioning is the answer #

The example’s third bar is the uncomfortable one: an ordinary table with a composite index matched the partitioned version’s performance for the hot query, without any of the operational cost. That is common. Partitioning earns its keep through retention (dropping partitions instead of deleting rows), vacuum scope (each partition vacuumed independently), bulk load and swap patterns, and occasionally through pruning that an index cannot replicate — very wide scans of one partition where an index would be less efficient than a sequential scan of a small relation.

If none of those apply, an index usually gives the same query performance with simpler operations. The decision is explored further in partial indexes vs partitioning for hot subsets.

When partitioning is justified, the key follows the workload, and the workload is what the queries filter on — not what the data “is about”. A table of events is conceptually about time, but if every query is tenant-scoped, tenant is the key, and time becomes a subpartition or simply an indexed column within each tenant’s partition.

Common Pitfalls #

Partitioning by the conceptually obvious column. Time is natural for events, and wrong if queries filter by tenant. Diagnostic signal: Append over every partition. Fix: choose from the query mix.

Wrapping the key in a function. date_trunc('month', created_at) prunes nothing. Diagnostic signal: filters on expressions of the key. Fix: range conditions on the bare column.

Too many partitions. Planning cost and catalog size grow. Diagnostic signal: planning time of milliseconds on simple queries. Fix: coarser granularity.

Forgetting parent statistics. Estimates above the Append default. Diagnostic signal: 200-group estimates. Fix: scheduled ANALYZE on the parent.

Frequently Asked Questions #

How do I choose a partition key? #

Pick the column that the highest-traffic queries filter on directly, since pruning only happens with a condition on the partition key. If retention matters, time must be part of the key, possibly as a second level.

Does partitioning make queries faster by itself? #

Only when it lets the planner skip partitions. Its more reliable benefits are cheap retention through dropping partitions, smaller vacuum units and easier bulk loads.

Can I partition by two columns? #

Yes — a composite range key, or a two-level scheme partitioning by one column and subpartitioning by another. Both increase the partition count, so size the levels so the total stays manageable.

Related #

• Partition Pruning Analysis — parent guide: reading pruning in plans
• Planning Overhead with Many Partitions — the cost of a high partition count
• Statistics on Partitioned Tables — keeping estimates right above the Append

Up: Partition Pruning Analysis