Partitioning / Sharding Basics
On this page
Your orders database runs on the biggest machine your cloud sells. It already has read replicas and a cache. Then the business grows again: 9,000 orders per second at peak and 20 TB of data. Every write still goes to one primary, the disk is nearly full, and there is no bigger machine to buy.
The only way forward is to stop keeping all the rows in one place. Split them across several databases so each one holds a slice and takes a slice of the writes. That split is called partitioning, and when the slices live on different machines it is called sharding.
How it works
Two ways to split

- Range partitioning: each shard owns a range of the key, for example orders from January to April. Range scans ("all orders in March") hit one shard. But if the key grows over time (dates, auto-increment IDs), every new write goes to the last shard, which becomes a hot shard.
- Hash partitioning: compute
hash(key)and map it to a shard. Similar keys land on different shards, so load spreads evenly. Range scans now touch every shard.
A common middle ground is a compound key: hash on one part, sort on another. (user_id, created_at) puts all of a user's orders on one shard (by hash of user_id) and keeps them in time order inside it.
Routing

Something has to know which shard holds which key: a shard map. It can live in the application (a library computes hash(user_id) % 1024 to a partition, then looks up which machine holds that partition), in a routing proxy, or in a coordination service such as ZooKeeper or etcd.
- A query with the shard key goes to one shard. Fast and cheap.
- A query without it goes to all shards in parallel and the results are merged (scatter-gather). It is as slow as the slowest shard, and costs N times the work.
Choosing the shard key
Ask three questions:
- What is the most common query? Make its filter the shard key so it hits one shard. "My orders" →
user_id. - Will writes spread evenly? Avoid keys where one value gets most writes (today's date, a celebrity's ID, one giant tenant).
- Will related data that changes together live together? A transaction across shards needs distributed transactions or sagas, so keep a user's cart, orders and payments on the same shard if they change together.
Resharding
When you add shards, data must move. With hash(key) % N, changing N moves almost every key. Two standard fixes:
- Many fixed virtual partitions: hash into, say, 1,024 partitions from day one and assign several partitions to each machine. Adding a machine means moving whole partitions, not rehashing every row.
- Consistent hashing: only the keys between the new node and its neighbour move.
Either way, the move is copy → catch up → switch → delete, done gradually while serving traffic.
Trade-offs and costs
- Cross-shard queries become scatter-gather; cross-shard joins are usually gone, so you denormalize.
- Cross-shard transactions need two-phase commit or sagas; most designs avoid them.
- Global uniqueness (unique emails, global auto-increment IDs) needs a separate service or ID scheme (for example Snowflake-style IDs).
- Hot keys still exist: one huge customer can overload their shard. Split hot keys further (salt the key with a suffix) or give them a dedicated shard.
- Operations multiply: N primaries to back up, monitor, fail over and upgrade.
- Secondary indexes are either local to each shard (queries fan out) or global (writes update another shard asynchronously).
When to reach for it
- Writes exceed what one primary can take, even after batching.
- Data no longer fits comfortably on one machine (or backups and restores take too long).
- Per-tenant isolation: big customers on their own shards.
Before sharding, check: are the right indexes there? Would read replicas or a cache remove the pressure? Can old data be archived? Sharding is the expensive last step.
Example
Orders for a large shop. Assumptions: 9,000 writes per second at peak, 20 TB of orders growing 6 TB a year, one Postgres primary handles about 3,000 writes per second comfortably.
- Shard count: 9,000 / 3,000 = 3 shards today; plan for growth with 1,024 virtual partitions mapped onto 4 machines, about 256 partitions each.
- Shard key:
user_id. "My orders" (the top query) hits one shard, and one user's writes are a tiny fraction of the total, so no hot shard. - Order lookup by ID: the order ID embeds the partition number (for example, the top 10 bits), so
GET /orders/{id}routes without a lookup table. - Admin query "all orders in the last hour": scatter-gather to 4 shards. Better: stream orders into an analytics store and query there.
- Growth: adding a fifth machine moves about 205 partitions, a fifth of the data, copied in the background and switched partition by partition.
Common mistakes
- Sharding too early, before indexes, replicas and caching are used up.
- A shard key with a hot spot, such as a date or a monotonically increasing ID with range partitioning.
- A shard key that doesn't match the main query, so the most common request becomes scatter-gather.
hash % Nwith no plan for changing N.- Forgetting global constraints such as unique usernames.
- Not mentioning hot keys in systems with celebrities or giant tenants.
Say this in the interview
"At 9,000 writes per second one primary isn't enough, so I'll shard orders by hash of user_id. That keeps 'my orders' on one shard and spreads writes evenly. I'll hash into 1,024 virtual partitions mapped to machines, so adding capacity means moving whole partitions. Order IDs carry the partition number so lookups by ID route directly. Cross-user analytics go to a separate analytics store instead of scatter-gather on the primaries."
Used in
- Consistent Hashing, Database, Write-heavy Systems.
- Design a Distributed Key-Value Store, Design a Distributed Message Queue like Kafka, Design a Distributed Cache like Redis.