Concrete scenario
One tenant dominates writes and the primary no longer meets latency or recovery targets.
Mechanics
Shard when measured capacity, locality, or fault-domain requirements cannot be met safely by one write domain; there is no universal 2 TB or writes-per-second threshold. Range partitioning aids scans but can hotspot monotonic keys; hashing spreads keys but scatters ranges.
Failure mode
A poor shard key creates hot partitions, cross-shard joins, and distributed transactions. Rebalancing can consume network and disk while old and new routers disagree.
Trade-off
Sharding adds capacity and isolation at the cost of routing, rebalancing, global constraints, and operations. Vertical scaling or partitioning within one database is often simpler first.
Worked example: sharding a multi-tenant SaaS
Suppose there are 20k tenants and 12 TB of data, with a single primary at 70% CPU on writes. The dominant queries are all scoped to one tenant.
- Shard key:
tenant_id. Every dominant query becomes single-shard, and per-tenant transactions and foreign keys still work within the shard. - Logical shards:
logical = hash(tenant_id) mod 1024, then a lookup table maps logical shards to 8 physical clusters (128 logical per cluster, about 1.5 TB each). - Big tenants: the largest tenant has 8% of the data. Pin it to its own cluster via a directory entry, so it does not overload a hashed neighbour.
- Growth: to go from 8 to 16 clusters, move 64 logical shards off each cluster using logical replication or CDC, then flip the directory entry. No rows are rehashed.
Strategy comparison
| Strategy | Routing | Range scans | Hotspot risk | Rebalancing | Used by |
|---|---|---|---|---|---|
| Range | Key-range table | Efficient | High for monotonic keys | Split and move ranges | Bigtable, HBase, Spanner, CockroachDB |
| Hash (mod N) | Compute | Scatter to all shards | Low (except hot keys) | Painful: most keys move | Simple app-level sharding |
| Consistent hashing / vnodes | Ring lookup | Scatter | Low | About 1/N of keys move | Cassandra, DynamoDB (internally), Riak |
| Directory / lookup | Metadata service | Depends on key | Controllable | Move individual entries | Vitess (vindexes), Notion, Figma |
What you lose after sharding
- Cross-shard joins: push them to an analytics store, denormalize, or run scatter-gather with limits.
- Global unique constraints: email uniqueness across shards needs a separate global table or service.
- Cross-shard transactions: use 2PC (slow, blocking on coordinator failure) or sagas with compensation.
- Globally sorted pagination: needs a merge across shards, so prefer per-shard cursors.