What is sharding in a database, and how does it work?

In this article

1. Before you start
2. Why interviewers ask this
3. Sharding from first principles
4. Worked example
5. Trade-offs and edge cases
6. How to answer it in an interview
7. Common mistakes
8. Follow-up questions
9. Sources

Before you start

This page is one step of the system design learning path. If you have not yet estimated storage and load, read back-of-the-envelope estimation first; it supplies the arithmetic used in the worked example.

Why interviewers ask this

A design round reaches sharding the moment your numbers no longer fit on one database server. Once you say "we will shard", the interviewer can test several things at once: whether you know what a shard key is, whether you can name the cost of choosing a bad one, and whether you can explain what happens when the system grows.

A weak answer says "we split the database into pieces by user id" and stops. A strong answer states the trigger that justified sharding, names the strategy and why, explains how a query finds its shard, and says what you give up: cross-shard joins, cross-shard transactions and painful rebalancing. A Staff-level answer adds the order of operations: exhaust simpler options, pick the key from the dominant query, and plan the resharding path before the first row is written. The system design round overview shows where this topic sits in the wider interview.

Sharding from first principles

Shard key

A shard key is the column or columns of a row whose value decides which shard stores that row, also called the partition key, so every read and write must be able to compute the owning shard from it.

Hotspot

A hotspot is a shard that receives a disproportionate share of the load or data, so it saturates while other shards sit idle and the cluster scales worse than its size suggests.

In plain words

Picture a library with one huge room. When it fills up, you could buy a bigger room, but eventually no bigger room exists. Instead you open four branches and give each branch a range of the alphabet, or a rule such as "a member's number decides the branch". A member only visits their own branch, and each branch is small enough to run well. The analogy breaks in one place. A branch can lend a book to another, but a database query needing rows from two shards must ask both servers and merge the answers.

In one precise sentence, sharding is horizontal partitioning in which each partition lives on a separate server. The MongoDB manual describes the same idea as horizontal scaling. It divides the dataset and load over multiple servers, each handling a subset of the workload. The alternative, vertical scaling, means a more powerful single server, and it has a practical maximum.

Step 1: A shard key picks the dimension that splits the data

The shard key is the first and most expensive decision. The Azure Architecture Center treats it as a decision to make carefully before any code is written, because changing it later means migrating all the data to a new layout. It advises a key that does not change, has many distinct values, spreads data and load evenly, and matches your most common query so that most requests hit one shard.

Input: a row. Output: the key value that will be mapped. Bad keys are easy to name: an autoincrement number or sequential timestamp sends every new write to the newest shard, and a boolean has too few values to split anything. A tenant identifier or user identifier is a common good choice. If no single column works, combine two into a composite key. You now know what is being split; the next step turns that key into a server.

Step 2: A mapping rule turns the key into a shard, and the rule sets the cost of growth

A mapping rule is the function from shard key to shard. Azure lists four families: lookup (a directory table), range (sorted key ranges), hash (a hash function of the key) and geographic (the region of the user). Each has different consequences, compared in the table later on this page.

The simplest hash rule is hash(key) mod N, where N is the number of shards. It spreads keys evenly: with 100,000 keys and four shards, each shard received between 24,905 and 25,117 keys.¹ But Azure warns that with this rule, adding or removing a shard reassigns most keys. Going from four shards to five moved 80,144 of 100,000 keys, or 80.14%.¹ The same keys moved to a ring-based scheme, consistent hashing (Karger et al., 1997; see consistent hashing and the distributed cache), moved 22.15%.¹ Input: a key and N. Output: a shard number. You now know why the rule, not just the key, matters.

1 · Shard key2 · Mapping rule3 · Router4 · GrowthReplay

Step-by-step description

1. Each row carries a shard key.
2. A rule turns the key into a shard.
3. The router sends the query to one shard.
4. A new shard takes some keys.

• The shard key of a row is the input to the mapping rule.
• A router sends each query to the shard that owns the key.
• Adding a new shard moves some keys to it.

Step 3: A router sends each query to the shard that owns the key

Something has to apply the mapping rule on every request. Azure says this sharding logic can live in the application's data access code or in the data store itself when it supports sharding transparently. In MongoDB, a router process called mongos is the interface between client applications and the cluster, and config servers store the cluster's metadata. MongoDB's manual also requires each shard to be deployed as a replica set, which means every shard is itself replicated.

Input: a query containing the shard key. Output: one shard is contacted. A query without the shard key cannot be routed, so it must go to every shard and the results are merged. Azure notes that even with parallel fan-out, the slowest shard determines overall latency. This is why the dominant query should include the shard key. You now know how reads and writes find their data.

Step 4: Adding shards moves data, so plan the rebalancing

Data skew builds up over time, so Azure advises planning to rebalance periodically, and says rebalancing often causes downtime or reduced throughput. The fix it recommends is virtual partitions: map many logical partitions to fewer physical shards, so an overloaded shard hands some logical partitions to a new server without rehashing everything. It also advises preferring many small shards over a few large ones, because small shards migrate faster.

Range sharding is the hardest to rebalance, because ranges must be split and merged, and moving data may not help if activity concentrates on adjacent keys. MongoDB lets you change a collection's shard key in place starting with version 5.0 (MongoDB manual, read October 2026), called resharding, but treat that as a repair tool, not a plan. Input: an overloaded shard. Output: some partitions move to a new shard. Next, the worked example puts numbers on all four steps.

Worked example

Suppose a service must store 12 terabytes and sustain 30,000 writes per second at peak, and one server can hold 2 terabytes and take 8,000 writes per second (all four inputs are illustrative; the estimation method is in back-of-the-envelope estimation). Storage needs 6 shards and write load needs 4, so the answer is the larger of the two: 6 shards.¹

Now compare how two rules behave on 100,000 sequential user numbers. With four shards and hash(key) mod N, the newest 10,000 writes landed 2,518, 2,488, 2,466 and 2,528 on the four shards. With range sharding on an increasing number cut into four equal ranges of 25,000, all 10,000 of them landed on the fourth shard, the one holding the highest range: a hotspot that moves to whichever shard owns the top of the key space.¹

The chart shows what happens when a fifth shard is added.¹ The ring moved 22.15% rather than the ideal 20% because, even with 100 virtual nodes per shard, the arcs the shards own are uneven. The mod rule's 80% follows from the fact that only keys with the hash mod 20 in {0, 1, 2, 3} stay put.

Text description of the diagram

• Adding a fifth shard moves 80.14% of keys with a mod rule.
• Adding a fifth shard moves 22.15% of keys with a hash ring.

A last number shows why fan-out hurts. If each shard is slow for 1% of requests (illustrative), a query touching one shard is slow 1% of the time, but a query touching eight shards is slow 7.73% of the time, assuming each shard is slow independently, because it waits for the slowest one.¹ This is the tail latency effect.

Trade-offs and edge cases

Sharding buys storage and write capacity and costs simplicity. Azure lists the burdens: monitoring across all shards, backups of each shard with restores that must stay consistent across shards, and coordinated schema changes. Cross-shard transactions need distributed coordination such as two-phase commit, which adds latency and failure modes, so designs usually keep each transaction inside one shard and make cross-shard steps idempotent or eventually consistent.

MongoDB has supported distributed transactions across shards since version 4.2, but each one costs coordination latency (MongoDB transactions documentation), so designs still keep most operations on one shard. A design that needs strong integrity across shards, such as moving money between accounts, should reconsider its key or its use of sharding; the payment system design shows how that plays out.

Text description of the diagram

• Range sharding suits range queries but balances load poorly.
• Hash sharding balances load but is hard to rebalance without consistent hashing.
• Lookup sharding adds a map but eases moving data.

Range sharding suits range queries but balances load poorly. Hash sharding balances load but is hard to rebalance without consistent hashing. Lookup sharding adds a map but eases moving data.

Edge cases to name: reference data that every query needs can be copied to every shard; secondary lookups on non-key columns need an index table; geographic sharding meets data residency rules but can skew load if users cluster in one region. Sharding also complements other partitioning: Azure notes a shard can hold vertically partitioned entities.

Sharding is not the first remedy either: vertical scaling only postpones the limit, so a strong answer says why the cheaper options are exhausted. Before sharding, rule out vertical scaling, read replicas and caching (which offload read traffic when reads, not writes or storage, are the bottleneck) and the engine's own table partitioning (Azure Architecture Center). Sharding questions also turn up in database interview questions and backend interview questions.

How to answer it in an interview

The spoken structure: justify sharding, pick the key, pick the mapping, say how queries route, then name the costs.

Common mistakes

• "Sharding and replication are the same." (wrong) Replication copies the same data; sharding splits different data. Say instead: "Each shard is replicated, so I use both: sharding for capacity, replication for availability." (right)
• "I'll shard by order date." (wrong) Sequential keys send all new writes to one shard. Say instead: "I'll hash the customer identifier, and use a range key only if range queries dominate." (right)
• "Just add a server when it fills up." (wrong) With a mod rule most keys move. Say instead: "I use virtual partitions or consistent hashing so growth moves a small fraction." (right)
• "We'll join across shards as usual." (wrong) Cross-shard queries add latency and complexity. Say instead: "I keep related entities under one shard key and denormalize." (right)

Follow-up questions

What is the difference between sharding and partitioning?

Partitioning is the general idea of splitting data; sharding is horizontal partitioning in which each partition lives on its own server, as the Azure pattern describes. Partitioning can also be vertical, splitting columns, and the two can be combined.

What is the difference between sharding and replication?

Replication stores copies of the same data on several servers for availability and read capacity; sharding stores different rows on different servers for storage and write capacity. In MongoDB each shard is deployed as a replica set, so production systems use both.

When should you not shard a database?

A strong answer says to shard only after simpler steps no longer cover your estimated storage or load, because sharding adds operational burden and loses easy joins and transactions. Rule out vertical scaling, read replicas, caching and the engine's own table partitioning first; vertical scaling raises the ceiling but does not remove it.

What is a hot shard and how do you fix it?

A hot shard, or hotspot, takes disproportionate load, often from a sequential key or one very active tenant. Fixes are a better key, hashing, a composite key, or giving the busy tenant its own dedicated shard.

Can you run transactions across shards?

You can with distributed protocols such as two-phase commit, and MongoDB has supported them across shards since version 4.2, but they add coordination latency and failure modes, so designs keep each transaction inside one shard and make cross-shard steps idempotent or eventually consistent. The concurrency interview questions cover the related consistency ideas.

Sources

1. Sharding - Database Manual (MongoDB Docs) — MongoDB (read 2026-10-26; page undated), 26 Oct 2026
2. Sharding pattern - Azure Architecture Center — Microsoft Learn, 11 Oct 2026
3. Transactions - Database Manual (MongoDB Docs) — MongoDB (read 2026-10-26; page undated), 26 Oct 2026
4. Consistent hashing and random trees (Karger et al., STOC 1997) — ACM (Karger et al.), 4 May 1997