Sharding

Every step so far kept one thing the same: there is one primary database, and every write goes to it. Read replicas took away reads. Caching took away more reads. Archiving and partitioning made the data smaller and tidier. None of them help if the primary simply can't handle the writes.

That's the point where you shard. It's also the point where the design gets much harder, for good. So this lesson is half "how to shard" and half "how to avoid it for as long as you can".

What sharding is

Sharding splits your data across several independent databases, each holding a different subset of the rows. Each one is called a shard.

Each shard is a full database with its own primary and its own replicas. Shard 1 has some customers' orders, shard 2 has others, and no row lives on two shards. Together they hold everything. Because writes for different rows go to different machines, total write capacity grows with the number of shards.

Compare with partitioning from earlier in this module: partitions are pieces of a table inside one database. Shards are pieces of your data across databases. Partitioning is a storage trick. Sharding changes your architecture.

When you actually need it

Before sharding, check that you've really hit the limit. Let's do the maths for Tiffin.

At 250,000 orders a day, with 60% of them between 7pm and 10pm, Tiffin takes about 14 orders a second at peak. Each order causes maybe 15 row writes (the order, its items, status changes, payment rows). Add rider location pings if they go to the database: at 14 orders a second and about 15 minutes on the road per order, about 12,600 orders are out with riders at peak. Riders often carry two, and some are waiting for their next order, so about 10,000 riders are on shift, each sending a ping every 4 seconds. That's 2,500 writes a second.

Orders:  14 orders/s x 15 writes =   210 writes/s
Pings:   10,000 riders / 4 s     = 2,500 writes/s
Other (carts, ratings, sessions) ~   400 writes/s
                                   ~ 3,100 writes/s at peak

A well-tuned Postgres primary on good hardware handles several thousand to tens of thousands of simple writes a second. At about 3,100, Tiffin is within reach of one primary, and 80% of that is rider pings. It doesn't need sharding. It needs to move the pings to a store built for them (Redis for the latest position, batched files or a time-series database for history), which is a much smaller change and leaves the primary with about 600 writes a second.

The real triggers for sharding are:

• Write load the biggest machine you can buy can't handle, even after moving out data that doesn't belong.
• Data size that doesn't fit on one machine even after archiving: tens of terabytes of hot data.
• Regulation that says a country's data must stay in that country. This one forces sharding by geography whatever your load is.

If none of those apply, scale up, add replicas, cache, archive and partition. Those are all cheaper.

Choosing the shard key

Every row needs a rule that says which shard it lives on. That rule is based on one column, the shard key. Choosing it is the most important decision in sharding, because changing it later means moving all your data.

A good shard key does three things:

1. Most queries include it, so they go to one shard instead of all of them.
2. Things that change together share it, so transactions stay on one shard.
3. It spreads load evenly, so no single shard is much busier than the rest.

Let's try the candidates for Tiffin's orders:

city_id wins on the first two. Almost every Tiffin operation stays inside one city: the customer, the restaurant, the rider and the order are all there. Queries and transactions stay on one shard, which is the hardest property to get any other way.

Its weakness is the third column. Mumbai might have 15 times the orders of Nagpur. So you don't map one city to one shard. You map several small cities to one shard and give a very large city a shard of its own. If one city outgrows even that, you shard that city again, for example by a hash of restaurant_id. Module 14 covers hot spots like this in depth.

The router

Something has to know where each key lives. Two common designs:

• A lookup table: a small, heavily cached map of city_id -> shard. Flexible, because you can move one city by updating one row.
• A formula, like hash(key) % number_of_shards. No lookup needed, but changing the number of shards moves almost every row. Consistent hashing (Module 14) reduces how much moves.

For a key with few values, like city, a lookup table is the natural fit.

What sharding breaks

Here's the part that makes people put it off.

Queries across shards

"Total orders across India today" now means asking every shard and adding the results. Fine for an internal report. Terrible for a customer-facing page. And a customer who orders in Pune and then in Mumbai has orders on two shards, so "Your orders" has to ask both and merge them. Every query that doesn't include the shard key becomes a scatter-gather: send to all, wait for the slowest.

Transactions across shards

A transaction that touches two shards can't use a normal database transaction. If Tiffin wanted a "transfer wallet balance between two users" feature and the users lived on different shards, you'd need the techniques of Module 16. Pick a shard key that keeps your important transactions on one shard, and design features to fit it.

Unique IDs

SERIAL and auto-increment give each shard its own 1, 2, 3... so two orders on different shards can get the same ID. You need IDs that are unique across shards: UUIDs, or Snowflake-style IDs that combine a timestamp, a machine number and a counter. Module 19 covers these.

Moving data later

When a shard fills up, you split it or move some keys elsewhere, while traffic keeps flowing. That's a live migration: copy the data, keep the copy in sync with new writes, switch the router, clean up. It's slow and risky, and it's why choosing well up front matters.

Everything operational doubles

Ten shards means ten primaries, twenty or thirty replicas, ten backups to test, ten failovers that can happen at 8:15pm, and schema migrations that have to run on all ten. Small team, many shards is a recipe for pain.

Before sharding: split by function

There's a cheaper move that people often skip straight past. Instead of splitting one table's rows across databases, split the tables across databases by what they're for. This is usually called federation, or functional partitioning.

Federation gives each area of the product its own database: orders in one, accounts in another, menus in a third. Each is a normal, unsharded database with its own primary and replicas.

Why it helps:

• Write load splits along natural lines. Tiffin's primary does about 600 writes a second once the pings have moved out. Spread over three databases, none of them is near its limit, and each can grow for years.
• Each database gets the shape it needs. The menu database is almost all reads, so it gets replicas and a cache. The orders database is write-heavy and small per row, so it gets fast disks and a big primary. One database can't be tuned for both.
• A problem stays local. A runaway report on the menu database no longer slows checkout.
• It matches the teams. Federation is the data side of splitting the app: when payments and menus are owned by different teams, giving each its own database stops one team's migration from breaking another's queries.

What it costs is a smaller version of what sharding costs:

• No joins across databases. "Orders with the restaurant's name" now means reading orders, then fetching names from the menu database, or keeping a copy of the name on the order (the data model lesson covers that trade).
• No transactions across databases. Placing an order and redeeming a coupon used to be one transaction. If coupons live with accounts, they're now two writes in two places, and you need the patterns from Module 16 to keep them consistent.
• More to run. Three sets of backups, failovers and upgrades instead of one.

The rule of thumb: federation is enough when the load is spread across different parts of the product. Sharding is needed when one part, usually orders, outgrows the biggest single machine on its own. Most companies federate years before they shard, and many never shard at all.

Put it off, honestly

Notice the pattern: almost every cost of sharding is permanent and spread across every future feature. That's why it comes so late in this module, and why it comes after the cheaper fixes.

A sensible order of moves when the primary is struggling:

1. Fix the queries. Missing indexes and N+1 queries are the most common cause of a "slow database".
2. Scale up. Bigger CPU, more RAM, faster disks.
3. Take reads away. Replicas and caches.
4. Take data away. Archive, partition, and move data that doesn't belong (logs, events, pings) to stores built for it.
5. Split by function. Put unrelated tables, like rider tracking and orders, on separate databases: the federation described above. It's sometimes called vertical partitioning, and it's much simpler than sharding one table.
6. Then shard, choosing the key from your real queries.

There is also a middle path: databases that shard for you. Citus (for Postgres), Vitess (for MySQL), CockroachDB, Spanner and others hide much of the routing and rebalancing. They don't remove the need for a good shard key, and cross-shard queries are still slower, but they save you writing the plumbing yourself.