System Design
Database Sharding
When your data outgrows a single machine, sharding is how you spread it across many - each holding a slice. It's the standard way to scale a database's writes and storage horizontally. Here's how it works, and where it gets hard.
There comes a point in a growing system where a single database machine simply can't keep up - either the data is too large to fit, or the write volume is too high for one machine to handle. You can make the machine bigger for a while (vertical scaling), but that has a ceiling. Eventually you have to spread the data across multiple machines. Sharding is the technique for doing exactly that: splitting a database horizontally across many machines, each holding a slice of the data.
Sharding is the standard answer to scaling a database's writes and storage beyond one machine - and it's also one of the more challenging things to get right. This guide explains what sharding is, how data gets split, how queries find the right shard, and the real challenges that make sharding a serious decision rather than a casual one.
What sharding is
Sharding means splitting your data across multiple database machines, where each machine (a shard) holds a subset of the total data. Instead of one database holding all the rows, you have many databases, each holding some of the rows. Together, the shards hold the complete dataset; individually, each holds only its piece.
The key word is horizontal: you're splitting the rows of your data across machines (each shard has the same structure but different rows), not splitting the columns or the features. If you have a billion users, you might put users 1–100 million on shard 1, the next 100 million on shard 2, and so on - same table structure on each shard, different users.
This is fundamentally different from replication. Replication copies the same data to multiple machines (for read scaling and redundancy). Sharding splits different data across machines (for write and storage scaling). Replicas all have everything; shards each have a piece. Real systems often use both - sharding to split the data, and replication within each shard for redundancy.
graph TD
APP[Application] --> ROUTE[Routing: which shard?]
ROUTE --> S1[(Shard 1<br/>users 1–100M)]
ROUTE --> S2[(Shard 2<br/>users 100M–200M)]
ROUTE --> S3[(Shard 3<br/>users 200M–300M)]
This diagram shows database sharding. The application sends a request to a routing layer, which determines which shard holds the relevant data based on a sharding key. The data is split across three shards: shard 1 holds users 1 to 100 million, shard 2 holds the next 100 million, and shard 3 the next. Each shard has the same table structure but a different subset of the rows.
The shard key: the most important decision
Everything about sharding hinges on the shard key (or partition key) - the piece of data used to decide which shard a given row belongs to. When you store or look up a row, the system uses its shard key to determine which shard it lives on. Choosing the shard key well is the single most important decision in sharding, because it determines how evenly data and load spread across shards, and how efficiently queries run.
A good shard key spreads data and load evenly across shards (no single shard overwhelmed while others sit idle) and aligns with how you query the data (so common queries hit a single shard rather than all of them). A poor shard key creates hot spots (one shard taking most of the load) or forces queries to hit every shard. We'll return to why this is so consequential.
How data is split: sharding strategies
There are several strategies for mapping rows to shards, each with trade-offs:
- Range-based sharding. Divide data into ranges of the shard key - users 1–100M on shard 1, 100M–200M on shard 2, and so on. Simple, and great for range queries (a range of keys lives on one shard). But it risks uneven distribution: if data clusters in certain ranges, some shards get overloaded (a hot spot).
- Hash-based sharding. Apply a hash function to the shard key and use the result to assign the row to a shard. This spreads data evenly (a good hash distributes keys uniformly), avoiding the hot spots range sharding can create. The downside: range queries become inefficient, because consecutive keys are scattered across shards rather than grouped.
- Directory-based sharding. Maintain a lookup table that maps each key (or key range) to a shard. This is flexible - you can place data wherever you want and move it around - but the lookup table is an extra component that must be fast and reliable (and not become a bottleneck itself).
- Geographic sharding. Shard by location, so data is stored near the users who access it (and to satisfy data-residency rules). Useful for global systems, with the same hot-spot risk if regions are uneven.
The choice depends on your access patterns. Hash-based sharding is a common default because it distributes evenly; range-based suits range-query-heavy workloads; directory-based offers flexibility at the cost of the lookup layer.
Routing: finding the right shard
Once data is sharded, every query needs to reach the right shard. This routing - determining which shard holds the data for a given key - happens through a routing layer (which could be logic in the application, a separate routing service, or a feature of the database). When a query comes in with a shard key, the router applies the sharding strategy (compute the hash, check the range, consult the directory) to find the shard, and sends the query there.
The cleanest case is a query that includes the shard key - it routes straight to one shard, efficiently. The problematic case is a query that doesn't include the shard key (or needs data from multiple shards), which we'll see is one of sharding's main challenges.
The hard parts of sharding
Sharding solves the scale problem but introduces genuine complexity. The challenges that make it a serious decision:
- Cross-shard queries. A query that needs data from multiple shards (or doesn't include the shard key) must hit every shard and combine the results - slow and complex. Sharding works beautifully for queries that target a single shard and poorly for queries that span shards. This is why aligning the shard key with your query patterns matters so much.
- Cross-shard joins and transactions. Joining data that lives on different shards, or running a transaction that touches multiple shards, is hard - the clean joins and transactions of a single database become complex distributed operations. Often you design to avoid needing them.
- Hot spots / uneven distribution. If the shard key distributes data or load unevenly, some shards get overwhelmed while others idle, undermining the whole point. A celebrity user, or a popular key, can make one shard a bottleneck.
- Resharding. As data grows, you may need to add shards and redistribute data - and resharding a live system without downtime is genuinely difficult. This is why the shard key and strategy must be chosen carefully up front; changing them later is painful.
- Operational complexity. Many database machines instead of one means more to monitor, maintain, back up, and reason about. Sharding adds real operational burden.
Tip: Emeka's note from production: The shard key is everything, and I cannot overstate how much pain a bad one causes. Choose a key that distributes load evenly and matches your most common queries, so those queries hit a single shard. The classic mistake is sharding by something that creates hot spots - sharding by date when most activity is recent (the newest shard gets hammered while old shards idle), or by a key where a few values dominate. And think hard before you shard at all: it's a one-way door that's costly to undo. I've seen teams shard prematurely, taking on all this complexity for scale they hadn't reached, when a bigger single machine plus caching and read replicas would have carried them for years. Shard when you genuinely must - when one machine truly can't hold the data or handle the writes - not before.
A worked example: choosing a shard key
Let me make shard-key choice concrete, because it's where sharding succeeds or fails. Suppose you're sharding the database for a large messaging app, and the dominant query is "get all messages for a given conversation." What should the shard key be?
A tempting choice is the message's own ID or timestamp - but that's a trap. Sharding by timestamp means all recent messages land on the newest shard, so that one shard takes nearly all the write traffic while older shards sit idle: a classic hot spot. Sharding by message ID scatters a single conversation's messages across every shard, so the dominant query ("all messages in this conversation") has to hit every shard and merge results - slow and expensive.
The better choice is to shard by conversation ID. Now all messages for a conversation live on the same shard, so the dominant query routes to a single shard - fast and clean. And because conversations are spread across many users, the load distributes reasonably evenly, as long as no one conversation is absurdly larger than the rest. The shard key aligns with the access pattern and distributes load - exactly what a good shard key does.
Notice the reasoning: look at your dominant query, choose a key that keeps that query on one shard, and check that the key distributes load evenly. Get both right and sharding works; get either wrong and you have hot spots or scatter-gather queries. This is why I say the shard key is the whole ballgame.
Handling cross-shard queries in practice
Even with a good shard key, some queries will need data from multiple shards - and how you handle them matters:
- Design to avoid them. The best strategy is choosing the shard key so your important queries hit a single shard, accepting that rare cross-shard queries are slow. Most of the design effort goes here.
- Scatter-gather. For queries that genuinely must span shards, the router sends the query to all relevant shards, gathers the results, and combines them. This works but is slower (you wait for the slowest shard) and more complex, so you minimize how often it's needed.
- Maintain a separate store for cross-cutting queries. If you frequently need queries that don't fit the shard key (analytics across all shards, search across all data), the answer is often not to force the sharded database to serve them, but to feed the data into a separate system built for those queries - a search index, a data warehouse - kept in sync (often via change data capture). The sharded database serves its sharded access pattern; the other store serves the cross-cutting one.
The throughline: sharding optimizes for queries that align with the shard key, and you architect around the queries that don't.
Resharding and consistent hashing
One of sharding's hardest operational problems is resharding - redistributing data when you add or remove shards as the system grows. A naive scheme makes this brutal: if you assign rows to shards by "hash modulo number-of-shards," then adding one shard changes the modulo, which remaps almost every key to a different shard, forcing a massive data migration.
The technique that tames this is consistent hashing, which assigns keys to shards so that, when you add or remove a shard, only a small fraction of keys need to move (rather than nearly all of them). This makes growing the cluster far less painful. Consistent hashing is a foundational technique for exactly this reason - it's what lets a sharded or distributed system add and remove nodes without reshuffling everything. (See the dedicated guide on consistent hashing for how it achieves this.) Knowing that naive hash-modulo sharding makes resharding catastrophic, and that consistent hashing is the fix, is one of the real operational realities of running sharded systems.
Sharding and replication together
A final clarification, because the two are easily conflated: sharding and replication solve different problems and are typically used together. Sharding splits the data across machines to scale writes and storage; replication copies data for redundancy and read scaling. In a real production setup, you shard the data across many shards, and each shard is itself replicated (a primary with replicas) so that no shard is a single point of failure and each shard's reads can be spread across its replicas. So a large sharded database is often a grid: many shards (for scale) each with several replicas (for reliability and read scaling). You don't choose between sharding and replication - you combine them.
When to shard (and when not to)
Sharding is powerful but costly in complexity, so the decision matters:
Shard when a single database machine genuinely can't handle your data size or write volume, even after vertical scaling, caching, and read replicas. That's the real trigger: you've exhausted the simpler options and the write/storage load truly exceeds one machine.
Don't shard when simpler approaches still have headroom. Read replicas handle read scaling; caching relieves read load; a bigger machine handles a lot. Because sharding adds so much complexity and is hard to undo, it should be a later resort, not a first move. The order is usually: optimize and cache, scale up the machine, add read replicas, and only then, when writes or storage genuinely exceed one machine, shard.
Interview: In a system design interview, sharding is the answer for scaling writes or storage beyond one machine - but the strong signal is when and how you propose it. Reaching for sharding immediately, before simpler scaling, is a flag; proposing it as the answer to a genuine single-machine limit, with a well-reasoned shard key that distributes load and matches the query pattern, is a strong signal. Mentioning the hard parts - cross-shard queries, hot spots, resharding pain - shows you understand sharding as a serious trade-off, not a free scaling button.
The bottom line
Sharding splits a database horizontally across multiple machines, each holding a subset of the rows, to scale writes and storage beyond what one machine can handle. It hinges on the shard key, which determines how data and load distribute - chosen well (even distribution, aligned with queries) it works beautifully; chosen poorly it creates hot spots and cross-shard queries that hit every machine. Strategies (range, hash, directory, geographic) map rows to shards differently, and the hard parts - cross-shard queries and joins, hot spots, resharding, operational complexity - make sharding a serious decision.
Shard when you genuinely must, choose the shard key with care, and reach for the simpler scaling options first. For how sharding relates to the broader term partitioning, see the companion piece. We cover scaling the data layer across System Design Fundamentals.
Go deeper
- Shard (database architecture) (Wikipedia) - sharding concepts and strategies.
- The System Design Primer - sharding alongside the other scaling building blocks.
- Designing Data-Intensive Applications - the definitive treatment of partitioning and sharding.
- Amazon Builders' Library - how data is partitioned and scaled in production.