Sharding

Replication gives you more read capacity and durability. It does not help with writes — every replica applies every write — and it does not help when the data no longer fits on one machine.

Sharding splits the data itself. Each machine holds a slice and handles the writes for that slice.

Everything depends on the shard key

Three keys, the same forty rows, three completely different outcomes. This is the decision the whole design rests on.

By tenant. Queries for one customer stay on one machine, which is exactly what you want — until one customer is 70% of your data. That is a hot shard, and you cannot fix it by rebalancing, because the smallest thing you can move is one tenant and it does not fit anywhere.

By time. Perfectly even in total, and every write goes to today’s shard. Three machines idle, one saturated. Monotonically increasing keys — timestamps, auto-increment ids, UUIDv7 — all have this problem.

By hash. Flat distribution of both storage and writes, and locality is gone: any query not filtered by the shard key must ask every shard and merge the results.

Range or hash

Range sharding — shard 1 holds A–F, shard 2 G–M. Range queries work, and hot spots are likely, since real data is never uniformly distributed.

Hash sharding — hash(key) % n. Even, and range queries are impossible.

Consistent hashing — hash sharding where adding a node moves 1/n of the keys rather than nearly all of them. If you shard by hash, do this rather than plain modulo.

Directory-based — an explicit lookup table saying which range lives where. Maximum flexibility, one more service to keep available, and it is what a lot of large systems actually run because moving a range becomes a deliberate operation rather than an emergent one.

What you give up

Joins across shards must be done in the application, or by denormalising.

Unique constraints are per-shard unless the unique column is the shard key. Global uniqueness requires a separate service or a UUID.

Every aggregate query becomes a scatter-gather that is as slow as its slowest shard — which, with enough shards, is reliably slow.

Rebalancing means moving data while serving traffic.

Before you shard

Sharding is a large, mostly irreversible increase in complexity. Exhaust the alternatives first, because they are all cheaper:

Vertical scaling. A modern server takes 128 cores and several terabytes of RAM. That is a very large single database, and it costs less than the engineering time sharding consumes.

Read replicas, if reads are the constraint.

Archive old data. Most tables are dominated by rows nobody queries. Moving 2019 to cold storage is a one-week project.

Split by service (functional partitioning). Move analytics to its own database. Simpler than sharding one table, and it often buys years.

Caching. If the same rows are read constantly, a cache removes the load without touching the schema.