Day 4: Shard orders across three databases

Orders grow forever. Products don't. At some point the orders table is too big, too busy, or both, for one database. The answer is to split it — shard it — across several databases. The hard part isn't splitting; it's deciding where each row lives so that you can find it again, spread the load evenly, and add a database later without moving everything. In this lesson you shard ShopStream's orders across three SQLite databases with consistent hashing, then add a fourth and measure how much data moves.

The problem

Two questions decide a sharding design:

1. What's the shard key? The value that decides which shard a row goes to.

2. How do you map a key to a shard? And what happens to that mapping when the number of shards changes?

For orders, the most common query is "show me my orders". If we shard by user_id, that query hits exactly one shard. If we sharded by order_id, it would have to ask every shard. So: shard key = user_id.

The naive mapping and why it fails

The first idea is shard = hash(user_id) % N. It spreads users evenly. But when N goes from 3 to 4, almost every user's shard changes, because h % 3 and h % 4 agree only about a quarter of the time. In our data, 74.2% of users would have to move — nearly the whole database copied during a migration.

What you build

• ring.py — a consistent-hash ring with virtual nodes, about 40 lines.

• shards.py — orders live in orders-shard0.db, orders-shard1.db, orders-shard2.db. insert_order() and orders_for() ask the ring which shard to use. The shard list lives in shards.json.

• db.py — place_order() now spans two databases: it decrements stock in shop.db, then writes the order to the user's shard. If the order write fails, it puts the stock back. That's a tiny saga: no distributed transaction, just a compensating action.

• app.py — new route GET /users/<id>/orders, which returns the orders and the shard they came from.

• fill.py — writes 20,000 orders for 2,000 users. reshard.py — adds a shard and moves only the orders that need to move.

How the ring works

Picture the range of a 64-bit hash as a circle. Each database gets many points on it — virtual nodes, 100 per shard here — at md5("shard0#0"), md5("shard0#1"), and so on. To place a user, hash their ID onto the circle and walk clockwise to the first point; that point's shard owns the user. Finding it is a binary search over sorted points.