Sharding

Why it matters

Indexes, replicas, caching and partitioning all keep one primary alive longer. Sharding is what you do when the writes themselves exceed one machine: split the data across many independent databases, each a primary for its slice. It multiplies write capacity and it removes the things that made a single database pleasant, which is why the order of the scaling steps matters and why interviewers want to hear you shard last and deliberately.

The idea

Sharding (horizontal partitioning across machines) assigns each row to one of N databases based on a shard key. Each shard holds a subset of the data and handles all reads and writes for it independently. A routing layer sends each query to the shard that owns the key.

Choosing the shard key

The shard key decides everything. Good keys appear in nearly every query (so routing is to one shard), distribute data and load evenly, and keep rows that are accessed together on the same shard.

Key	Pros	Cons
Tenant or customer id	Every query in a multi-tenant app carries it; a tenant's data stays together, so joins within a tenant still work	Tenants are uneven; one huge customer can outgrow a shard
User id	Natural for consumer apps; a user's data co-located	Cross-user features (feeds, search) become scatter-gather
Hash of id	Even distribution	Range queries scatter; related rows separate
Geography	Data residency, local latency	Uneven regions; users who move
Time	Simple, append at the end	The newest shard takes all writes: the worst hot shard

What you lose

Cross-shard joins no longer exist in the database; either the join key is the shard key (so the data is co-located), or the application fetches from several shards and joins in memory, or the data is denormalised. Cross-shard transactions require two-phase commit or sagas, covered in the Distribution stage, so invariants that span shards are avoided by design. Unique constraints are per shard; globally unique ids come from a generator (Snowflake-style ids, UUIDv7) rather than a sequence. Aggregates across all data (total revenue today) become scatter-gather or move to an analytics store.

Hot shards and resharding

A single viral tenant or celebrity user can overload one shard while the others idle. Mitigations: finer-grained keys, splitting a hot tenant across sub-shards, or giving the largest tenants dedicated shards. Resharding (changing N or moving ranges) is the operation to design for from day one: use many logical shards (hundreds or thousands of virtual buckets) mapped to fewer physical databases, so moving a bucket is a copy and a routing update rather than a rehash of everything. Directory-based routing (a lookup table of bucket to database) makes this a metadata change; consistent hashing is the alternative covered next.

Tooling

Application-level sharding (the code picks the connection) is simple and leaks everywhere. A proxy or middleware (Vitess for MySQL, Citus for Postgres, MongoDB's mongos) hides routing and handles scatter-gather. Distributed SQL databases (Spanner, CockroachDB, TiDB, YugabyteDB) shard automatically and keep transactions and SQL, at the cost of higher write latency and operational unfamiliarity. The further down that list, the less the application has to know.

Trade-offs

Sharding buys write capacity and data volume that scale with machines, and pays with lost joins and transactions across shards, global uniqueness and aggregation headaches, hot-shard risk and a resharding story you must maintain. It is the right step when a well-tuned primary with replicas, caches and partitions is genuinely saturated, and a costly mistake before that.

In an interview

Shard last and say why: "After indexes, replicas, caching and partitioning, if write volume exceeds one primary we shard by tenant id: every query carries it, a tenant's data stays together so joins still work, and the few largest tenants get dedicated shards. Ids come from a Snowflake generator. We use 1,024 logical buckets mapped to physical databases so resharding moves buckets, not rows by hash. Cross-tenant analytics run in the warehouse, not against the shards."