Sharding

Replication helps us scale reads. But what about writes? What if our single primary server can’t handle the write load, or our data simply doesn’t fit on one machine anymore?

That’s where sharding comes in. In simple language, sharding means splitting our data across multiple database servers (called shards). Each shard holds a subset of the total data. Together, they hold everything.

Think of it like a library that’s gotten too big for one building. Instead of one massive library, we split the books across three buildings — A-H in Building 1, I-P in Building 2, Q-Z in Building 3. Each building is smaller and faster to search.

When Do We Actually Need Sharding?

Sharding adds significant complexity. We should only do it when simpler solutions have been exhausted:

1. Vertical scaling — get a bigger server (more RAM, faster CPU, better disks). This is often cheaper and simpler than sharding.
2. Read replicas — if the problem is too many reads, add replicas.
3. Caching — reduce database load with Redis or Memcached.
4. Query optimization — maybe the problem is bad queries, not insufficient hardware.

If none of these are enough, then yes, it’s time to shard.

Sharding Strategies

The critical question is: how do we decide which shard holds which data?

Range-Based Sharding

Split data based on ranges of a key value.

-- Shard 1: user IDs 1 to 1,000,000
-- Shard 2: user IDs 1,000,001 to 2,000,000
-- Shard 3: user IDs 2,000,001 to 3,000,000

-- To find user 1,500,000:
-- 1,500,000 falls in range 1M-2M → go to Shard 2
SELECT * FROM users WHERE id = 1500000;  -- routes to Shard 2

Pros: Simple to understand and implement. Range queries are efficient (all data in a range lives on one shard).

Cons: Hot spots. If most new users have high IDs, Shard 3 gets all the writes while Shard 1 sits idle. The distribution is often uneven in practice.

Hash-Based Sharding

Apply a hash function to the shard key, then use modulo to pick a shard.

-- shard_number = hash(user_id) % num_shards

-- With 3 shards:
-- hash(42) % 3 = 1   → Shard 1
-- hash(43) % 3 = 0   → Shard 0
-- hash(44) % 3 = 2   → Shard 2

Pros: Even distribution. No hot spots (assuming a good hash function).

Cons: Range queries are impossible — data that was “next to each other” gets scattered across shards. And resharding is painful — if we go from 3 shards to 4, almost every key maps to a different shard. We’d have to move most of the data.

Directory-Based Sharding

Maintain a lookup table that maps each key (or key range) to its shard.

-- Lookup table (stored in a separate database or service)
-- key_range    | shard
-- user:1-500K  | shard_1
-- user:500K-1M | shard_2
-- user:1M-2M   | shard_3

-- To find a user: look up the mapping first, then query the right shard

Pros: Maximum flexibility. We can move data between shards without changing the sharding logic — just update the lookup table.

Cons: The lookup service is a single point of failure. Every query has an extra hop. The directory itself needs to be fast and highly available.

Sharding Strategy Comparison

Consistent Hashing

Consistent hashing solves the biggest problem with regular hash-based sharding: adding or removing a shard doesn’t require moving all the data.

The Problem with Simple Hashing

With hash(key) % N, changing N (the number of shards) changes where almost every key maps. Going from 3 shards to 4 means ~75% of keys need to move. That’s a nightmare.

How Consistent Hashing Works

Imagine the hash space as a ring (circle from 0 to 2^32). Both shards and keys are placed on this ring using a hash function.

To find which shard owns a key: start at the key’s position on the ring and walk clockwise until we hit a shard. That shard owns the key.

When we add a shard: the new shard takes over some keys from its clockwise neighbor. Only those keys move — everything else stays put.

When we remove a shard: its keys move to the next shard clockwise. Again, minimal disruption.

In practice, each physical shard gets multiple virtual nodes on the ring for better distribution. Instead of Shard A at one point, we put Shard A at 100 points around the ring. This smooths out imbalances.

The Headaches of Sharding

Cross-Shard Queries

This is the biggest pain point. If we shard users by user_id, and we need to find “all orders in the last 24 hours” — that data is spread across all shards. We have to query every shard and merge the results. That’s slow and complex.

-- This query needs to hit ALL shards and merge results
SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '24 hours'
ORDER BY created_at DESC LIMIT 20;
-- Each shard returns its top 20, then we merge and re-sort

Cross-Shard JOINs

Even worse than queries — JOINs across shards are extremely expensive. If users are on Shard 1 and their orders are on Shard 3, a JOIN users ON orders.user_id = users.id requires fetching data from both shards over the network.

The common solution: denormalize. Store enough data in each shard so we don’t need cross-shard JOINs.

Choosing the Shard Key

The shard key is the single most important decision in sharding. A bad choice can make our lives miserable:

• Too few unique values — uneven distribution (imagine sharding by country)
• Doesn’t match query patterns — forces cross-shard queries for common operations
• Can’t be changed later — resharding is extremely painful

Good shard keys have high cardinality, even distribution, and match our most common query patterns.

Resharding

When we need to add or remove shards, we have to move data. Even with consistent hashing, this means:

1. Setting up the new shard
2. Copying data from existing shards to the new one
3. Keeping both in sync during migration
4. Switching traffic to the new topology
5. Cleaning up old data

Some databases handle this automatically (like CockroachDB, Vitess for MySQL). With others, it’s a manual and risky operation.

Real-World Sharding

• Instagram — sharded PostgreSQL by user_id (each shard is a PostgreSQL schema)
• Discord — sharded by guild_id (server ID) using Cassandra
• Notion — sharded PostgreSQL, eventually moved to a custom solution
• Vitess — YouTube’s MySQL sharding middleware (now open source)

Interview Tip

The key thing interviewers want to hear: sharding is a last resort, not a first instinct. Always explain the simpler alternatives first (vertical scaling, caching, read replicas). Then if sharding is needed, explain the strategy, the shard key choice, and the trade-offs — especially around cross-shard queries and resharding.