01 · Where partitioning left you
The data was divided. The machine was not.
Sixty partitions. A query for last month opens one of them and ignores the rest. Retention is a
DROP. Maintenance runs piece by piece.
Now look at what those sixty pieces have in common. They share one buffer pool. One write-ahead
log. One CPU. One disk. One fsync queue. Splitting the table changed how much data
each operation had to consider — it did not change what those operations were
running on.
What if each piece had its own?
Not just its own file — its own memory, its own CPU, its own disk, its own log. Pieces that do not queue behind each other, because there is nothing shared left to queue for.
That question is sharding.
Sharding splits your data across several independent databases, each owning a different subset of the rows and serving it with its own hardware.
The column whose value decides which machine a row lives on is the shard key.
Shard orders by customer_id and every row for customer 84217 lives on one
machine — every read and every write for that customer goes there and nowhere else.
One practical difference before going further: there is no CREATE TABLE ... SHARD BY.
PostgreSQL has no built-in sharding. You are either running four separate databases and routing
between them in your application, or adopting something that does it for you — Citus, Vitess,
or a managed equivalent.
That is not a footnote. Partitioning is a schema decision. Sharding is an architecture.
02 · The one thing that improves writes
Reads can be made smaller. Writes cannot.
Everything so far has made reads cheaper. Indexes cut the pages a query touches. Partitions remove whole regions from consideration. Even lesson 04 was about understanding write cost, not reducing it.
None of them raised the write ceiling, and that is not an oversight.
A read can be narrowed until it touches almost nothing — find the row, read the page, done. But every write has to be durable. It goes into the same log, gets flushed by the same disk, on the same machine, in the same queue. You can make a write cheaper by dropping an index. You cannot make it go somewhere else.
So on one machine there is a hard ceiling on commits per second, and no amount of reorganising the data moves it. Sixty partitions do not give a server a second core.
Sharding is the first thing here that changes the denominator: it increases the total resources available to the database, and it is the only technique in this course that raises the write ceiling.
Four shards means four write-ahead logs accepting commits, four buffer pools, four CPUs, four disks. It multiplies roughly linearly — four shards is about four times the write capacity, eight is about eight.
That is the whole reason to accept everything it costs, and the rest of this lesson is that cost.
03 · Where it pays
Five problems only more machines can solve.
1. Write throughput beyond one machine
The case sharding exists for. Orders arrive faster than one server can commit them. The indexes are
right, the partitions prune, the reports are quick — and the machine is still saturated,
because every insert queues for the same fsync on the same disk.
Nothing on that machine will help. Four shards give you four independent commit paths, and the queue divides. Worth stating precisely: if reads are your problem, sharding is an expensive way to solve something an index or a partition would have handled. It earns its cost only when writes are what ran out.
2. A working set larger than any single machine’s RAM
Partitioning shrinks the hot slice so it fits in memory. But if your genuinely hot data — the rows queried constantly, right now — exceeds the RAM of the largest machine you can buy, partitioning has nothing left to give. It cannot add a gigabyte.
Sharding can. Four machines hold a quarter of the data each and bring their own memory, so total buffer pool multiplies while the per-machine working set shrinks. You gain twice.
3. Data larger than one machine’s disk
The simplest case, and the least interesting once you see it. At some point the dataset does not fit on one server. Sharding is how a database outgrows a single disk without moving to a different kind of system entirely.
4. Isolating a large or noisy tenant
In a multi-tenant system, one customer eventually becomes a problem — a third of the traffic, or a workload that evicts everyone else’s pages from the buffer pool.
With directory-based routing you can move that tenant onto a shard of their own. Their heavy queries stop affecting everyone else, and you can size their machine differently. This is a real, common reason to shard that has nothing to do with total volume.
5. Placing data geographically
Shards do not have to live in the same place. European customers on European infrastructure, US customers on US infrastructure, routed by the same shard key. Latency improves for both, and data-residency requirements become a routing rule rather than a separate system.
04 · The shard key
A query must carry it, or it goes to every machine.
You met this rule with partitions, and it applies here with more force. A partition only prunes if you filter on the partition key. A shard can only be targeted if the query carries the shard key.
Same rule, one scale up — and the penalty has grown from opening every partition, to sending a request to every machine you own.
A workable key needs three things
- Many distinct values, enough to spread across the shards you will eventually have — not just the ones you have today.
- Even spread, so no single value owns a large share of the rows or the traffic.
- Presence in your common queries, or they fan out to all of them.
These pull against each other. A random UUID distributes flawlessly and aligns with nothing. A
tenant_id aligns with every query in a SaaS application and distributes terribly the
moment one customer grows. Resolving that tension is the design work.
How keys map to machines
- Range — shard 1 takes A–F, shard 2 takes G–M. Ordered scans stay efficient, but a sequential key sends every new row to the last shard, and you have built a distributed system where one machine does all the writing.
-
Hash —
hash(key) mod N. Spreads beautifully until you change N. Going from four shards to five re-maps roughly three quarters of your rows, all of which must move while the system serves traffic. Not wrong on the day you build it; wrong on the day you grow. - Consistent hashing — keys and shards both placed on a ring, each key belonging to the next shard clockwise. Adding a shard moves only about 1/N of the keys. This exists specifically to fix the problem above.
- Directory — an explicit lookup table recording where each key lives. Costs more and becomes a critical dependency, but it is the only option that lets you move one particular customer. Common in real multi-tenant systems for exactly that reason.
05 · What you give up
One database gave you guarantees you never had to think about.
Four cannot, because nothing sits above them that understands all four.
- Queries without the shard key go everywhere. They must be sent to every shard and combined, so your answer arrives no faster than the slowest machine. Occasional slowness anywhere becomes routine slowness for you.
- Joins across shards stop working. A database can join two tables in one file. It cannot join a table on one machine to a table on another. You denormalise, copy small reference tables to every shard, or fetch both sides and join in application memory.
- Transactions no longer span everything. "All or nothing" across two machines needs coordination that is slow and fails badly — if the coordinator dies mid-commit, participants hold locks waiting for a verdict that never comes. Most successful systems refuse the problem instead, choosing a key that keeps transactions inside one shard.
- Even spread is not even load. Hashing distributes rows. It knows nothing about traffic. Your hash function can be perfect and your cluster still lopsided.
- Growing means moving live data. Adding a shard is not a configuration change. Rows must be copied, kept in sync while both copies serve traffic, verified, and cut over without losing writes in the middle. Plan it before you need it, because you will need it under pressure.
Global uniqueness gets harder — again
You met a version of this with partitioning: a unique constraint had to include the partition key, because each partition’s index could only enforce uniqueness locally.
Sharding is that problem without the escape hatch. There is no cross-shard index at all, and
bigserial cannot work because two shards would issue the same value. You need
externally generated IDs, and the choice matters: UUIDv4 is random, which scatters
inserts across the B-tree and causes constant page splits, while UUIDv7, ULID, and
Snowflake embed a timestamp and land together at the right edge of the index.
06 · When to shard, and what it does not fix
Capacity and survival are different problems.
Shard last. Genuinely last — it is the only tool here that raises the write ceiling, and the only one that is close to irreversible. Every feature you build afterwards has to respect it.
The order of the work is the order of this course: fix the queries and the indexes, drop the indexes nobody uses, partition where the data has a natural boundary, buy a bigger machine — badly underrated, since a large modern server costs less than the engineering months sharding consumes — and only then shard.
And it buys capacity, not safety
Four machines are four things that can fail, and a shard going down takes its share of the data with it — not a quarter of your latency, a quarter of your answers. In availability terms you have made things worse: there are now four ways to be partly down instead of one way to be fully up.
Solving that means keeping more than one copy of the same data — which is replication, and it brings a question sharding never had to ask: when two machines hold the same row, which one is right?
The Old Dragon “You have many keeps now, and no single gatekeeper who knows them all. That knowledge is yours to carry, keeper — forever.”