1Understand
The idea
Sharding (horizontal partitioning) splits one logical database into many physical shards so storage and write throughput can grow past one primary’s ceiling. Interviews probe whether you pick a partition key from access patterns, how clients route to the right shard, and what you refuse to promise (cross-shard transactions, scatter-gather on every query).
Sharding trades simplicity for scale. A bad shard key makes every request a fan-out; a hot shard from a celebrity user or a single time bucket can undo the whole layout. Strong answers say why you shard, how you reshuffle later, and what pain you accept on multi-shard reads.
Mention global secondary indexes (duplicate index per shard or lookup service) vs restricted queries, interviews prefer honest query limits over magic “query all shards” every request.
Intuition
Why shard. One primary hits limits: disk size. Write QPS, index RAM, blast radius of one giant table. Sharding splits rows across machines; each shard is still a database, but you own routing.
Partition key must match how you read and write. Shard by user_id if almost every query is “this user’s data.” Shard by tenant_id for B2B isolation. Sharding by created_at helps append-only logs but scatter-gather for “all users.”
Routing styles:
- Hash(user_id) → shard, even spread; point lookups cheap; cross-user queries expensive.
- Range (time), great for time-series; risk hot tail on latest range.
- Directory service, lookup table maps key → shard; flexible resharding, extra hop and consistency for the directory.
Cross-shard pain. JOIN across shards, global COUNT, two-phase commit, avoid in interviews unless you name Saga/outbox. Prefer denormalize, aggregate async, or restrict queries to one shard key.
Hot keys. Fan-out replicas for read-heavy celebrity rows, split key into sub-keys, or cache, sharding alone does not fix skew.
Resharding story: consistent hash + background copy, or directory migration with dual-read window, never hand-wave “we will rebalance live” without steps.
Locality: co-locate data accessed together (conversation id shard) to avoid scatter-gather chat reads.
What you will be able to do
- Pick a partition key that matches how you look data up
- Explain routing to the right shard
- Call out hot shards and cross-shard pain
When this comes up
- Single DB too big
- Write throughput ceiling
- Partition key
- Hot shard
2See it
Architecture sketch
Hash shard by user id
Partition key chooses the shard. Cross-shard queries are the expensive exception.
Walkthrough
Route one write
userId → shard
1 / 3
API accepts a write for user 42.
Read the walkthrough as text
Case, messages table outgrowing one Postgres.
Access: GET /threads/{userId}, POST message with sender_id; no “search all messages globally” in v1.
- Why shard: 2 TB and write QPS climbing; backups and vacuum hurt one node.
- Key:
user_id(or conversation id derived from participants), every read filters by user. - Routing:
shard = hash(user_id) % Nor consistent hash; app or sidecar picks connection string. - Cross-shard: “users in two shards talking”. Pick conversation id as key if both users share one shard per conversation; else accept two-shard read rare or redesign key.
- Hot user: celebrity inbox, monitor shard QPS; optional read replicas on that shard, cache thread list, or dedicated shard.
- Resharding: directory service or consistent hash with dual-write/migrate background job, never claim zero-downtime without a story.
Range shard anti-fit here: sharding only by message_id time hurts “all messages for user U” unless secondary index fans out, say why hash-by-user wins.
- Global admin query: “count all users” → offline batch or aggregate table, not live scatter-gather.
- Shard count: start with N you can operate; doubling shards is a project.
Phrase: “Partition key = user_id; cross-shard checkout → saga, not 2PC.”
Napkin math
partition key = userId (or tenantId)
shards = 8 → route = hash(key) % 8
target per shard ≤ 70% of single-box QPS
hot key → secondary split or cache
cross-shard join = last resort (fan-out)
Pick the key that keeps most queries single-shard.
3Answer it
How to answer in an interview
When “database is too big” or “writes don’t scale”:
- Motivation, storage, QPS, or isolation (not fashion).
- Partition key, from listed queries; reject bad keys.
- Routing, hash, range, or directory; who computes shard id.
- Query fit, which queries stay single-shard; which become scatter-gather or forbidden.
- Transactions, single-shard ACID; cross-shard = saga/outbox, not hand-waved 2PC.
- Hot shard / resharding, monitoring, split, consistent hash migrate.
Draw at least two shards and one example query path.
Say: “Dominant query filters shard key.” · “Hot shard metrics.” · “Resharding via directory or consistent hash migrate.”
Answer checklist
Tradeoffs & numbers
- Hash sharding: even spread; painful global secondary indexes.
- Range sharding: range scans local; tail hotspot on “now.”
- Directory: operational flexibility; directory is its own SPOF/scale problem.
- More shards: smaller blast radius; more connections and ops overhead.
- FK across shards: usually impossible, use application-level ids and async reconciliation.
- Shard count N: plan growth; resharding is a project, not a config flip.
- When NOT to shard: single primary under ~1 TB and moderate write QPS, replicas + cache first.
- Range on time: hot tail on “now” shard, mitigate with rotation/archival.
Reshard cost: doubling shard count is a multi-week project with dual-write, say when you would choose directory vs hash. Scatter-gather admin COUNT(*) → offline aggregate table, not live query. - When NOT to shard: one primary still under ~1 TB with indexed point lookups, replicas + cache first; sharding is last resort.
Kinds of questions (4)
Same pattern, different interview surfaces. Recognise which variant you are in before you answer.
Hash sharding by user id
Example. Social app: every API filters `WHERE user_id = ?` for profile, DMs, and settings; no cross-user joins in v1. Defend the shard key from access patterns, call out hot shards and resharding, and say which queries stay single-shard vs scatter-gather.
How. Application computes `shard_id = hash(user_id) % N`; connection pool per shard; point reads/writes local; explain cross-user chat may need conversation-id key to co-locate both parties.
Range sharding by time
Example. Compliance audit log: inserts are append-only; investigators query `BETWEEN start AND end` for a month. Defend the shard key from access patterns, call out hot shards and resharding, and say which queries stay single-shard vs scatter-gather.
How. Shard by `YYYY-MM`; writes hit current month shard (hot tail); archival job moves cold months to object storage; scatter-gather only for rare cross-month admin reports.
Directory / lookup service
Example. Multi-tenant B2B: enterprise tenants added weekly; you reshuffle without changing app hash function daily. Defend the shard key from access patterns, call out hot shards and resharding, and say which queries stay single-shard vs scatter-gather.
How. Registry table `tenant_id → shard_id` cached in app; dual-read during migration; directory itself is HA SQL or consensus; resharding background job copies tenant rows then flips pointer.
Hot-key fan-out
Example. Celebrity user id maps to one shard doing 80% of read QPS while other shards idle. Defend the shard key from access patterns, call out hot shards and resharding, and say which queries stay single-shard vs scatter-gather.
How. Monitor per-shard QPS; read replicas on hot shard; Redis cache for profile; optional dedicated shard or sub-key split; do not only “add shards” without fixing skewed key.
Common mistakes
Failure modes the AI coach watches for when you practise open questions.
Hot shard
Avoid. Shard by created_at day, today is on fire
Prefer. Shard by userId, load spreads
Weak sketch
Bad
Stronger sketch
Good
Bad shard key
Why it happens. Every query becomes scatter-gather across all shards.
How it fails. Shard by `message_id` but every API filters by `user_id`.
Fix. Pick key from dominant access pattern; co-locate data that is read together.
Ignores hot shard
Why it happens. Skewed keys overload one shard while others sit idle.
How it fails. Celebrity user on same hash shard as everyone else with no cache or split.
Fix. Monitor per-shard QPS; fan-out reads, dedicated shard, or sub-key splitting for hot ids.
Hand-waves cross-shard TX
Why it happens. Distributed ACID across shards is expensive and rarely needed in interviews.
How it fails. “Two-phase commit across 10 shards for checkout” with no saga or outbox.
Fix. Keep transactions single-shard; use sagas, idempotent steps, or redesign partition key.
4Practise
Concept check
A short quiz on this lesson before open questions. Optional, and it does not gate practice.
Open questions
- about 9 min
Shard by user id
A messaging product stores billions of rows. Every query is “messages for user U” or “send as user U.” One Postgres primary is maxed on disk and writes. How do you shard, what is the partition key, and how does one read route to a single shard?
- about 8 min
Range by time
Audit logs are append-only; admins query “events between T1 and T2.” Writes never update old rows. Compare hash sharding vs range sharding by month for this workload. Which do you pick and what hotspot appears on the latest range?
- about 9 min
Directory routing
Tenants move between shards during rebalancing. You cannot recompute `hash(tenantId) % N` on every topology change without massive data movement. Describe a shard directory service: what it stores, how the API uses it, and how you keep the directory highly available.
- about 8 min
Hot shard mitigation
After sharding by user id, one celebrity’s shard has 50× the read QPS of others. Adding more random shards does not help that key. Name at least two mitigations that do not require resharding the entire key space.
Ready to practise?
Write an answer, get AI coaching, then revise. You are not building a real system.