Range partitioning
Pick an sorted column — almost always a timestamp — and give each split a bounded interval.
orders_2024 : order_date >= '2024-01-01' AND < '2025-01-01'
orders_2025 : order_date >= '2025-01-01' AND < '2026-01-01'
orders_2026 : order_date >= '2026-01-01' AND < '2027-01-01'
A query with WHERE order_date >= '2026-01-01' can be proved to need only the last split, so the engine reads one file instead of three. That proof is split pruning, and it's where nearly all the gain is.
The second gain is admin and, for time-series data, often the bigger one. Deleting a year of orders with DELETE writes a row version per deleted row, makes huge WAL traffic, and leaves the table bloated. DROP TABLE orders_2024 unlinks a file. Retention policies are the standard reason to split.
Hash partitioning
Hash the key, take the remainder modulo the split count, and store the row there. Rows spread evenly even so of what the values look like.
This is the right choice when there's no natural sorting to exploit and the goal is to spread contention. Many concurrent writers hitting one hot page, say.
The trade-off is that hash splitting prunes only on equality. A query for one customer_id reaches one split. A query for a *range* of customer ids reaches all of them, because hashing destroys order. Range splitting is the opposite: it prunes ranges beautifully and can leave you with one very hot split if recent data is where all the traffic goes.
| Range | Hash |
|---|
| Prunes equality | yes | yes |
| Prunes ranges | yes | no |
| Even distribution | no | yes |
| Drop old data instantly | yes | no |
| Typical key | timestamp | id |
Choosing the key
The split key must appear in the WHERE clause of the queries you care about. This is the whole game, and it's where splitting schemes go wrong.
Split by order_date and then run reports by customer_id, and each report reads each split — you've added cost and gained nothing. Worse than nothing: each split has its own indexes. So A query that scans them all does more index work than it would have on a single table.
A second constraint follows: a unique constraint has to include the split key, because the engine can't cheaply enforce uniqueness across pieces it's trying not to read.
What pruning is worth, and what caps it
The payoff scales with how much the query skips, and that is just math. Take 120 million orders over five years, split by month — 60 splits of two million rows each. A report over a single month, filtering on the split key, prunes to one split:
| query | rows the engine must touch | fraction of the table |
|---|
| one month | 2,000,000 | 1.7% (1/60) |
| one quarter | 6,000,000 | 5.0% (3/60) |
| no partition-key filter | 120,000,000 | 100% |
The single-month query does 60× less scanning. And the number is just the split count, because pruning reads one of 60 equal pieces. Finer splits prune harder: weekly splits would make that same month prune to a quarter of the rows again.
Sizing
So why not split into thousands of tiny pieces? Because pruning saves scan work and the planner spends it back. The planner tests each split when it builds the plan, so planning cost grows roughly linearly with the split count — a fixed cost paid on each query whether or not pruning helps it:
| partitions | rough planning overhead |
|---|
| 60 | small, negligible next to the scan |
| 500 | starting to matter for short queries |
| 5,000 | can exceed the query's own runtime |
For a lookup that executes in a couple of milliseconds, planning across thousands of splits is the dominant cost, and the splitting has made the query slower. That is the ceiling the two forces set between them: too few splits and pruning barely helps. Too many and planning overwhelms the saving. a place between a few dozen and a few hundred is the usual advice, and monthly splits over a couple of years land on its own in that band.
Splits can also be subpartitioned. Range by month, then hash by customer within each month — when both access patterns matter.
Joins get a second, quieter benefit
Pruning is the gain everyone knows. The other one shows up when two tables are split on the *same* key. Orders and order-items both by customer_id, say. A row in bucket i of one table can only match rows in bucket i of the other, so the engine performs a split-wise join: 60 small joins of aligned buckets, rather than one join over the whole product.
The math is the same shape as pruning. With 60 buckets a side, matched splitting means 60 aligned split-joins. Unmatched, any bucket of one table could hold a match for any bucket of the other. This Is 60 × 60 = 3,600 split-pairs to consider. And each aligned join is small enough that its hash table — two million rows, not 120 million — fits in memory instead of spilling to disk. This is why data warehouses split fact and dim tables on the join key: the join, not just the scan, gets cut into pieces the engine can hold.
Questions people ask about Partitioning by Range and Hash
What is partitioning? Splitting one large table into smaller pieces, called partitions, that are stored separately but queried as one table.
What is the difference between range and hash partitioning? Range splits by value ranges such as dates. Hash spreads rows evenly using a hash of the key.
What is partition pruning? The planner skips partitions that cannot contain matching rows. It only works when the query filters on the partition key.
Is partitioning the same as sharding? No. Partitioning splits a table inside one database server. Sharding spreads data across several servers.
How do I choose a partition key? Pick the column most queries filter on, such as a date for time-series data. A poor choice leaves queries touching every partition.
Does partitioning always make queries faster? No. It helps when queries prune partitions or when old data is dropped by detaching a partition. Otherwise it adds overhead.
Where it goes wrong with Partitioning by Range and Hash
Splitting a small table. Under a few million rows, a good index is simpler and often faster.
A split key the queries don't filter on. No pruning, more index cost.
Hash splitting then querying ranges. each split, each time.
Losing the default split. A row that matches no split is an error unless a catch-all exists, and a data-loading job that fails at midnight on New Year's Day is the classic version of this.