Split users into 1024 buckets, then move one bucket at a time: freeze, catch up, flip

Imagine a Flipkart-style store running on one Cloud SQL database. The disk is about 70% full, queries are slowing down, and the sale season is coming. Every order, address and payment still lands on that one primary.

The problem

One primary takes every write. Indexes, a cache, replicas and a bigger machine help, but at some point that primary is still the bottleneck. The next step is sharding: spreading per-user data across several databases.

The hard part is not creating three new databases. It is moving live users onto them while those users keep placing orders. A naive plan, such as "stop the site, copy everything, switch", means downtime. A hash like user_id mod 3 has a different problem: the day you add a fourth shard, almost every user maps to a new place.

The solution: buckets and a shard map

I split the problem into two layers.

1. A fixed rule from user to bucket. A bucket is a number computed in code, never stored per user:

bucket = int(first 8 hex digits of user UUID) mod 1024 # 07a1c3e4-... -> 128,041,956 mod 1024 -> bucket 996

This rule never changes.

2. A small table from bucket to shard. Only this table, shard_map, is stored: one row per bucket, 1024 rows in total.

bucket shard_name (live) target_shard (planned) frozen
996 old_db shard_a false

The app reads shard_map to route every query. Moving users now means changing rows in a small table, not rehashing anyone.

What moves and what stays: users, orders, addresses and payments move to shards A, B and C, about a third of users each. products, offers and other shared tables stay on the old database, along with shard_map.

Step 1: prepare with no visible change

Create the three shards with the same schema. Seed shard_map with all 1024 buckets pointing to old_db. Deploy the shard-aware app. It behaves exactly as before, because every bucket still points to the old database.

Step 2: copy and catch up

A one-off Cloud Run job copies existing users to their target shards, then runs catch-up passes for rows changed since the last pass. Users keep writing to the old database the whole time, so each pass finds fewer rows. Once a pass finds only a small number of rows a few times in a row, the job stops. Catch-up can never reach exactly zero while users are writing, and that is what the cutover is for.

Step 3: cut over one bucket

For one bucket:

  1. Set frozen = true. New writes for that bucket get a 503; reads still work.
  2. Wait for in-flight writes that started before the freeze to finish.
  3. Run a final catch-up for that bucket and verify the counts.
  4. Flip shard_name to shard_a and set frozen = false.

The client retries the 503, and the retry lands on the new shard. Users in that bucket see a few seconds of "try again", and nobody else notices anything.

Step 4: roll out in waves

Start with one quiet bucket as a pilot. Then move a wave of 10, then a larger wave, then the rest. Only one bucket is frozen at a time. Watch errors, latency, 503 counts and shard CPU and disk between waves.

After the last flip, keep the old copies of the moved tables for a safe window as a rollback option. Take a final backup, then drop them. The old database lives on as the products database, with its replicas and cache.

Adding a fourth shard later

The same machinery works. Set target_shard = 'shard_d' for the chosen buckets, copy from each bucket's current shard, then freeze and flip bucket by bucket. The bucket rule never changes, so no user is rehashed. Only whole buckets move.

Trade-offs and limits

  • Shard last, not first. Sharding adds a router, a migration job and cross-shard query problems. I would only do it after indexes, cache, replicas and a bigger machine have run out.
  • Cross-shard queries get harder. "All orders in Chennai yesterday" now touches three databases. Reports should come from an analytics copy, not from the shards.
  • The 503 is a real cost. It is small, but the client must retry safely, so writes need to be idempotent.
  • Disk does not shrink by itself. Dropping the moved tables frees used space, but the provisioned disk size stays where it was.
  • Pick the bucket count up front. Changing the bucket rule later means rehashing everyone.

Takeaway

Map users to fixed buckets, and store only the bucket-to-shard map.

Move one bucket at a time: freeze, final catch-up, verify, flip.

The same steps that create the first shards also add the next one.