Sharding Patterns
also called Horizontal Partitioning
Splitting data across independent stores by a key, and the choice of that key — which is the decision that will define the system for years.
Definition
Sharding splits a dataset across independent database instances so that each holds a subset. It is the last resort of database scaling, taken after indexing, query optimisation, caching and read replicas, because it is the only one that changes the application's model of the world.
Shard key strategies
| Strategy | Good for | Fails when |
|---|---|---|
| Hash of entity ID | Even distribution | Range queries; resharding remaps everything |
| Range (e.g. by date) | Time-series, easy pruning | The current range is always the hot one |
| Directory / lookup table | Flexibility, per-tenant placement | The directory is a new critical dependency |
| Consistent hashing | Adding nodes moves ~1/N of keys | Still needs virtual nodes to avoid imbalance |
| By tenant | Natural isolation and residency | Tenants vary enormously in size |
Why the key is the whole decision
Everything follows from it. Queries that include the shard key are fast and route to one shard. Queries that do not must fan out to every shard and merge, which is slow and gets slower as shards are added. Joins across shards do not exist. Transactions across shards require a saga. Unique constraints across shards require a separate mechanism.
So the shard key must be the dimension almost every query filters on, and it must distribute evenly. Those two requirements often conflict, and resolving the conflict is the design work.
Industry example
Pinterest's sharding work is a well-documented case of choosing deliberate simplicity. Rather than an automatic distributed database, IDs were constructed to embed the shard location within the identifier itself, so any object's home shard is derivable from its ID with no lookup and no directory service on the critical path.
Two consequences worth internalising. First, this eliminates a whole class of failure — there is no directory to become unavailable or inconsistent. Second, it is deliberately hard to move an object between shards, because its ID encodes its location. That constraint was accepted knowingly: the system trades rebalancing flexibility for radical operational simplicity, and at their read-heavy workload it was the right trade.
The general lesson is that the "less capable" sharding scheme is frequently the better engineering choice, because sharding failures are almost always operational rather than theoretical.
Failure scenarios
- The hot shard. One celebrity user, one enormous tenant, one popular product receives a disproportionate share of traffic. This is the most common sharding failure and it is not fixed by adding shards. Fixes: split that key's data further, give it a dedicated shard, or cache it aggressively at a layer above.
- Resharding under load. Doubling shard count with a plain hash remaps almost every key. Consistent hashing or a directory limits the movement; plan the mechanism before you need it, because you will need it during a crisis.
- Cross-shard queries becoming the norm. A wrong key means most queries fan out, and the system is slower than the single database it replaced.
- No cross-shard uniqueness mechanism, so two shards independently allocate the same user-visible identifier.
Trade-offs
Bought: write throughput and dataset size beyond one machine, blast-radius isolation per shard, and sometimes residency compliance. Sold: joins, cross-shard transactions, straightforward schema migration (now N migrations), operational simplicity, and the ability to change your mind cheaply.
Exhaust every other option first. Then choose the key as though you cannot change it, because in practice you cannot.
Interview question
"You are sharding a multi-tenant SaaS database. One tenant is 40% of your data. What is your shard key, and what do you do about that tenant?"