How would you shard a database for 1 billion users?

Question

IT Interview Practice · Accepted Answer

Shard by a key that's **present in almost every query and spreads load evenly** — usually a hashed `user_id` — then plan for resharding and cross-shard queries from day one. 1B users won't fit or perform on one node; you split rows across many databases and route each query to the shard that owns it.

```text
                 ┌──────────────┐
Request ─▶ Router/│ shard_of(key)│─▶ Shard 0  (users 0–…)
           Proxy  └──────────────┘   Shard 1
           (Vitess)      │           Shard 2
                         └─────────▶  Shard N   ← each = its own primary+replicas
Directory/lookup table (which key → which shard) ── for range or resharding
```

## Shard key choice — the decision that's hard to undo

Pick a key with **high cardinality, even distribution, and present in most queries**. `user_id` usually wins: user-scoped queries ("my profile, my orders") hit exactly one shard. A bad key (e.g. `country`) creates hotspots — one shard holds all of India while another sits idle.

## Hash vs range

- **Hash sharding** (`shard = hash(user_id) % N`): distributes evenly, kills hotspots — but range queries ("users created last week") must scatter-gather across all shards.
- **Range sharding** (`user_id 0–1M → shard 0`): keeps ranges local and makes resharding easier, but risks hotspots (newest users all land on the last shard).
- Common answer: **hash for even load**, with a **lookup/directory table** so you can move ranges without rehashing everything.

## Resharding — plan before you need it

`% N` is a trap: adding one shard remaps almost every key. Use **consistent hashing** or **virtual buckets** (map keys → 4096 logical buckets → physical shards). Splitting a shard then moves buckets, not the whole keyspace. Tools like **Vitess** (used by YouTube/Slack) automate live resharding with cutover.

## Cross-shard queries

Anything not keyed by the shard key is painful: a global "top posts" query fans out to every shard and merges. Mitigate by **denormalizing** or keeping a separate index/search store (Elasticsearch) for cross-cutting reads.

## Interview perspective

Interviewers probe whether you understand shard-key choice is **semi-permanent** and whether you've thought about **resharding and cross-shard joins** — the two things that bite in production. Good: "shard by user_id." Great: you raise consistent hashing, hot-shard mitigation, and the fact that distributed transactions across shards are best avoided entirely.

**Common trap:** `hash % N` with a fixed N — it makes future growth a full data migration.

## Trade-offs & failure modes

- **Lost cross-shard ACID:** no cheap joins/transactions across shards; use sagas or denormalize.
- **Hot shard:** a viral user; sub-shard or cache that entity.
- **Rebalancing risk:** live migration must double-write and verify before cutover.
- **Operational cost:** N shards × replicas = many databases to monitor, back up, and fail over.

Shard key choice — the decision that's hard to undo

Hash vs range

Resharding — plan before you need it

Cross-shard queries

Interview perspective

Trade-offs & failure modes

Related questions