Overview
Partitioning splits one large logical table into smaller physical pieces using one of three strategies: range (non-overlapping value ranges, natural for time-series data), list (explicit, named value groups), or hash (even distribution by hash modulo a partition count, when there's no natural range or category). The performance payoff is partition pruning: when a query's WHERE clause constrains the partition key with values the planner can compare directly against each partition's declared bounds, entire partitions get eliminated from the plan before execution, not scanned and filtered afterward, a query touching one month of a decade-spanning, monthly-partitioned table can skip well over a hundred partitions entirely. Pruning depends purely on partition bounds, not indexes, but it requires the filter value to be something the planner can actually reason about, a literal or bound parameter, not a volatile expression like CURRENT_TIMESTAMP whose value isn't fixed at plan time.
Quick Reference
| Strategy | Split by | Natural fit |
|---|---|---|
| Range | Non-overlapping value ranges | Time-series data (logs by month/year) |
| List | Explicit, named value groups | Known, finite categories (region, status) |
| Hash | Hash of key modulo partition count | Even distribution, no natural range/category |
Syntax
CREATE TABLE measurement (
city_id int NOT NULL,
logdate date NOT NULL,
peaktemp int
) PARTITION BY RANGE (logdate);
CREATE TABLE measurement_y2026m01 PARTITION OF measurement
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');Examples
-- Partition pruning eliminates every partition outside the
-- matching range before execution, not by scanning and filtering.
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2026-01-01';
-- Seq Scan on measurement_y2026m01 only; every earlier
-- monthly partition is pruned entirely.Volatile expressions defeat plan-time pruning
WHERE logdate >= CURRENT_TIMESTAMP can't be pruned at plan time because the value isn't fixed until the query actually runs. Filter on literals or bound parameters the planner can compare directly against partition bounds, not on non-immutable functions.
Visual Diagram
Common Mistakes
- Choosing hash partitioning by default when the data actually has a natural range (time) or category (region) that would partition-prune more effectively.
- Assuming partitioning alone speeds up every query, when pruning specifically requires filtering on the partition key with a value the planner can reason about at plan time.
- Filtering on a volatile expression like
CURRENT_TIMESTAMPornow()and being confused why pruning didn't eliminate any partitions. - Not creating a new range partition ahead of when data for it starts arriving, causing inserts to fail or fall into an unintended default partition.
Performance
- Pruning is driven entirely by partition bounds, not indexes, a partitioned table with no indexes at all still benefits, though indexes still help within whichever partition is actually scanned.
- Partition pruning can also happen at execution time (for genuinely parameterized values from a join or subquery), visible in
EXPLAIN ANALYZEoutput as(never executed)on eliminated partitions, distinct from plan-time pruning.
Best Practices
- Choose range partitioning for time-series data, list for known finite categories, and hash only when neither natural split exists.
- Write filters on the partition key using literals or bound parameters, not volatile expressions, so plan-time pruning can actually apply.
- Pre-create upcoming range partitions ahead of the data that will need them, rather than reactively after inserts start failing.
- Verify pruning is actually happening with
EXPLAIN, don't assume it based on the table being partitioned at all.