Nail Sharding in System Design Interviews: Range-Based, Hash-Based, Directory-Based

A Practical guide about Database Sharding and Partioning in System Design Interviews

Appreciate you reading this newsletter, I hope the content and resources are helping you level up as an engineer. If you want me to write about something specific, just message me or comment on the post.

Note: This article on was originally published on my blog - https://pradyumnachippigiri.dev/blogs/sharding

Previously, we talked about how we deal with a situation when an application grows overnight more users, more traffic, more queries.
Our first reaction was straightforward:

• scale vertically (bigger machine),

• scale horizontally (more servers for the app),

• add read replicas to offload read traffic.

And that worked… for reads.
You can read about it here - https://pradyumnachippigiri.substack.com/i/181087908/horizontal-scaling-sharding

Read replicas were amazing because they helped us spread the read load across multiple machines.
But there was one thing we still couldn’t scale: Writes.

No matter how many replicas we added, every single write still went to one place the primary database.

• All inserts? → Primary

• All updates? → Primary

• All deletes? → Primary

And sure, we can increase CPU, add RAM, use faster disks…
But hardware scaling hits a limit.
Eventually, this one machine becomes the bottleneck for write heavy systems.

And that’s exactly where sharding comes in.

---

Sharding

Split the data horizontally across multiple databases (shards), so writes are spread out instead of hitting a single node. So each shard now has its own CPU, its own memory, its own storage, its own connection pool.

A shard is just a subset of your data stored in a separate database.

Example:

• Shard 1 → Users with IDs 1–1,000,000

• Shard 2 → Users with IDs 1,000,001–2,000,000

  As we grow we can increase the number of shards.

You can (and usually should) have replication inside each shard:

• Shard 1 primary + read replicas

• Shard 2 primary + read replicas
  So you end up with: sharding for scaling writes, replication for High Availability + read scaling.

So Sharding solves the problem of scalability in writes, but you may ask questions like:

• How does my request even know which shard to go to ?

• What if one shard goes down

• What if one shard receives most of the traffic?

  ---

How to shard your data?

When you decide to shard, you’re really making two decisions that work together:

1. What to shard by (the shard key): This is the field/column you use to split the data – for example, user_id, tenant_id, or country. It defines how the data is logically grouped.

2. How to distribute it (the distribution strategy): This is the rule that assigns those groups to shards. It defines how those groups are spread across machines.

In practice, this logic usually lives in your API/backend code as a routing strategy.
Every read or write request goes through this routing logic before it touches the database.

// Pseudo-code in the API layer

function getShardId(userId: number) {
  // Simple hash-based strategy:
  // use userId % 4 to pick one of 4 shards
  return userId % 4;          // 0,1,2,3 → 4 shards
}

function getShardConnection(userId: number) {
  const shardId = getShardId(userId);
  return shardConnections[shardId];  // pick the right DB client
}

Here:

• Shard key = userId

• Distribution strategy = userId % 4 (a simple hash-based rule)

• Router = this bit of code that picks the correct DB connection

Choosing the Right Shard Key ?

When you shard a database, the shard key decides how your data and traffic are split across machines. When you shard/partition data, you expect traffic to be spread out evenly.

A shard that receives disproportionately high traffic (reads or writes) compared to others, then it becomes a bottleneck while other shards sit mostly idle. This is called as hot partiotion.
Example:

• You shard by country

• 60% of users are from US

• The US shard is overloaded = hot

• Other shards (for smaller countries) are mostly idle = cold

In that case, even if you have 10 shards, your system behaves like one overloaded shard.

So a good shard key should have:

• High Cardinality

  High cardinality makes it easier to avoid hot partitions, because the key space is nicely spread out.

  • Good: user_id, order_id, tenant_id

  • Bad: country, plan_type, created_at (only a few values → some shards get all traffic)

• Align with Query Patterns

  Your shard key should match how your application queries data.

  • If most queries are WHERE user_id = ? → shard by user_id

  • If you usually load “all data for a user” → that user’s data should live on one shard

  If your shard key has nothing to do with your query filters, you’ll end up with:

  • Cross-shard queries

  • Distributed joins

  • Higher latency and complexity

• Immutable and Stable

  The shard key should never change after it’s chosen.

  • Good: internal IDs (user_id, tenant_id)

  • Bad: email, phone, username, status

  If the shard key changes, you’d have to move rows across shards, which is slow, error-prone, and painful in production

  ---

Different Sharding Strategies

1. Range Based Sharding

Range-based sharding divides your data into contiguous ranges:

Shard 1 → User IDs 1–1M
Shard 2 → User IDs 1M–2M
Shard 3 → User IDs 2M–3M

range based sharding

The main advantage of range-based sharding is simplicity and support for efficient range scans. If you need all orders between user IDs 500K and 600K, you only hit one shard.

Range-based sharding is simple to implement and understand but it can lead to “hot spots,” where one shard is handling much more data than others. Early on, all traffic goes to the first range (because only low IDs exist), so one shard does all the work while the others are empty; later, as the system grows, all new writes and “recent activity” queries pile onto the latest range, so the last shard becomes the new hotspot.

2. Hash Based Sharding

Hash sharding uses a hash function to evenly distribute records across shards. Instead of assigning ranges, you take a shard key like user_id, hash it, and use the result to pick a shard. This is the default and most common sharding strategy.

hash based sharding

Hashing evenly distributes data across shards, minimizing hot spots.

Hash-based sharding using hash(key) % N spreads data evenly at first, but the moment you add or remove a shard, the value of N changes and almost every key now maps to a different shard, causing a massive reshuffle and painful rebalancing. Consistent hashing fixes this by placing shards and keys on a hash ring so that when the number of shards changes, only a small fraction of keys move to new shards, keeping the system much more stable and easier to scale.

We will talk about Consistent hashing in a different article.

3. Directory Based Sharding

Directory sharding uses a lookup table to decide where each record lives. Instead of using a formula, you store shard assignments in a mapping table or service.

directory based sharding

Directory-based sharding gives you maximum flexibility: you keep a mapping of userId → shard, so you can move heavy users to dedicated shards or rebalance load just by updating the directory instead of reshuffling all data. The tradeoff is that every request needs a directory lookup, which adds latency and turns the directory into a critical dependency if it goes down, your whole system is effectively down.

---

Partitioning

Partitioning means splitting a large table into smaller pieces inside a single database instance.

Unlike sharding, partitioning doesn’t spread data across multiple machines. Instead, it helps organize data internally to speed up queries. Partitioning directly impacts performance and data manageability. Here are some of its key benefits:

• Query optimization: Speeds up queries by limiting the search scope to a specific partition.

• Efficient data management: Simplifies data lifecycle management by segregating data for archiving or deletion.

• Better indexing and maintenance: Indexes can be applied at the partition level, reducing their size and making them easier to maintain. This keeps your database lean and responsive.

There are 2 types of partitions :

1. Horizontal Partitioning

Split rows across partitions. For example split by row range.

2. Vertical Partitioning

Split columns across partitions. For example, keep frequently accessed columns in one partition and large or rarely used columns in another. Same rows, fewer columns per partition.

We’ve reached the end of this super informative blog.

Last but not the least to say that :

A scalable architecture almost always combines:

• Sharding for write scalability

• Replication for HA + read scaling

• Partitioning for internal DB performance

Hope you liked this article, if you liked it then please do like and subscribe.It really motivates me to do better and make such amazing content.

Take Care and See you in the next one.