When to shardWhen to use
%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","actorBkg":"#e7f5ff","actorBorder":"#1e1e1e","noteBkgColor":"#fff9db","noteBorderColor":"#1e1e1e"}}}%%
graph TD
Start["single DB hitting limits"] --> V{"bigger machine<br/>enough?"}
V -->|yes| Done["keep the current setup"]
V -->|no| R{"read replicas<br/>enough?"}
R -->|yes| Done
R -->|"no, writes still bottlenecked"| C{"caching<br/>enough?"}
C -->|yes| Done
C -->|no| Shard["time to shard"]
class Start yellow
class V,R,C blue
class Done green
class Shard red
classDef orange fill:#ffd8a8,stroke:#1e1e1e,color:#1e1e1e
classDef blue fill:#a5d8ff,stroke:#1e1e1e,color:#1e1e1e
classDef green fill:#b2f2bb,stroke:#1e1e1e,color:#1e1e1e
classDef red fill:#ffc9c9,stroke:#1e1e1e,color:#1e1e1e
classDef violet fill:#d0bfff,stroke:#1e1e1e,color:#1e1e1e
classDef yellow fill:#ffec99,stroke:#1e1e1e,color:#1e1e1e
classDef grey fill:#e9ecef,stroke:#1e1e1e,color:#1e1e1eChoosing a shard keyHow it works
The shard key decides which shard each record lives on. It is the most important decision: a bad key creates hot spots (one shard doing most of the work), which cancels out the benefit of sharding.
User IDcommon
All of a user’s data sits on one shard, which suits user-centric queries. Risk: very active users create hot spots.
Geographic region
Data sits near its users, which cuts latency. Risk: regions are uneven in size.
Timestamp
Time-series data shards naturally and suits analytics. Risk: “now” is always the hot shard.
Sharding strategiesPattern
Hash-based sharding
Hash the shard key: shard = hash(key) % numShards. Data spreads evenly, but adding or removing a shard changes the result for almost every key, so nearly everything has to move.
%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","actorBkg":"#e7f5ff","actorBorder":"#1e1e1e","noteBkgColor":"#fff9db","noteBorderColor":"#1e1e1e"}}}%%
graph LR
K["user_id = 12345"] --> H["hash = 78901234"]
H --> M["78901234 % 4 = 2"]
M --> S["Shard 2"]
class K blue
class H,M yellow
class S green
classDef orange fill:#ffd8a8,stroke:#1e1e1e,color:#1e1e1e
classDef blue fill:#a5d8ff,stroke:#1e1e1e,color:#1e1e1e
classDef green fill:#b2f2bb,stroke:#1e1e1e,color:#1e1e1e
classDef red fill:#ffc9c9,stroke:#1e1e1e,color:#1e1e1e
classDef violet fill:#d0bfff,stroke:#1e1e1e,color:#1e1e1e
classDef yellow fill:#ffec99,stroke:#1e1e1e,color:#1e1e1e
classDef grey fill:#e9ecef,stroke:#1e1e1e,color:#1e1e1eRange-based sharding
Give each shard a range of keys: shard 0 gets 0–999, shard 1 gets 1000–1999. Simple, and range queries are efficient, but skewed access creates hot spots.
Consistent hashing
Place keys and shards on a ring. Adding or removing a shard only moves the keys next to it. See the consistent hashing tutorial for details.
Architecture: the shard routerDesign
%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","actorBkg":"#e7f5ff","actorBorder":"#1e1e1e","noteBkgColor":"#fff9db","noteBorderColor":"#1e1e1e"}}}%%
graph LR
Client["Client"] --> Router["Shard router"]
Router --> S1[("Shard 1<br/>users 0–999K")]
Router --> S2[("Shard 2<br/>users 1M–1.9M")]
Router --> S3[("Shard 3<br/>users 2M–2.9M")]
Router --> S4[("Shard 4<br/>users 3M+")]
class Client blue
class Router orange
class S1,S2,S3,S4 green
classDef orange fill:#ffd8a8,stroke:#1e1e1e,color:#1e1e1e
classDef blue fill:#a5d8ff,stroke:#1e1e1e,color:#1e1e1e
classDef green fill:#b2f2bb,stroke:#1e1e1e,color:#1e1e1e
classDef red fill:#ffc9c9,stroke:#1e1e1e,color:#1e1e1e
classDef violet fill:#d0bfff,stroke:#1e1e1e,color:#1e1e1e
classDef yellow fill:#ffec99,stroke:#1e1e1e,color:#1e1e1e
classDef grey fill:#e9ecef,stroke:#1e1e1e,color:#1e1e1eThe challenges of shardingScale
Cross-shard queries
Joins across shards are expensive: you query several shards and merge in the app. Denormalise (store copies of data) to avoid them.
Distributed transactions
ACID across shards needs two-phase commit (2PC), which is slow and complex. Prefer eventual consistency where you can.
Rebalancing
Adding a shard means moving data, which can cause downtime if done carelessly. Consistent hashing keeps the move small.
Global unique IDs
Auto-increment IDs collide across shards. Use UUIDs, Snowflake IDs or a central ID generator.
Common interview mistakesPitfalls
SummarySummary
| Strategy | Pros | Cons |
|---|---|---|
| Hash-based | Even distribution | Adding shards moves almost every key |
| Range-based | Simple; range queries are efficient | Hot spots on the current range |
| Consistent hashing | Minimal data movement | More complex to implement |
Frequently asked questionsFAQ
What is database sharding and when do you need it?
Sharding splits one large database into several smaller databases, each holding a subset of rows so writes and storage scale horizontally. You need it when a single instance hits CPU, memory, or write-throughput limits and cannot comfortably serve the data or load on one machine.
How do you pick a good shard key?
The shard key decides which node stores each row, so it must spread data evenly and match your common query patterns. A poor key concentrates hot rows on one shard, creating a hotspot, while a good key such as a user ID distributes writes and keeps reads local per user.
How do you handle joins and transactions across shards?
They become cross-node and expensive, which is why sharded schemas are designed to avoid them. Prefer denormalization and single-shard queries keyed by the shard key; when a truly global operation is needed, use a coordinator or rely on eventual consistency with idempotent retries.
Now explain database sharding to me out loud
I'll give you a design problem and push on the trade-offs, like a real interviewer.