How splitting a database across machines works, how to choose the key you cannot change later, and the four cheaper fixes almost everyone should try first.
Sharding splits one database into several. Each one holds a different slice of the rows. Users A to M live on one machine, N to Z on another.
Every shard is a complete database. It has the same schema as the others and accepts its own reads and writes. Together they handle more traffic and more data than one machine ever could.
It is how Instagram, Slack, Notion and YouTube store their data. It is also the most expensive decision on this list, and the split you choose is close to permanent.
Most teams reach for it too early. Sharding fixes exactly two problems: too many writes for one machine, or too much data for one machine. If your reads are slow, an index or a cache will fix that in an afternoon.
You have one users table. It holds 500 million rows on one server. Every query
in the product hits that machine.
Sharding splits it on one column. That column is the shard key.
┌─────────────┐
│ shard 0 │ user_id % 4 == 0
├─────────────┤
app ──▶ router ──▶ │ shard 1 │ user_id % 4 == 1
├─────────────┤
│ shard 2 │ user_id % 4 == 2
├─────────────┤
│ shard 3 │ user_id % 4 == 3
└─────────────┘
The router is often just a function in your application:
SHARDS = ["db-0", "db-1", "db-2", "db-3"]
def shard_for(user_id: int) -> str:
return SHARDS[user_id % len(SHARDS)]
conn = connect(shard_for(300_000_042))
conn.execute("SELECT * FROM users WHERE id = %s", (300_000_042,))
Four machines, a quarter of the rows each, and a lookup that still touches one of them.
Here is the whole trade in two statements.
-- Has the shard key. One machine answers. Fast.
SELECT * FROM users WHERE id = 300000042;
-- No shard key. Nobody knows where this row lives.
SELECT * FROM users WHERE email = 'priya@example.com';
The second one used to be an indexed lookup. Now your application has to ask all four machines, wait for the slowest, and merge the results itself.
def find_by_email(email):
results = [q(s, "SELECT * FROM users WHERE email = %s", email) for s in SHARDS]
return next((r for r in results if r), None) # 4 queries for 1 row
That is a fan-out query. Four is survivable. Sixty-four is not.
The usual fix is a second table that maps email to shard, kept on one machine or in a cache. Now you have two things to write on signup and two things to keep in agreement. That is the shape of most sharding work. Not the split itself, but everything that used to be free.
These three get used interchangeably. They solve different problems.
| what it does | gives you | does not give you | |
|---|---|---|---|
| Replication | full copies of the same data | read capacity, failover | write capacity, more space |
| Partitioning | splits a table inside one database | faster scans, easy archiving | more machines |
| Sharding | splits data across separate databases | write capacity, storage | simplicity |
Partitioning is the quiet win most teams skip. Split an orders table by month inside the same Postgres instance, and queries for last week stop scanning four years of history. Dropping old data becomes one statement instead of a delete that runs all night.
CREATE TABLE orders (id bigserial, created_at date, ...)
PARTITION BY RANGE (created_at);
CREATE TABLE orders_2026_09 PARTITION OF orders
FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');
DROP TABLE orders_2022_01; -- instant. no distributed system involved.
If you have not tried partitioning, you are not ready to shard.
Each is reversible. Each takes days instead of months.
Read the query plan. A query scanning ten million rows is not a capacity problem. It is a missing index.
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;
-- Seq Scan on orders (cost=0.00..184301.00 rows=1 width=64)
-- ^^^^^^^^ this is your answer, not a bigger machine
Cache the reads that repeat. Most products read the same small set of rows constantly. Put those in memory and a large share of your load disappears.
Add read replicas. Copies serve reads while one primary takes writes. Most workloads are mostly reads, so this goes a long way. The cost is replication lag. A user who writes and immediately reads may not see their own change for a few milliseconds, which matters more than it sounds when the write was "update my profile picture".
Archive the cold rows. Tables are usually huge because they hold years nobody queries. This is unglamorous and often the biggest win available.
symptom do this
──────────────────────────────────────────────────────────
one query is slow index it
the same rows read constantly cache
reads outgrew one machine read replicas
table huge, most of it cold partition and archive
writes outgrew one machine shard
data will not fit on one machine shard
Only the last two rows are sharding problems.
This is the decision. Everything else in a sharded system is a consequence of it.
Pick the column that appears in almost every query. If your product is built around users, shard by user id and most queries stay on one machine. If it is built around teams or workspaces, shard by workspace id. Slack shards by workspace for exactly this reason: almost nothing in Slack crosses workspaces, so almost nothing crosses shards.
Notion made the same call with workspace id. When they outgrew their first layout they increased the shard count rather than changing the key, because the key was right and the count was not.
Two failures to check for before you commit.
Uneven traffic. Sharding by country when 80% of your users are in one country gives you one melting machine and four idle ones. A shard key has to spread traffic, not just rows.
Hot individual rows. Shard a social product by user id and it works until one account has forty million followers. That shard now carries a disproportionate share of everything. The biggest accounts are always the exception and usually get handled separately.
The test. Write down your ten most common queries. Count how many can be answered by a single shard. Nine or ten and the key is good. Five and you should choose again, because after the data has moved, choosing again is a migration.
Three strategies. Pick deliberately.
Range. Users 1 to 1,000,000 on shard 0, and so on. Simple, and range scans stay on one shard. The problem is sequential ids. Every new signup lands on the last shard, so your newest machine takes all the write traffic while the others idle.
Hash. Hash the key, take the remainder, get a shard. Traffic spreads evenly. The cost is that ranges scatter, so "everyone who joined last week" now touches every machine.
Directory. Keep an explicit map of key to shard.
{
"strategy": "directory",
"shard_key": "workspace_id",
"map": {
"ws_8812": "shard-0",
"ws_9134": "shard-0",
"ws_2277": "shard-3"
},
"default_strategy": "hash",
"shards": {
"shard-0": "db-0.internal:5432",
"shard-3": "db-3.internal:5432"
}
}
This is the flexible one. A customer who grows enormous can be moved to their own machine without touching anybody else. The cost is that the directory is now on the path of every query, so it has to be fast and it must not go down.
Most teams start with hash and add a directory later, once one customer is big enough to need its own machine.
There is a trick worth knowing. Instagram used it when they sharded Postgres, and plenty of teams have copied it since.
Instead of looking up which shard a row lives on, encode it in the id itself.
63 23 13 0
┌──────────────────────┬─────────┬─────────┐
│ timestamp (41 bits) │ shard │ sequence│
└──────────────────────┴─────────┴─────────┘
when where which one
def make_id(shard_id: int, seq: int, epoch_ms: int) -> int:
return (epoch_ms << 23) | (shard_id << 10) | (seq % 1024)
def shard_of(row_id: int) -> int:
return (row_id >> 10) & 0x1FFF # read it straight back out
Now any id tells you where its row lives. No lookup table, no extra round trip. The ids also sort by time, which is free and useful.
The catch is that the shard is baked into the id forever. Move a row and its id lies about where it is. Teams handle this by moving whole shards rather than individual rows, which is a good habit anyway.
Four shards were plenty. Now they are not, and you want eight.
With plain modulo hashing, almost everything moves:
user 42: 42 % 4 = 2 42 % 8 = 2 stays
user 43: 43 % 4 = 3 43 % 8 = 3 stays
user 44: 44 % 4 = 0 44 % 8 = 4 moves
user 45: 45 % 4 = 1 45 % 8 = 5 moves
Roughly half your rows change address. In a real system that is hours of copying while writes keep arriving.
Consistent hashing exists to fix this one problem. Shards are placed around a ring, keys land on the ring, and each key belongs to the next shard clockwise. Add a shard and only the keys between it and its neighbour move.
shard-0
●─────────● shard-1
╱ ╲
● ●
shard-3 ← new → shard-2
only keys in this arc move
The practical version most teams use is simpler. Create far more shards than you need, say 1,024 logical shards, and map many of them onto each physical machine. Growing means moving whole logical shards to new hardware. No rehashing, no recalculating, just moving files.
{ "logical_shards": 1024,
"placement": { "0-255": "db-0", "256-511": "db-1",
"512-767": "db-2", "768-1023": "db-3" } }
Decide this at the start. It is cheap on day one and expensive on the day you need it.
None of these is fatal. All of them are permanent.
Joins across shards stop working.
-- Fine on one database. Impossible if these live on different machines.
SELECT u.name, o.total
FROM users u JOIN orders o ON o.user_id = u.id
WHERE u.id = 42;
The fix is to shard related tables on the same key, so a user and their orders land together. That works when there is one obvious key. It falls apart when two things both want to be the key, which is why Discord partitions messages by channel and time rather than by user.
Transactions stop being free. Two rows on one machine update atomically. Two rows on different machines do not. You end up writing application code to handle a failure halfway through, or redesigning so the two rows are on the same shard.
Unique constraints get harder. AUTO_INCREMENT collides across shards, so
you need UUIDs or an id scheme like the one above. A unique email is no longer
something the database can promise, because no single machine sees them all.
Operations multiply. Backups, upgrades, monitoring and schema migrations now happen N times. Every schema change has to be safe to run while some shards have it and some do not.
Two signals. You need at least one.
Writes outgrew one machine. Not reads. Replicas fix reads. Each shard accepts its own writes, and that is the one thing a replica cannot do.
The data does not fit. Not "the table is big", but the working set genuinely exceeding what one machine can hold and index.
If neither is true, your answer is in the four cheaper fixes above.
The order that works for most products: index, cache, partition and archive, bigger machine, read replicas, then shard. Plenty of successful companies never reach the last step. A single modern Postgres instance handles far more than architecture blogs suggest.
Sharding vs partitioning? Partitioning splits a table inside one database. Sharding splits data across separate databases on separate machines. One is a schema decision, the other is an architecture decision.
Can I shard later? Yes, and it is a large project. What you can do cheaply today is avoid making it harder. Use ids that will not collide, and be careful about joins you would not be able to do later.
Which databases shard for me? Cassandra, DynamoDB, MongoDB and CockroachDB distribute natively. Vitess does it for MySQL and came out of YouTube for exactly this reason. They remove the routing work. They do not remove the physics, so fan-out queries and hot keys are still fan-out queries and hot keys.
How many shards should I start with? More than you need, each smaller than you could handle. Growth should be moving shards to new machines, not splitting data again.
How do I query across all shards? For dashboards, usually you do not. Copy the data into something built for scanning everything, a warehouse or a search index, and leave the shards serving the per-key queries they are shaped for.
Is sharding the same as horizontal scaling? Sharding is horizontal scaling applied to data. Adding stateless app servers is the easy kind. Sharding is the hard kind, which is why it comes last.
Sharding answers two problems. Too many writes, or too much data. Everything else has a cheaper fix.
Index, cache, partition, archive. In that order. Most systems never get past it.
The shard key is the decision. Pick the value your queries already carry, then check it for hot shards and hot rows.
Create more logical shards than you need on day one. Growing then means moving files, not rehashing the world.
Tutorials in System Design come first when available.
Follow a tiny next-token predictor until the logic behind modern language models becomes clear.
A repeatable framework for system design, breaking down client-to-backend architecture, isolated code execution, and high-concurrency leaderboards.
The seven layers, which of them actually exist in a real stack, and how to use the model to find out why a request is failing.