Part 4 · Databases & Data at Scale

Sharding: Splitting Data Across Machines

Indexes tuned, biggest server bought, replicas added — and the primary still drowns in writes. How sharding splits one dataset across machines, the four strategies, how to pick a shard key, and the hard problems it creates.

Lesson 2512 min readintermediate#database#sharding

The Problem

You have done everything right:

indexes are tuned,
you upgraded to the biggest database server you can buy,
five read replicas handle the reads,
Redis caches the hot data.

But the primary (the one main database server that takes all writes) is still at 90% CPU. The reason is that every write (new orders, messages, likes) goes to that one machine. The data is also 8 TB and grows by 1 TB a month. Backups take all night and schema changes take days.

Replication copies the same data to every machine. It helps reads, but it does not help writes or data size. To go further, you must split the data itself across machines. This is sharding, also called horizontal partitioning. Each part is called a shard. Sharding is powerful, and it is one of the hardest changes to undo.

The Core Idea

Think of a national exam board with results for 50 million students.

One office cannot store and answer queries for all of them.
So they split the work. Students with roll numbers 1–10 million go to Office A. Students with 10–20 million go to Office B. And so on.
Each office holds only its part and handles only its students' queries.
A directory at the front tells you which office has which roll numbers.

Each office is a shard. The roll number is the shard key. A shard key is the field that decides which shard holds a row. Now look at these cases:

"Get result for roll number 12,345,678" → go straight to Office B. ✅ Fast.
"Top 100 students in the country" → you must ask every office and combine the answers. ❌ Slow.
If one office gets far more queries than the others (for example, all the toppers' families call it), it is a hot spot.

How It Works

First, make sure you actually need sharding

Sharding adds a lot of complexity. Try these steps first, roughly in this order:

Fix queries and indexes (Lessons 5 and 22).
Cache the most-read data (Lessons 15–16).
Scale up the database machine: give it more CPU, memory and disk (Lesson 7).
Add read replicas, which are read-only copies (Lesson 24).
Archive old data: move it to cheaper storage.
Split by function (vertical partitioning / federation): move separate features into separate databases. For example, users in one database, orders in another, and analytics in another. This is a simpler first step.

Text

Before:  [ One DB: users + orders + products + messages ]Federation:         [ Users DB ]  [ Orders DB ]  [ Products DB ]  [ Messages DB ]

Sometimes a single table (like orders or messages) is still too big, or gets too many writes, for one machine. Then it is time to shard that table.

1
Fix queries and indexes
lessons 5 and 22
2
Cache hot reads
lessons 15–16
3
Scale up the database machine
lesson 7
4
Add read replicas
lesson 24
5
Archive old data
into cheaper storage
6
Split by function (federation)
users DB, orders DB, messages DB
7
Shard the one table that's still too big
the last resort; hard to undo

The steps to try before sharding. Most teams never need the last step. · flow

Sharding strategies

1. Range-based sharding

Split the data by ranges of the shard key.

Text

Shard 1: user_id 1 – 1,000,000Shard 2: user_id 1,000,001 – 2,000,000Shard 3: user_id 2,000,001 – 3,000,000

✅ Simple. Range queries (queries for all values between two limits) are efficient. For example, "orders from 1–7 December" if you shard by date.
❌ Hot spots. If you shard by time, all new writes go to the latest shard, while the old shards sit idle. If one range is more popular than the others, that shard is overloaded.

2. Hash-based sharding

Apply a hash function to the key. A hash function turns any value into a number that looks random. Then use that number to choose a shard. The % sign means "remainder after division".

Text

shard = hash(user_id) % number_of_shardshash(42)   % 4 = 2 → Shard 2hash(43)   % 4 = 0 → Shard 0hash(9001) % 4 = 1 → Shard 1

✅ Even distribution: similar keys land on different shards, so the load spreads well.
❌ Range queries are scattered: the rows you need sit on all shards.
❌ With plain % N, changing the number of shards moves almost all the data (Lesson 26 fixes this with consistent hashing).

3. Directory-based (lookup) sharding

Keep a lookup table (a directory) that says which shard holds each key or group of keys. In the example, a tenant is one customer company.

Text

tenant_id → shardacme      → shard 3globex    → shard 1initech   → shard 3bigcorp   → shard 7   (a huge customer, alone on its own shard)

✅ Very flexible: you can move one tenant to another shard by updating the directory. You can also give huge customers their own shards.
❌ The directory is an extra lookup and a critical dependency (if it fails, everything fails). So it must be fast (cached) and highly available.

4. Geo-based sharding

Split by region. Indian users' data goes to the Mumbai shard. EU users' data goes to the Frankfurt shard.

✅ Low latency for local users. It also helps with data residency laws (laws that say data about citizens must stay in their country or region).
❌ Shards have uneven sizes. Users who travel or move need special handling.

Choosing a shard key: the most important decision

A good shard key:

Spreads data and traffic evenly. It needs many different values (this is called high cardinality). It should not have a few values that are extremely popular.
Matches your most common queries, so most requests go to one shard.
Keeps related data together, so data that you read or change together lives on the same shard.
Rarely or never changes. If a row's shard key changes, the row must move to another shard.

Examples:

App	Good shard key	Why
Multi-tenant SaaS (like Slack, Notion, Shopify)	`tenant_id` / `workspace_id`	Almost every query stays inside one customer. All of that customer's data is kept together
Social app	`user_id`	"My profile, my posts, my settings" are all on one shard
Chat app	`channel_id` / `conversation_id`	All messages in a conversation are together and ordered
E-commerce orders	`customer_id`	"My orders" is on one shard. But "all orders for seller X" becomes a cross-shard query (a query that needs several shards)

Bad shard keys:

country (only a few values, and some are huge),
created_at alone (all new writes hit the newest shard),
status (only a few possible values).

The hard problems sharding creates

Range

+Simple; range queries stay on one shard
−Time-based keys send every new write to the newest shard

Use whenNaturally bounded ranges, archives

Hash

+Even spread of data and load
−Range queries scatter to every shard
−hash % N moves most data when N changes

Use whenMost general-purpose sharding

How requests find the right shard

Diagram

Option A: App-level routing
   App code:  shard = lookup(user_id) → connect to that shard

Option B: A routing proxy
   App ──► [ Proxy / router ] ──► Shard 1, 2, 3...   (app thinks it's one database)

Option C: Built into the database
   Distributed databases route automatically

Tools:

Vitess (sharding for MySQL, originally built at YouTube),
Citus (for PostgreSQL),
ProxySQL (a proxy for MySQL that can route queries to different servers),
MongoDB's built-in sharding,
and fully distributed databases like DynamoDB, Cassandra, CockroachDB, YugabyteDB, TiDB and Spanner.

480

logical shards

fixed forever, keyed by workspace ID (Notion)

physical databases

15 logical shards each

rows re-split

to add capacity, just move whole logical shards

The “many logical shards on few machines” trick, as used by Instagram, Pinterest and Notion. · numbers

Trade-offs

✅ Scales writes and storage almost without limit. Each database is smaller and faster. It also limits the blast radius (how much breaks when something fails): one failed shard affects only part of your users.
❌ Complexity everywhere: routing, cross-shard queries, transactions, IDs, rebalancing and operations.
❌ The shard key is very hard to change later. A wrong choice can cause trouble for years.
❌ Some queries become expensive or need separate systems (search, analytics).

When NOT to shard: when caching, replicas, better indexes, archiving or a bigger machine would solve the problem. Many companies run for years on a single, well-tuned primary database. Shard when you have clear evidence that you have outgrown it. Then plan it with care.

In the Real World

Instagram's sharded PostgreSQL and IDs. Instagram split its data into thousands of logical shards. These were placed on fewer physical PostgreSQL servers. Its IDs contain a timestamp, the logical shard ID and a sequence number. So the IDs are unique, they sort by time, and they tell you which shard holds the data.

Pinterest's MySQL sharding. Pinterest sharded MySQL. Its IDs include the shard ID, so you can find any object by looking at its ID. It also placed many logical shards on each physical server. So it could move shards between machines as it grew.

Notion's sharding (2021). Notion's single PostgreSQL database was struggling as usage grew very fast. The team sharded it into 480 logical shards spread across 32 physical databases. The shard key is the workspace ID, so everything in one workspace stays together. They migrated while the app was live. They used double-writes (writing to old and new databases), backfilling (copying old data) and careful checks.

Figma's database scaling. Figma first split tables into separate databases by function (vertical partitioning). Later it built horizontal sharding for its largest tables, including its own routing layer, and it kept PostgreSQL. Their write-ups are a good real example of the "try simpler steps first" order above.

Vitess at YouTube and Slack. Vitess was created at YouTube to scale MySQL. Later, other companies adopted it. One is Slack, which moved its main databases to Vitess to handle growth. Slack shards largely by workspace.

Interview Questions

Q1When should you shard, and what would you try first?

Only when a single primary can no longer handle the write volume or the data size. First, fix queries and indexes. Then cache, scale up, add read replicas for reads, archive cold data (data that is rarely used), and split unrelated features into separate databases. Shard only the specific table that is still too big.

Q2How do you choose a shard key?

It should have high cardinality (many different values) and spread the load evenly. It should match the most common access pattern, so most requests hit one shard. It should keep data that is read or written together on the same shard. And it should almost never change. For SaaS this is usually tenant_id. For chat it is conversation_id.

Q3How do you handle queries that don't include the shard key?

You can use scatter-gather to every shard, but only when such queries are rare. You can keep a second lookup table with the other key (email → user_id). Or you can send the data to a search engine or a warehouse that is built for queries across all data.

Q4How do you generate unique IDs across shards?

Not with a separate auto-increment counter on each shard. Use UUIDs (UUIDv7 if you want time order). Or use Snowflake-style 64-bit IDs that combine a timestamp, a shard or machine ID and a sequence number. These IDs also let you find the right shard by reading the ID.

Q5How do you add capacity to a sharded database without downtime?

Start with many logical shards on a few machines. Later, move whole logical shards to new servers. If you must really re-split the data, copy it, keep it in sync with CDC or dual writes, switch reads and then writes, check the result, and clean up.

Q6How do you deal with a celebrity or whale-customer hot key?

Put it alone on its own shard (directory-based). Split it into smaller keys with a bucket suffix. Put a cache or read replicas in front of it. Consistent hashing alone does not help, because one key always maps to one place.

Key Takeaways

Sharding splits one dataset across machines, to scale writes and storage. Replication alone only scales reads.
Try indexes, caching, bigger machines, replicas, archiving and functional splits first.
The strategies are range, hash, directory and geo. Each trades even distribution, range queries and flexibility differently.
The shard key is the key decision: it should spread load evenly, match common queries, keep related data together, and rarely change.
Plan for hot keys, cross-shard queries, global IDs and rebalancing. Use many logical shards so machines can be added later.

Keep learning

Part of the study guide

System Design Interview Guide: Basics to Case Studies

Related series

Sharding: Splitting Data Across Machines

On this page

The Problem

The Core Idea

How It Works

First, make sure you actually need sharding

Sharding strategies

1. Range-based sharding

2. Hash-based sharding

3. Directory-based (lookup) sharding

4. Geo-based sharding

Choosing a shard key: the most important decision

The hard problems sharding creates

How requests find the right shard

Trade-offs

In the Real World

Interview Questions

Key Takeaways

Further Reading

Keep learning