Database and SQL Developer interview question
When do you consider database sharding?
Consider sharding only after a single database and partitioning cannot meet measured scale or isolation needs. Choose a stable shard key, avoid cross-shard transactions, plan rebalancing, routing, hot keys and global queries.
Direct answer
What a strong answer covers
How it works
Sharding splits one logical database across independent instances by a shard key, and it is the last resort because it turns simple things into hard ones: joins across shards, transactions across shards, unique constraints, and global queries all need application-level solutions. Before sharding, the options are a bigger instance, read replicas, caching, archiving cold data, and partitioning within one instance. When sharding is needed, the key must be stable and present in nearly every query, such as tenant or customer, distribute load evenly, and avoid hot keys; routing lives in a library or proxy, rebalancing is planned from day one, and cross-shard reports go through a separate analytical store.
Example
A multi-tenant SaaS with one tenant at 40 percent of traffic sharded by tenant ID with the largest tenants on dedicated shards and the rest hash-distributed; every query already carried the tenant ID, so the routing layer had no ambiguity. Global reporting moved to a warehouse fed by change data capture. The migration ran per tenant behind a routing flag, and rebalancing tooling was built before the first shard filled.
Common mistakes
- Sharding before trying partitioning, replicas, and archiving.
- Choosing a key that is absent from most queries so every request fans out to all shards.
- Sharding by a value that concentrates load on one shard, such as date.
A useful answer structure
Answer from your own work.
Do not memorise a script: use only examples you can explain honestly in follow-up questions.
Open the desktop workspace and rehearse a clear answer in your own words.