How it works
Start with the bottleneck: a single database cannot comfortably handle the projected storage, write throughput, or working set. Sharding is horizontal partitioning across database instances: different rows live on different shards, usually with the same schema. Splitting a table into partitions on one server alone does not add machine-level capacity.
Choose a shard key from the access patterns. For chat history, conversation_id can keep a conversation's messages together; for a private photo library, user_id can keep a user's metadata together. A useful key distributes both data and traffic while allowing common queries to reach one shard.
Choose a placement scheme. Hash sharding spreads keys but usually requires fan-out for range queries across keys. Range sharding preserves key locality but can concentrate writes, especially with increasing timestamps. Directory-based sharding uses a lookup table to map tenants or key ranges to shards, giving placement flexibility at the cost of maintaining that directory.
Route each request using the shard key. For example, hash conversation_id into one of 1,024 logical buckets, then use a bucket-to-shard map to find the physical database. Logical buckets let you move selected portions of the data without changing every key's assignment. Queries missing the shard key may need a separate index or requests to many shards.
Replicate each shard separately for durability and availability. Sharding places different data on different owners; replication keeps copies of the same data. Adding shards does not automatically add redundancy, and adding read replicas generally does not increase the write capacity of a single-primary shard.
Plan ownership changes before adding capacity. A migration can copy a bucket's snapshot, capture subsequent changes, catch up, and then switch routing through a coordinated ownership handoff. Versioned routing and fencing prevent stale clients or old owners from accepting conflicting writes; validation and an explicit rollback boundary make recovery safer.
A worked example
Sharding chat history by conversation
Suppose a chat service expects 80,000 message writes per second, and a load test shows one database shard can sustain 8,000 at the target latency. Ten shards would leave no headroom, so start planning around 16 and validate the design under skew and failure. Hash conversation_id into 1,024 logical buckets and map 64 buckets to each shard. Within a conversation, index messages by sequence number for history reads. Replicate each shard separately. If one shard becomes busy because it hosts several active conversations, move selected buckets. If one conversation alone exceeds a shard's capacity, bucket migration is insufficient: its write path needs a separate design, potentially splitting storage while explicitly preserving the required ordering.
Trade-offs to say out loud
- More capacity, more operational work
- Shards add storage and potential write throughput, but each one needs monitoring, backups, replication, recovery, and capacity headroom.
- Data locality versus even distribution
- Keeping a tenant or conversation together makes queries simpler, but a large or busy entity can overload its shard.
- Cross-shard queries cost more
- Queries without a shard key may contact many shards and merge results, increasing resource use and exposure to slow responses.
- Transactions and constraints become harder
- Cross-shard atomic updates, joins, and uniqueness checks require explicit distributed coordination or a data model that avoids those boundaries.
- Resharding competes with production traffic
- Moving data consumes disk, network, and database capacity while requiring correct handling of concurrent writes and ownership changes.
- More shards do not fix every hotspot
- A single heavily accessed key still has one owner unless you change its data model, split its work, or offload suitable reads.
When to reach for it
- Storage or write-throughput estimates exceed a practical single-node budget after reasonable indexing, query tuning, and vertical scaling.
- The workload has a natural ownership key, such as user_id, tenant_id, or conversation_id, that appears in most important queries.
- Independent users or tenants generate work that can be spread across database instances without frequent cross-shard transactions.
- A few large tenants need isolated placement or dedicated capacity, with a routing scheme that supports moving them.
How to bring it up in the interview
- Establish the need with a capacity estimate rather than saying 'we will shard for scale'; expect the interviewer to ask why indexing, caching, or replicas are insufficient.
- Name the shard key and walk through the most important reads and writes, including one query that does not contain that key.
- Separate logical shards, physical database placement, and replicas so you can explain both routing and failure behavior clearly.
- Discuss skew explicitly: distinguish a shard with many busy keys from one overloaded key, and explain which remedy addresses each.
- Describe a safe resharding path and transaction boundaries; expect follow-ups about concurrent writes during migration, stale routing, global uniqueness, and partial failures.
Common mistakes
- Choosing a shard key only for even row counts while ignoring request rates, data growth, and query locality.
- Using hash(key) modulo the current server count without acknowledging that changing the count can remap much of the dataset.
- Assuming all joins, unique constraints, and transactions retain their single-database behavior across shards.
- Treating sharding as replication, or assuming losing a shard is harmless because other shards remain available.
- Claiming that adding shards fixes a celebrity account, hot conversation, or other single-key bottleneck.
Where it comes up
Interview questions where database sharding is part of a strong answer, and why.
- Design a distributed key-value storeKey placement, request routing, ownership changes, and hot shards are core parts of designing the storage system.
- Design a chat app like WhatsAppSharding message history by conversation makes storage scalable while exposing important ordering and hot-group trade-offs.
- Design a news feed like FacebookPartitioning stored feeds by recipient distributes feed reads and writes while making celebrity fan-out and workload skew explicit.
- Design a metrics monitoring system like DatadogTime-series placement must balance ingestion load, series locality, retention, and query fan-out under high cardinality.
- Design Google AnalyticsPartitioning event storage must support time-bounded queries without letting large tenants dominate individual shards.
Questions people ask
What is the difference between sharding and partitioning?
Partitioning is the general practice of splitting data into subsets. Database sharding usually means horizontal partitions distributed across separate database instances. A table partitioned by month on one server may improve maintenance or query pruning without increasing the server's total write capacity.
What is the difference between sharding and replication?
Sharding distributes different portions of a dataset across database instances. Replication stores multiple copies of the same portion for availability, durability, or read scaling. A distributed database commonly uses both: several shards, each with its own replicas.
Can a SQL database be sharded?
Yes. An application, middleware layer, or distributed SQL database can route SQL data across shards. The important question is which joins, transactions, and constraints stay local and which require distributed execution; SQL versus NoSQL does not determine whether sharding is possible.