Choosing a Partition Key and Strategy
Evaluate range, list, and hash partitioning against real access patterns to pick a key that prunes effectively.
Why the Partition Key Decides Everything
Partitioning splits one logical table into many physical child tables. The single most important choice is the partition key — the column (or expression) PostgreSQL uses to route each row to a partition.
A good key lets the planner do partition pruning: it skips partitions that cannot match your WHERE clause, so a query touches a few partitions instead of all of them. A bad key forces scans across every partition and you lose most of the benefit.
- Range partitioning — buckets by ordered ranges (dates, IDs).
- List partitioning — buckets by a discrete set of values (region, tenant).
- Hash partitioning — buckets by a hash of the key for even spread.
The right strategy is the one that matches how your queries actually filter the data.
Start From the Access Pattern, Not the Schema
Never pick a partition key by looking at the table alone. Look at the queries. Ask three questions:
- Which columns appear in
WHEREclauses on almost every read? - Do queries filter by ranges (last 7 days) or exact values (tenant_id = 42)?
- How do you delete or archive old data — by time, by customer, or never?
If 90% of your queries filter created_at >= now() - interval '7 days', then created_at is your candidate key. The partition key must appear in the query's filter for pruning to fire.
-- Inspect real filters before deciding: which columns are filtered most?
SELECT query, calls, total_exec_time
FROM pg_stat_statements
WHERE query ILIKE '%events%'
ORDER BY total_exec_time DESC
LIMIT 10;All lessons in this course
- Choosing a Partition Key and Strategy
- Partition Pruning at Plan and Execution Time
- Automating Partition Creation and Retention
- Migrating a Huge Table to Partitions Online