Our single database can no longer hold the write volume, so we're going to shard it. How do you pick a shard key, and what goes wrong if you pick a bad one?

system-design · Mid level · software-engineering

What the interviewer is really asking

Assesses whether the candidate can reason about shard-key selection against load distribution and the dominant access pattern, the hotspot/skew failure modes of a bad key, and why resharding is expensive — core sharding judgment, not just 'split the data.'

What to say

• Choose the shard key from the dominant query pattern and even distribution: it should spread both writes and reads across shards and let your most common queries hit a single shard, so pick a key with high cardinality and balanced access (often a tenant or user id rather than something monotonic).
• Name the failure modes of a bad key: low-cardinality or monotonically increasing keys (a timestamp, an auto-increment id) create hotspots where one shard takes all the recent writes, and a key misaligned with queries forces scatter-gather across every shard.
• Address mapping and growth: use consistent hashing (or a lookup/directory layer) so adding a shard only remaps a small slice of keys rather than nearly all of them, and accept that cross-shard queries and transactions get harder — so the key choice and the decision to shard are deliberate, near one-way doors.

What to avoid

• Picking a shard key casually without considering data distribution or the dominant access pattern ("just shard by id").
• Using a monotonically increasing key (timestamp or sequential id) and not realizing it concentrates all recent writes on one shard.
• Ignoring resharding cost and assuming you can change the shard key or add shards later without a painful, large-scale migration.

Example answers

Strong: I'd pick the shard key from the main access pattern and aim for even spread. For a multi-tenant app that's usually tenant_id — high cardinality, keeps one tenant's data co-located so common queries hit one shard, and spreads load. I'd avoid monotonic keys like a timestamp or auto-increment id, because they funnel every new write onto the newest shard — an instant hotspot. For mapping I'd use consistent hashing so adding a shard only moves a fraction of keys, not almost all of them. And I'd go in knowing cross-shard joins and transactions get hard, so I treat the key choice as nearly irreversible and decide it carefully.

Weak: I'd shard by user id using a hash so the data spreads out across the shards evenly. Then each shard handles its share of the load.