Database Sharding Workflow
Pick a shard key with even distribution, route writes through a shard-aware driver, and migrate rows shard by shard while a dual-write window keeps the old and new in sync.
Objective
Split a large table into shards without downtime by choosing a good shard key and migrating rows shard by shard behind a dual-write gate.
Environment
- A database with a table too large to fit on one node
- A shard key candidate with measurable distribution
- A shard-aware driver or a routing layer
Steps
1. Choose the shard key
Measure the distribution of candidate keys and pick one that spreads writes evenly and keeps related rows together.
Expected state: A shard key with a heatmap that shows no hot spots and a max-to-min ratio under two.
2. Provision the shards
Create the shard nodes, apply the schema, and configure the routing layer to send new writes by the shard key.
Expected state: New writes land on the correct shard; reads of new rows go to the right shard.
3. Dual-write and backfill
Turn on dual-write for new rows, backfill existing rows shard by shard, and verify counts match between old and new.
Expected state: Every row exists in both places, and the count per shard matches the source.
4. Cut over reads
Switch reads to the shards behind a flag, watch the error rate, and fall back to the source if the rate rises.
Expected state: Reads are served by the shards, the error rate is flat, and the flag is on for the whole fleet.
5. Decommission the source
After a clean window, stop dual-write and archive the source table.
Expected state: The source table is read-only and then archived; the shards are the system of record.
Decision points
Which shard key?
- user_id for even spread
- created_at for time locality
user_id for even spread; time-based keys create a hot shard for the current period.
When to stop dual-write?
- After one clean day
- After one clean week
One clean week, so a weekly batch job that reads the source has a full cycle on the shards.
Expected outcome
The table is split across shards with no downtime, writes are evenly distributed, and the source is decommissioned after a clean window.
Verification
Confirm the shard heatmap has no hot spots, the per-shard counts match the source, reads are served by the shards, and the error rate is flat.
Cleanup
Archive the source table, remove the dual-write code, and record the shard map in the runbook.
The shard key is everything
A bad shard key makes every other step harder. Measure the distribution of candidates before you commit. A time-based key creates a hot shard for the current period; a user id spreads writes evenly.
SELECT shard_key, count(*) FROM big_table GROUP BY shard_key ORDER BY count(*) DESC LIMIT 10;Dual-write, then backfill
Turn on dual-write for new rows so the shards stay in sync with the source. Backfill existing rows shard by shard, verifying the count per shard matches the source. The dual-write window is the safety net.
Cut over and decommission
Switch reads to the shards behind a flag. Watch the error rate and fall back if it rises. After a clean week, stop dual-write and archive the source. The shards are now the system of record.
Verification
The shard heatmap has no hot spots, the per-shard counts match the source, reads are served by the shards, and the error rate is flat. Keep dual-write for a full week so a weekly batch job has a cycle on the shards.