What you'll learn

  1. Quick answer
  2. The steps before sharding
  3. What sharding does
  4. Choosing the shard key
  5. What becomes hard
  6. In interviews
  7. FAQ

Quick Answer

Sharding partitions rows across multiple databases by a shard key. It is the last scaling step, because it breaks cross-shard joins, transactions and global uniqueness. Exhaust indexing, caching and read replicas first.

The steps before sharding

Sharding is genuinely difficult, so it belongs at the end of a ladder. Work through these first:

  1. Fix the queries. Missing indexes and N+1 queries account for a large share of "the database cannot cope". Run EXPLAIN before concluding anything.
  2. Cache. If reads dominate and tolerate slight staleness, caching removes most of the load — see caching strategies.
  3. Scale up. A larger machine is dramatically simpler than sharding. Modern hardware handles far more than people assume.
  4. Read replicas. Send reads to copies and writes to the primary. Handles read-heavy load with modest complexity, at the cost of slight replication lag.
  5. Vertical partitioning. Move a few enormous tables to their own database.

Shard only when writes exceed what one machine can take, or the dataset genuinely will not fit. Both are real problems and both arrive far later than people expect.

What sharding does

Rows are split across several databases by a shard key. Each shard holds a subset, and no shard holds everything.

shard = hash(user_id) % 4

user 1001 -> shard 2
user 1002 -> shard 3
user 1003 -> shard 0

The application — or a routing layer — computes which shard to query. Requests for one user go to one shard, so each machine handles roughly a quarter of the load and a quarter of the data.

Distinguish this from replication. Replicas hold the same data; shards hold different data. Replication helps read capacity and redundancy; sharding helps write capacity and total size. Most large systems use both — each shard is itself replicated.

Choosing the shard key

The most consequential decision, and the hardest to change afterwards.

A good key gives even distribution and keeps related data together. Those pull in opposite directions.

Common choices and their failure modes:

  • User ID — usually good. Even distribution, and one user's data lives on one shard, so their queries hit one machine.
  • Tenant or organisation ID — natural for business software, and creates hotspots when one customer is far larger than the rest. A shard holding your biggest client can become the bottleneck alone.
  • Date — appealing and often bad, because all current writes land on one shard while older ones sit idle.
  • Sequential ID — worst case, since consecutive inserts concentrate on the same shard.

Hashing the key spreads data evenly and destroys range queries — you can no longer scan a contiguous range without touching every shard. That is the fundamental trade.

What becomes hard

These are the reasons to delay sharding, and they are not incidental.

Cross-shard joins. Joining users on shard 1 to orders on shard 3 is not something the database can do. You fetch from both and join in application code, losing the optimiser entirely.

Transactions across shards. ACID guarantees hold within one database. Spanning shards requires distributed transaction protocols that are slow and complex, or accepting eventual consistency with compensating actions — see ACID properties.

Global uniqueness. A unique constraint on email works within a shard. Enforcing it globally needs a separate lookup table or coordination. Auto-increment IDs also collide, so you need UUIDs or a distributed ID generator.

Rebalancing. Going from 4 shards to 8 with modulo hashing moves most rows, because the mapping changes for nearly everything. Consistent hashing exists specifically to limit this, moving only the affected fraction.

Queries without the shard key must fan out to every shard and merge results — expensive, and it gets worse as you add shards.

In interviews

Sharding appears constantly in system design questions, and the weak answer is proposing it immediately.

A strong answer works up the ladder: "At this scale a single database with proper indexing handles it. If reads grow, I would add replicas. I would shard only when write volume exceeds one primary — probably keyed on user ID, since most queries are per-user."

Then name the costs unprompted: cross-shard joins, global uniqueness, and rebalancing. Interviewers are specifically checking whether you know sharding is expensive rather than clever.

Two things worth adding: many managed databases now offer sharding transparently, which removes some operational burden but none of the query constraints. And it is worth knowing that partitioning — splitting a table within one database — solves some size problems without any of the distributed complexity, and is frequently the right answer instead.

Frequently Asked Questions

What is the difference between sharding and replication? Replicas hold identical copies, helping read capacity and redundancy. Shards hold different subsets, helping write capacity and total size. Large systems usually use both together.
How do I choose a shard key? Aim for even distribution while keeping related data together. User ID is often a good default. Avoid sequential IDs and dates, which concentrate current writes on one shard.
Why are cross-shard joins a problem? Data lives in separate databases, so the database engine cannot join them. You fetch from each and combine in application code, losing the query optimiser.
When should I shard? Only after indexing, caching, scaling up and read replicas are exhausted, and write volume or data size genuinely exceeds one machine. It is the last step, not an early one.
What is consistent hashing? A mapping scheme that limits how much data moves when shards are added or removed. Plain modulo hashing relocates nearly everything when the shard count changes.