Database Sharding
The Question
What is database sharding and what are its trade-offs?
What a Strong Answer Covers
- All three strategies. Directory = lookup service.
Senior-Level Answer
Database sharding is horizontal partitioning of data across multiple independent database nodes (shards), each owning a subset of the dataset. Unlike vertical scaling (bigger hardware) or read replicas (copies), sharding distributes both reads and writes, enabling scale beyond what a single node can handle.
There are three main sharding strategies. **Range sharding** assigns contiguous key ranges to shards (e.g., user IDs 1–1M on shard 1). It supports efficient range queries but creates hot spots when access patterns are skewed (e.g., recent records are always on the latest shard). **Hash sharding** applies a hash function to the shard key (e.g., `hash(user_id) % N`) to distribute data uniformly. It eliminates hot spots but makes range queries expensive—they must scatter to all shards. **Directory sharding** maintains an explicit mapping table from key to shard. It's the most flexible (arbitrary assignment, easy rebalancing) but adds a lookup hop and makes the directory a potential bottleneck and SPOF.
Choosing the shard key is the most consequential decision. A good shard key distributes data and queries evenly, is immutable (changing it requires migrating the row), and aligns with the primary query pattern. Bad shard keys cause hot shards that bottleneck the entire system while other shards sit idle.
The major trade-offs: **Cross-shard queries** are expensive. A query that filters on a non-shard-key column must scatter-gather across all shards and merge results in application code or a query router. **Cross-shard transactions** require distributed coordination (2PC or Saga). **Joins** across shards are generally not supported at the database layer and must be done in application code. **Rebalancing** when adding shards is complex—consistent hashing minimizes data movement, but it's still operationally heavy.
Sharding adds significant operational complexity: connection pooling per shard, schema migrations must run on all shards, backups across N nodes, and monitoring per shard. Many teams delay sharding by exhausting vertical scaling, read replicas, caching, and query optimization first. Managed solutions like CockroachDB, Vitess, or DynamoDB handle sharding transparently and are worth evaluating before rolling a bespoke sharding layer.
Key Differences
| Aspect | Range Sharding | Hash Sharding | Directory Sharding |
|---|---|---|---|
| Distribution uniformity | Poor (skewed if access is skewed) | Excellent | Configurable |
| Range query support | Efficient (single shard) | Poor (all shards) | Depends on mapping |
| Hot spot risk | High (temporal keys) | Low | Low |
| Rebalancing complexity | High | High (consistent hashing helps) | Low |
| Extra infrastructure | None | None | Lookup service required |
| Best fit | Time-series, ordered data | User data, even distribution | Multi-tenant, custom routing |
What Separates a 2/3 from a 3/3
2/3 — Passing but Incomplete
Explains range vs. hash sharding with a concrete example, identifies shard key selection as critical, names at least two trade-offs (cross-shard queries, rebalancing).
3/3 — Strong Answer
Covers all three sharding strategies with trade-offs, explains shard key immutability and hot spot problem, discusses cross-shard query scatter-gather, mentions consistent hashing for rebalancing, and recommends evaluating managed solutions before manual sharding.
Common Mistakes
- Conflating sharding with replication — replicas serve read scaling, sharding serves write/storage scaling
- Ignoring the shard key selection problem and assuming any key works
- Not addressing cross-shard transactions and the consistency challenges they introduce
- Overlooking the operational complexity — schema migrations, monitoring, and connection management across shards
Follow-Up Questions
- How does consistent hashing reduce rebalancing cost when adding a shard? — Consistent hashing maps keys and nodes onto a ring. Adding a node only moves keys from its neighbors, not a full reshuffle. Without it, adding a shard may require redistributing all keys.
- What happens when you need to query on a non-shard-key column? — You must scatter the query to all shards and gather/merge results. This is expensive. Mitigation: maintain a secondary index mapping that column to shard keys, or use a separate read model.
- How would you migrate an unsharded database to a sharded architecture with zero downtime? — Double-write to both old and new systems, backfill old data, verify consistency, then cut reads over gradually. This is a multi-week project in practice.
- When would you choose Vitess or CockroachDB over manual sharding? — When you want transparent sharding without application-level scatter-gather, managed rebalancing, and built-in connection pooling. Trade-off: less control, possible vendor lock-in, operational learning curve.
Related Questions
Can You Explain This Cold?
Reading the answer is step one. Explaining it unprompted — under interview pressure — is what actually matters. Get AI-graded feedback on your answer with follow-up probes on your weak points.
Get Graded — Free Assessment