A table has grown to two billion rows and queries are slow. Someone suggests partitioning. It sounds like the right kind of answer — divide the problem, work on smaller pieces — and it is frequently the wrong tool, applied at real cost, for a problem indexing would have solved.
Partitioning is genuinely valuable and for narrower reasons than its reputation implies. Knowing which reasons determines whether it helps you.
What changed in 2026
- Declarative partitioning matured. What used to require trigger-based workarounds became a first-class feature in mainstream databases, with much better planner support.
- Automated partition management spread. Tools and extensions that create and retire partitions on a schedule removed most of the manual maintenance burden.
- Pruning got smarter. Planners improved at eliminating partitions at both plan time and execution time, which widened the set of queries that benefit.
- Retention requirements drove adoption. Data deletion obligations made instant partition drops more valuable than any performance argument.
Partitioning is not sharding
The confusion is common enough to state plainly.
Partitioning splits one table into pieces inside a single database. The database still manages it as one logical table, transactions still work normally, and joins behave as expected. You are reorganising storage.
Sharding distributes data across separate database instances. Each shard is an independent system, cross-shard transactions and joins are hard or impossible, and you have taken on a distributed systems problem — see database sharding explained.
Partitioning does not increase your write capacity beyond one machine. If you are out of headroom on a single server, partitioning will not save you. It is a lifecycle and query-pruning tool, not a scaling-out one.
What it actually buys
| Benefit |
Real? |
Condition |
| Instant deletion of old data |
Yes, large |
Partition by the retention dimension |
| Faster queries via pruning |
Yes |
Query must filter on the partition key |
| Smaller, faster index maintenance |
Yes |
Indexes are per partition |
| Cheaper bulk loads |
Yes |
Load into a new partition, attach |
| Faster queries generally |
No |
Unfiltered queries touch every partition |
| More write throughput |
No |
Still one machine |
Instant deletion is the headline. Deleting a billion old rows with a DELETE is a long-running transaction generating enormous write-ahead log volume and leaving dead rows for cleanup to reclaim — see MVCC explained. Detaching and dropping a partition is a metadata change that completes in milliseconds and frees the space immediately. For any table with a retention policy, this alone justifies partitioning.
Pruning is the query benefit, and it is conditional. If your query filters on the partition key, the planner skips irrelevant partitions entirely. If it does not, every partition gets scanned — and a query touching a hundred partitions is slower than the same query on one unpartitioned table, because of per-partition planning overhead.
That conditionality is the crux. Partitioning a table by date when your queries filter by customer ID makes things worse, not better.
Choosing the key
The partition key must match how you query and how you retire data. Usually those align — time-series data is queried by recent time ranges and retired by age — which is why range partitioning on a timestamp is the dominant pattern.
Where they conflict, retention usually wins, because pruning is achievable through indexing and instant deletion is not achievable any other way.
Sizing matters in both directions. Too few, very large partitions and you get little pruning benefit. Too many small ones and planning overhead grows, since the planner considers every partition. Monthly or weekly partitions are common for time-series; thousands of tiny partitions is a known way to make everything slower.
The constraint that surprises people: unique constraints and primary keys must include the partition key. The database cannot enforce global uniqueness across partitions without it. If your table has a unique constraint on a column that is not the partition key, you either add the partition key to it — changing its semantics — or lose the constraint. This is discovered mid-migration more often than it is planned for.
Common mistakes
- Partitioning for size rather than lifecycle. Indexing handles large tables; partitioning handles retention.
- A key that does not match query filters. No pruning, plus overhead.
- Too many partitions. Planning cost grows with count.
- Forgetting the unique constraint rule. Found during migration, painfully.
- No automated partition creation. Inserts fail when a partition for the new period does not exist.
- Expecting write scaling. Still one machine.
- Not verifying pruning. Check the plan actually eliminates partitions rather than assuming.
FAQ
Will it make my queries faster?
Only queries that filter on the partition key. Others get slightly slower from planning overhead. Check your actual query patterns before committing — if most queries do not filter on your candidate key, the answer is no.
Can I partition an existing table?
Approaches vary by database, and it generally involves creating a partitioned table and migrating data, which needs planning for a large table. Some engines support attaching an existing table as a partition, which helps considerably.
How many partitions is too many?
Depends on the engine and version, and planning overhead grows with count. Hundreds is normally fine; many thousands starts to hurt. Size partitions to your retention granularity rather than minimising row counts.
Does it help with vacuum and index bloat?
Yes, usefully. Maintenance operates per partition, so it works on smaller units and old partitions become read-only and stable. Dropping a partition also removes its index bloat instantly — see index bloat.
Where to go next
For the distributed alternative and when you actually need it, read database sharding explained. For the indexing that usually solves large-table problems first, database indexing explained, and for narrowing indexes further, partial indexes.