Sharding

Partitioning strategies, choosing a shard key, hot spots and cross-shard queries.

When a single database can't hold all your data or handle all your traffic, you split it across multiple machines. Each piece is a shard (or partition). Sharding is powerful but expensive in complexity — so the first thing to show an interviewer is that you know when you need it.

Do you actually need to shard?

Before sharding, exhaust the simpler options:

Vertical scaling — a large modern database server has 64+ cores, hundreds of GB of RAM and many TB of SSD. That goes a long way.
Read replicas — if the problem is read load, replicas solve it without sharding.
Caching — reduces read load further.
Archiving — move cold data out of the hot database.

Shard when write throughput or total data size exceeds what one primary can handle. A rough heuristic: think about it past ~10k sustained writes per second or several TB of hot data.

Partitioning strategies

Choosing a shard key

The shard key is the most important decision. A good key:

Distributes load evenly — high cardinality, no single value dominating
Matches your most important queries — so they hit exactly one shard
Doesn't change — moving a row between shards is painful

[!EXAMPLE] Sharding a chat app Shard messages by conversation_id. "Load the latest messages in this conversation" — the dominant query — then hits a single shard. Sharding by sender_id would scatter every conversation across many shards.

Problems sharding creates

Hot spots: a celebrity's user ID or a viral event concentrates traffic on one shard. Fixes: add a random suffix to hot keys (splitting them across shards), or give hot entities dedicated capacity.
Cross-shard queries: anything not keyed by the shard key becomes a scatter-gather across all shards. Keep a secondary index (or a separate store like Elasticsearch) for those queries.
Cross-shard transactions: atomic updates spanning shards need two-phase commit or sagas. Design to avoid them — keep entities that change together on the same shard.
Rebalancing: adding shards means moving data while serving traffic. Consistent hashing or a directory keeps this manageable.
Unique IDs: auto-increment no longer works globally. Use UUIDs or Snowflake-style IDs (timestamp + machine ID + sequence).

[!INTERVIEW] Name the key and the query it serves Saying "we'll shard" is weak. Saying "we'll shard bookings by event_id, because every booking-path query is scoped to one event — and the cost is that 'all bookings for a user' needs a secondary index" is strong.