Database sharding and partitioning: the scaling move an interviewer makes you justify

Database sharding and partitioning: the scaling move an interviewer makes you justify

Every system design answer eventually hits the same wall: one database is not enough. The moment your design has to hold more data or absorb more writes than a single machine can, database sharding stops being a buzzword and becomes the thing you have to reason about out loud. And sharding and partitioning are where an interviewer separates the candidates who scale a database by reflex — "we'll just shard it" — from the ones who can say why now, on what key, and at what cost. This explainer is about being the second kind.

The reason this comes up in almost every system design interview is that the trick everyone reaches for first — add read replicas — only scales reads. The instant the bottleneck is write throughput or raw storage, replicas do nothing, and the only way out is horizontal scaling: split the data across many machines so no single one carries it all. That split is partitioning, sharding is partitioning taken across machines, and the quality of your answer is decided by the one decision underneath it — the shard key. Get the vocabulary straight, learn the three ways to split the data, and learn to pick and defend a shard key, and you have the whole topic.

Why sharding comes up in every system design interview

Nearly every "design X" round reaches a point where you have drawn a single database box and the interviewer asks the quiet question that decides the second half of the round: what happens when this one box isn't enough? A single database server has a ceiling — CPU, memory, disk, and the number of open connections — and once your writes or your dataset cross it, no amount of query tuning or caching moves the ceiling. Read replicas buy you read capacity and nothing else: every replica still has to apply every write, so the write and storage ceiling is unchanged. Sharding is the answer the interviewer is waiting to hear, and then to pressure-test.

"Your users table is 10 TB and growing, and one box can't hold it or keep up with the writes. How do you shard it, and on what key?" That question is the whole topic in one line — it is not enough to say "shard by user"; you have to justify the key against how the data is read and written, and name what you give up.

The rounds here are modelled on how the loop runs, not scraped from any candidate's recall. What matters for prep is the shape: you introduce sharding at the right moment, name a strategy, defend a shard key, and then answer the follow-up about the shard that runs hot — out loud, while someone pushes back.

What the interviewer is really testing

The score is not "did you say the word sharding." A good system design debrief reads on four dimensions, and every move in this topic maps to one: problem solving (did you recognise the moment a single DB and its replicas are exhausted, and reach for the strategy that fits the access pattern), communication (can you justify the shard key in one sentence — high cardinality, even distribution, aligned with the dominant query — and name the trade-off you accept), technical execution (range vs hash vs directory, consistent hashing to bound remaps, scatter-gather and two-phase commit for cross-shard work), and time management (did you introduce sharding when it was needed rather than as your first move, and avoid rat-holing on resharding mechanics unless pushed). Reading this teaches the ideas; it does not teach you to deliver them on the clock.

Partitioning vs sharding: vertical and horizontal

Start by getting the words right, because interviewers notice when they are used loosely. Partitioning is the general act of splitting one logical dataset into smaller parts. It comes in two flavours.

• Vertical partitioning splits by column or feature — you move some columns, or whole tables, into their own store. A user's profile fields live in one place, their billing records in another, their activity log in a third. Each store holds different columns for the same entities. This is how a monolith's database naturally breaks apart as it grows into services.
• Horizontal partitioning splits by row — every partition has the same schema but holds a different subset of the rows. Users 1–1,000,000 here, users 1,000,001–2,000,000 there. Each partition is a complete, self-contained slice of the table.

Sharding is horizontal partitioning where the partitions live on separate machines. Each shard is its own database, on its own hardware, holding its slice of the rows. That distinction is the point: splitting rows into logical partitions on one server buys you nothing against a capacity ceiling; spreading those partitions across machines is what adds CPU, memory, disk and connections.

The sentence that earns points: "Read replicas scale reads because every replica is a full copy; they do nothing for write throughput or storage, because each still applies every write and holds all the data. Sharding is the only thing that scales writes and storage, because each shard owns just its slice." Say that and you have explained not only what sharding is but exactly when it is the right tool.

Sharding strategies: range, hash and directory

Once you have decided to shard, the next question is how do you decide which row goes to which shard? There are three classic strategies, and naming all three before picking one signals range.

A few things to say out loud about each. Range-based sharding keeps range queries fast because a whole range sits on one shard, which is why time-series and ordered data often use it — but that same property is its trap: if your key increases monotonically, every new write targets the last shard and you have built a hotspot. Hash-based sharding fixes the spread by scattering adjacent keys everywhere, at the price of range scans (a "last 7 days" query now hits every shard); and the naive version, hash(key) mod N, is a trap of its own, because changing N when you add a shard remaps almost every key at once — which is exactly the problem consistent hashing solves by moving only a small fraction of keys when the shard count changes. Directory-based sharding is the most flexible — you rebalance by editing a mapping table — but that table is now something every query consults, so it has to be small, cached, and highly available, or it becomes the bottleneck you were trying to escape.

System Design
HLD · Core concept
Pick a sharding strategy and defend it out loud

Practise on Zynter →

Choosing a shard key — and the problems sharding creates

The strategy matters, but the decision that actually makes or breaks a sharded design is the shard key — the column (or combination of columns) whose value decides which shard a row lives on. A good shard key has three properties, and you should name them:

• High cardinality — many distinct values, so the data can be divided finely. Sharding on a boolean or a status column gives you at most a handful of buckets and no room to grow.
• Even access distribution — traffic spreads roughly equally across values, so no shard carries far more than its share.
• Aligned with the dominant query — most reads and writes should be answerable from a single shard. If you shard orders by order_id but almost every query is "all orders for this customer," you have guaranteed that the common path touches every shard.

When a key fails the even-distribution test, you get the hotspot — often called the celebrity problem. Shard a social graph by user and most users are fine, but the one account with fifty million followers sends its shard into the ground while the others idle. The fixes are worth naming: a compound shard key (combine the hot dimension with another, e.g. user plus time bucket) to spread one entity's load, splitting the hot key's data across shards deliberately, or putting a cache in front of the hot key so most reads never reach the shard.

"One of your shards is running 5x hotter than the rest. What did you get wrong, and how do you fix it without taking the system down?" The honest answer names the shard key: it concentrated load — a low-cardinality or skewed key, or a celebrity entity. The fix is a better or compound key and a controlled migration of the hot data to rebalance, done online by double-writing and backfilling rather than with a maintenance window. Admitting the key was the mistake scores better than pretending the strategy was fine.

Sharding also creates costs that did not exist on one machine, and a strong answer volunteers them before being asked. Cross-shard joins and cross-shard transactions are the big ones. A query that spans shards becomes a scatter-gather — fan the request to every shard, then merge the results in the application — which is slower and harder to paginate and rank. A transaction that must update rows on two shards can no longer rely on the database's single-node ACID guarantee; you reach for two-phase commit (correct but slow and prone to blocking) or a saga (a sequence of local transactions with compensating undo steps). The standard advice, and the thing to say, is: design the shard key so the common path stays single-shard, and avoid cross-shard transactions wherever you can. Finally, there is resharding — changing the number of shards or the key when you outgrow the layout — which is painful enough that it is the reason to choose a scheme (consistent hashing, a generous number of virtual shards up front) that minimises how much data moves when you grow.

System Design
HLD · Core concept
Choose a shard key and survive the hotspot follow-up

Practise on Zynter →

Sharding in a system design interview

The thing that separates a clean answer from a muddled one is timing. Do not reach for sharding first. The ordered path an interviewer wants to hear is: a single primary, then read replicas when reads dominate, then caching to absorb the hot reads, and only when writes or storage still exceed one machine do you shard. Introducing sharding at that moment — and saying explicitly "replicas and cache won't help here because the bottleneck is write throughput, so I'll shard" — is the signal that you are reasoning, not reciting. Then name the key, justify it against the access pattern in one sentence, call out the hotspot risk, and state how you will handle the queries that have to cross shards.

Scan the probes that follow, because they rhyme across every round:

• "When do you shard instead of adding replicas?" → When the bottleneck is write throughput or storage, not reads. Replicas copy the whole dataset and apply every write, so they scale reads only; sharding is the only thing that splits writes and storage across machines. (problem solving)
• "Range, hash or directory — which and why?" → Range when range scans dominate and you can manage the sequential-write hotspot; hash (with consistent hashing) when you need even load and can live without cheap range scans; directory when you need flexible rebalancing and can keep the lookup table fast and highly available. (technical execution)
• "What makes a good shard key?" → High cardinality, even access distribution, and alignment with the dominant query so the common path stays single-shard. (communication)
• "One shard is a hotspot — what happened and how do you fix it?" → The key concentrated load; fix with a compound key, splitting the hot entity, or caching in front, and rebalance online. (problem solving)
• "A query has to span shards — how?" → Scatter-gather and merge in the application for reads; for writes across shards, avoid it, or use two-phase commit or a saga, accepting the latency and complexity cost. (technical execution)

Each maps to a debrief dimension. Reading the list teaches you the answers; it does not teach you to deliver them while someone interrupts — that only comes from saying them out loud.

How to prepare: a short plan

You do not need to memorise a database internals textbook. You need these ideas automatic so the next "design X" round, when it reaches the single-database wall, is just the same reasoning with new nouns.

• Day 1: Lock the vocabulary — vertical vs horizontal partitioning, and why sharding (horizontal across machines) is the only thing that scales writes and storage. Say the replicas-vs-sharding sentence until it is reflex.
• Day 2: Drill the three strategies out loud — range, hash (with consistent hashing), directory — and when you would pick each, in one sentence apiece.
• Day 3: Own the shard key — the three properties, the hotspot/celebrity problem and its fixes, and the cross-shard join/transaction and resharding costs. Practise volunteering the costs before you are asked.
• Day 4: A timed HLD round where you reach the sharding moment and defend a shard key against follow-ups, narrating the whole way, with someone (or something) pushing back.

This explainer backs one row of the broader system design interview prep map — once sharding is automatic, the same reasoning carries the key-value store, the CDN and the analytics rounds that all lean on it.

Frequently asked questions

What is the difference between sharding and partitioning?

Partitioning is the general idea of splitting one dataset into smaller parts; sharding is a specific kind of partitioning. Partitioning comes in two forms: vertical, which splits a table by column or feature (profile fields in one store, billing in another), and horizontal, which splits a table by row into subsets that share the same schema. Sharding is horizontal partitioning carried across separate machines — each shard is its own database on its own hardware holding a slice of the rows. The practical point is that logical partitioning on one server does not raise a capacity ceiling, whereas sharding across machines adds CPU, memory, disk and connections, which is why sharding is what scales writes and storage.

What is a shard key and why does it matter so much?

A shard key is the column or combination of columns whose value decides which shard a row belongs to. It matters more than the sharding strategy because it determines how evenly load spreads and whether your common queries stay on one shard. A good shard key has high cardinality (many distinct values so data divides finely), even access distribution (no value attracts far more traffic than others), and alignment with the dominant query (most reads and writes answerable from a single shard). A poor key concentrates traffic on one shard or forces most queries to fan out across all of them, and because changing the key later means resharding, it is a decision you want to get right up front.

Range-based vs hash-based sharding — when do I use each?

Use range-based sharding when range scans dominate your workload, because a contiguous range of keys lives on a single shard and those queries stay local; the risk is that monotonically increasing keys like timestamps or auto-increment ids send every new write to the newest shard and create a hotspot. Use hash-based sharding when you need even load distribution and can live without cheap range scans, because hashing scatters adjacent keys across all shards; pair it with consistent hashing so that adding or removing a shard moves only a small fraction of keys instead of remapping everything, which naive hash mod N would do.

What is the hotspot or celebrity problem in sharding?

A hotspot is a single shard that receives far more traffic than the others, usually because the shard key concentrates load. The classic case is the celebrity problem: shard a social platform by user and most users are evenly spread, but one account with tens of millions of followers overwhelms its shard while the rest idle. The same happens with low-cardinality keys or sequential keys under range sharding. Fixes include a compound shard key that combines the hot dimension with another (such as user plus a time bucket), deliberately splitting the hot entity's data across shards, and placing a cache in front of the hot key so most reads never reach the shard.

How do queries work when the data spans multiple shards?

A read that spans shards becomes a scatter-gather: the application fans the request out to every relevant shard, each returns its local result, and the application merges, sorts and paginates them — slower and more complex than a single-shard query, which is why you try to design the shard key so the common path stays on one shard. A write or transaction that spans shards can no longer rely on a single database's ACID guarantee, so you either avoid it, use two-phase commit (correct but slow and prone to blocking), or use a saga, which is a sequence of local transactions with compensating actions to undo on failure. The general rule is to keep the frequent path single-shard and treat cross-shard operations as the expensive exception.

How do I practise sharding questions out loud?

Reading about sharding is not the same as defending a shard key while someone asks why one shard is on fire. On Zynter, the high-level design track runs live voice rounds modelled on how real system design loops run — you draw the architecture on a shared canvas, reach the single-database wall, and a voice interviewer pushes with the follow-ups above one at a time, then gives you a debrief on problem solving, communication, technical execution, and time management. The score is a practice signal, not a hiring decision.

Practise it on Zynter

You can read what a shard key is. The round is won on defending the one you chose out loud.

Pick a high-level design round, scale it past the single-database wall on the canvas, and defend your shard key to a voice interviewer that keeps asking "on what key, and why?" and "one shard is 5x hotter than the rest — what went wrong?" — then read the debrief. Zynter.ai is on-demand mock interviews modelled on real company loops, with 150+ rounds across 20 companies including the HLD system design track (Sep 2026).

Practise an HLD round →
Browse all interview tracks