Database Sharding for System Design Interviews: The Complete Guide

You're designing a ride-sharing app. The interviewer nods as you walk through the tables. Then you casually drop "we'll shard the database" like it's seasoning. A pinch of architecture. A sprinkle of scale. The interviewer writes something down.

That's the mistake. Not that you mentioned sharding. That you said it without being able to explain the shard key, the tradeoffs, or why you'd reach for it over five cheaper options. Interviewers who understand distributed systems will ask exactly those questions. If you can't answer, you've demonstrated the most common system design failure mode: naming a solution without understanding it.

Partitioning Stays in One Box. Sharding Leaves It.

These two terms get used interchangeably, which is partly correct and mostly confusing. Sharding is partitioning. Specifically, it's horizontal partitioning distributed across multiple machines.

Partitioning is an organizational strategy within a single database. You split a large table into smaller segments based on some rule. Those pieces live on one server.

Sharding is a scaling strategy. You distribute those pieces across separate database instances, each running on its own hardware. Every piece is called a shard, and each shard owns a disjoint slice of your data. Getting data back out requires knowing which shard to ask. That's the part that gets you.

Vertical partitioning splits a table by column rather than by row, separating hot columns from cold ones. Useful to know, but rarely the interview focus.

Single Server
┌──────────────────────────────────────────┐
│  users table (100 million rows)          │
│  rows 1 - 100,000,000                    │
└──────────────────────────────────────────┘

Sharded (3 shards, horizontal split)
┌──────────────────┐  ┌──────────────────┐  ┌──────────────────┐
│  Shard 1         │  │  Shard 2         │  │  Shard 3         │
│  rows 1 - 33M    │  │  rows 33M - 66M  │  │  rows 66M - 100M │
│  (own server)    │  │  (own server)    │  │  (own server)    │
└──────────────────┘  └──────────────────┘  └──────────────────┘

Try Everything Cheaper First

Sharding is operationally expensive. Cross-shard joins don't work. Transactions span multiple machines. Rebalancing can mean moving terabytes of data. Interviewers expect you to know sharding is a last resort, not a first move.

The scale ladder, in order:

Vertical scaling (bigger machine, more CPU and RAM) is cheap and fast. Works until you hit the hardware ceiling, typically around a few terabytes or 50,000 write QPS on a single node.
Read replicas absorb read load. 80-90% of traffic in most systems is reads. Add replicas before you touch the write path.
Caching (Redis, Memcached) eliminates database reads for hot data entirely. A well-designed cache layer can absorb 95% of reads. It's the closest thing to a free lunch in distributed systems.
Indexing and query optimization costs nothing but planning. A missing index can make a query 100x slower.
Vertical partitioning separates hot columns from cold ones on the same server.
Sharding when all of the above are in place and writes still can't keep up.

If you jump straight to sharding without this progression, you've told the interviewer you reach for a sledgehammer when a screwdriver is right there.

No Vibes. Just Math.

You can't justify sharding without numbers. A rough rule: consider it above 1-5 TB of data on a single node, or above 10,000 write QPS when caching and replicas can't absorb more. At 10+ TB or 100K+ write QPS, you're almost certainly there.

Do the math out loud. If your system has 500 million users generating 5 KB of profile data each, that's 2.5 TB. A single server handles that with some configuration. If each user generates 50 events per day and you need 90 days of history, that's 2.25 trillion rows. Different conversation entirely.

State the numbers, then state the conclusion. "We have X TB of writes per year and our single node writes cap out at Y TB, so we need to distribute the write load" is what the interviewer wants to hear. "We have a lot of data" is not.

Your Shard Key Is the Whole Game

The most important decision in sharding is the shard key, and the wrong choice defeats the entire architecture. Pick wrong and you've built a very expensive single-threaded database.

A shard key is the field (or composite of fields) you use to route a write or read to a specific shard. Bad shard keys create hotspots: one shard gets 90% of the traffic while the others sit idle.

Common bad choices:

Timestamps or created_at: All new writes land on the shard holding the current time range. That shard overloads while older shards go cold. Congratulations, you have a hotspot.
Status fields: A field like order_status with three values can only create three logical buckets. Not enough cardinality to spread across 20 shards.
Sequential IDs without hashing: Same problem as timestamps. The hot shard is always the one with the biggest numbers.

Good shard key properties:

High cardinality: Enough distinct values to distribute across all shards
Even distribution: No single value or range concentrating all writes
Query alignment: The key should match your most frequent access pattern

Bar chart comparing a bad timestamp shard key creating a hotspot against an even user_id hash distribution A timestamp shard key funnels all current writes to one shard (left). A hashed user_id spreads the load evenly (right).

For a social app, user_id is usually a strong shard key. Most queries are "get data for this user." That data lives on one shard, and you rarely need to join across users. For an analytics system querying time ranges across all users, you may need a hybrid: hash by entity first, then range partition within each shard.

Range or Hash? One Question Decides

Two main strategies, optimizing for opposite things.

Range sharding maps key ranges to specific shards:

Shard 1: user_id 1 - 10,000,000
Shard 2: user_id 10,000,001 - 20,000,000
Shard 3: user_id 20,000,001 - 30,000,000

Pro: range queries ("get all orders between Jan 1 and Jan 31") stay on one shard. Con: writes concentrate on the shard holding the current range. New data always lands in the same place.

Hash sharding applies a hash function to the key and uses the result to assign a shard:

shard_id = hash(user_id) % number_of_shards

user_id 1001  → hash → 0x3F4A → mod 3 → shard 1
user_id 1002  → hash → 0x8B2C → mod 3 → shard 2
user_id 10003 → hash → 0x1D90 → mod 3 → shard 0

Pro: even distribution, no hotspots from sequential keys. Con: range queries now require hitting all shards and merging results in the application layer.

Connect the choice to your workload. A system dominated by point lookups benefits from hash sharding. A system that frequently queries date ranges probably wants range sharding. Saying "we'll use hash sharding" without explaining why tells the interviewer you read the bullet point but skipped the reasoning. For similar routing tradeoffs, key-value store system design goes deeper.

Adding a Node Shouldn't Require Moving Day

With naive hash sharding, adding a fourth shard turns hash(key) % 3 into hash(key) % 4. Almost every key maps to a different shard. You're moving most of your data during the transition. In production. Without taking anything down. Enjoy.

Consistent hashing solves this. Instead of modulo N, you map both shards and keys onto a ring: a continuous hash space from 0 to 2^32. A key is assigned to the first shard you encounter moving clockwise on the ring.

Consistent hashing ring showing three existing shards and a new fourth shard being inserted, with only the affected arc needing data migration Adding s4 only moves keys in the arc between s1 and s4. Everything outside that arc stays where it is.

When you add shard s4 between s1 and s2 on the ring, only the keys that were previously assigned to s2 in that arc need to move to s4. Everything else stays where it is. On average, you move 1/N of the data when adding one new shard to an N-shard cluster.

DynamoDB and Cassandra both use variants of this internally. You don't need to implement it in an interview. Just explain why it beats modulo-N when you start growing.

Name These Three Costs or Get Found Out

Sharding comes with three tradeoffs that interviewers specifically listen for. Not because they're trying to trip you up. Because anyone who's actually run a sharded database has scars from exactly these three things.

Cross-shard queries. Any query that doesn't include the shard key in its WHERE clause must broadcast to all shards (scatter-gather), and results need merging at the application layer. Every shard. An Instagram-style query like "find all photos tagged #sunset" hits every shard if photos are sharded by user_id. Teams typically maintain a secondary index in a separate store (Elasticsearch, or a lookup table in another database) for these patterns.

Distributed transactions. A transaction writing to two different shards needs two-phase commit or equivalent coordination. This adds latency and introduces new failure modes. Design your schema so the vast majority of writes hit a single shard. If you can't, that's often a sign the shard key was wrong from the start. Discuss whether the business can tolerate eventual consistency instead.

Operational complexity. Each shard needs its own replicas, backups, and failover logic. Schema migrations require coordinated deployment across every shard. Monitoring means watching N clusters, not one. This is why teams delay sharding as long as possible, and why the engineers who maintain sharded systems look perpetually tired.

How to Say "We'll Shard It" Without Getting Destroyed

You don't announce it. You build a case.

Start by estimating your data volume and write QPS. Show that a single database can't handle the load. If the numbers don't get you there, don't propose sharding.

When you do propose it, answer three questions before the interviewer has to ask:

What's the shard key? Name it and explain why it distributes evenly for your specific access patterns.
What breaks? Identify the cross-shard query patterns in your design, if any, and explain how you handle them.
How do you grow? Briefly mention consistent hashing or directory-based routing to show you've thought about adding shards as the system scales.

One thing most candidates forget: every shard needs replicas. A shard going down means that portion of your data is unavailable. If you've proposed three shards and haven't mentioned replication, you've designed a system where "shard 2 is down" takes out 33% of your users. Say it explicitly.

Practicing this out loud matters more than reading about it. The hard part isn't memorizing the concepts. It's constructing a sharding argument in real time, under pressure, while someone asks follow-up questions. SpaceComplexity lets you run through system design scenarios with voice-based mock interviews and get rubric-based feedback on whether your sharding argument actually holds up.

For a broader framework on how to structure system design answers, system design interview tips covers the full process.

Key Takeaways

Sharding is distributed horizontal partitioning. Walk the scale ladder first: vertical scaling, read replicas, caching, indexing, then sharding.
The shard key is the whole decision. High cardinality, even distribution, alignment with your query patterns. Bad keys create hotspots.
Hash sharding distributes evenly. Range sharding enables efficient range queries. Pick based on your workload, and explain why.
Name the three costs every time: cross-shard queries, distributed transactions, operational complexity. And add replicas per shard.