Partitioning and Sharding: Range, Hash, List, and Hot PartitionsOfficial sources7 of 9 sources come from the companies and standards bodies that own the technology.This rates where the sources come from. It doesn't prove every claim.
Vertical vs horizontal vs sharding, range/hash/list strategies, Postgres declarative partitioning + pg_partman, drop-old-partitions instead of DELETE, Cosmos hierarchical partition keys, verifying pruning with EXPLAIN.
By Amit UpadhyayPublished Updated 14 minArticle 2 of 13 in Databases
AI assistant · answers from this article
1 of 5
A table that still fits
Nothing to add yet
A well-indexed table is fine for a long time, even at hundreds of millions of rows. Partitioning it now would only add planning overhead.
On this page
Partitioning, in one sentence
Partitioning splits one large table into pieces, usually by time, so that queries read only the pieces they need and old data is removed a whole piece at a time.
You reach for it when one table keeps growing with time-based data, and most queries only need recent rows. Not before: it costs partition management (creating future partitions), a partition key in queries, and some constraints that get harder across partitions.
The cheat sheet
What to use when
You do not need all of this on day one. Each row is one signal and the piece that answers it. Stop at the row your system has reached, or pick one to see it in the picture above.
Where you areThe signalWhat to useWhat it costs
None. The indexes fit in memory, and no retention job is slow.Nothing. One well-indexed tableNothing
The nightly delete of old events takes five hours, overlaps with morning traffic and leaves the table bloated.Range-partition by time, and drop whole partitionsStep 4 ↓The partition key in every unique constraint
A query with no filter on the partition key scans every partition.Filter on the partition key, and verify pruning with EXPLAINOperating partitions ↓Queries rewritten to carry the key
At midnight on the 1st, inserts fail because no partition exists for the row.Create partitions ahead of time, and alert if the next is missingStep 2 ↓An extension or a scheduled job to run
One tenant is a hundred times larger, and its partition is overloaded while the rest sit idle.A second dimension in the key, or hash partitioningStep 3 ↓With hash, no easy way to drop a date range
Start With One Table
A well-indexed table is fine for a long time, even at hundreds of millions of rows. While its indexes fit in memory and you have no retention job to speed up, partitioning mostly adds planning overhead. It earns its place when you have a clear access pattern, usually time, and a real problem removing old data.
The nightly job that deletes old events used to take minutes. Now it takes five hours, overlaps with morning traffic, and leaves the table bloated. Nothing is broken exactly; the table has simply grown past the point where treating it as one thing works.
Partitioning splits a large table into pieces, usually by time, so queries touch only the pieces they need and old data can be dropped a whole partition at a time. Postgres’s declarative partitioning, with a tool such as pg_partman to create partitions ahead of time, makes this routine. Below is when to partition, how to pick the key, and what it costs in operational work.
Signs a table needs partitioning
Your events table is 800 GB and every query takes 30 seconds
You don't know whether to partition by created_at or tenant_id
You partitioned and somehow queries got slower
You have one Cosmos partition that's on fire while the rest are idle
Pro deep dive14 min full read
Keep reading with Pro
This is one of our advanced production deep dives. Pro unlocks the rest of it, every other deep dive, and the See, Animate, Test and Build modes on every topic.
What it covers
Vertical vs horizontal vs sharding, range/hash/list strategies, Postgres declarative partitioning + pg_partman, drop-old-partitions instead of DELETE, Cosmos hierarchical partition keys, verifying pruning with EXPLAIN.