The table is 800GB and the shard key turned out wrong. How do you reshard while writes continue?

Short answer

Online resharding works when the table is moved as small virtual buckets, not as one 800GB operation. Copy and stream changes for one bucket, briefly freeze only that bucket, verify it, flip the directory entry, and keep rollback until the source rows can be safely cleaned up.

A shard key change is not an update

Start with the thing that reframes the whole problem. The shard key decides which machine holds the row. Change it and the row belongs somewhere else, so the operation is an insert on the destination and a delete on the source, with no transaction spanning the two. There is no ALTER for this. Nobody can run one statement and wait.

That is why this is a data-movement project rather than a schema change, and why the 800GB is the constraint that sets the calendar. Put a number on it from a stated budget. If you can spare 20MB a second of sustained copy throughput without hurting production latency, 800GB takes about eleven hours of pure transfer. Squeeze that to 5MB a second and it is nearly two days, before you have verified anything. Indexes on the destination are extra, and building them as you insert is slower than building them after.

State that arithmetic early in an interview. It moves the conversation from "which technique" to "which technique for a job that runs for days while the product stays up", which is the question being asked.

Never route on hash-modulo-shard-count

If the routing rule is the hash of the key modulo the number of shards, changing anything moves nearly everything. Going from four shards to five leaves only the rows whose hash agrees under both moduli in place, which is one in five. Eighty per cent of 800GB moves because you added one machine, and every future change costs the same.

The replacement is an indirection. Hash the key into a large fixed number of virtual buckets, and keep a small directory mapping buckets to shards. The bucket count never changes. Only the directory changes. That is the whole trick.

# The bucket count is fixed for the life of the dataset.
bucket = crc32(new_shard_key) % 4096

# The directory is small enough to cache everywhere and to edit one row at a time.
# Moving data means moving buckets, not rehashing keys.
shard  = directory[bucket]        # 4096 entries, not 800GB

At 4096 buckets, one bucket of an evenly spread 800GB dataset is about 200MB. That is the unit you move, verify and roll back, and it is small enough that a mistake costs minutes rather than the project. Pick the bucket count generously at the start; it is the one number that is painful to change later, and 4096 buckets across four shards is not waste, it is the option to have forty shards without another migration.

Moving one bucket, with a freeze nobody notices

The per-bucket procedure is where the "writes continue" requirement is actually met. Nothing is frozen at table scope at any point. One bucket at a time is briefly unavailable for writes, and a bucket is a few thousandths of your key space.

The steps go in this order. Copy the bucket's rows to the destination shard with upserts, so the copy is restartable and a partial run costs nothing. Stream changes to those rows to the destination while the copy runs, keyed by primary key so replay is idempotent. When the destination has caught up to within a small lag, mark the bucket as migrating in the directory, which makes writes to it wait rather than fail. Drain the in-flight writes, apply the last few changes, compare a checksum of the bucket on both sides, then flip the directory entry and release the waiters. Delete the source rows later, on a separate schedule.

The freeze is the interval between marking and flipping, and it is bounded by the drain plus the residual delta. Keep the delta small by not flipping until replication lag is low, and the freeze is milliseconds. Get that wrong and it is the length of your longest in-flight write, which is why request timeouts on that path matter more than they look.

An air-traffic controller closing one runway is the right picture. Aircraft hold briefly, the other runways keep working, and the airport does not shut. It breaks down where the analogy usually does: a runway does not need its contents copied first, and the copy is where all the elapsed time goes.

Two copies means two copies of the disk

For the duration of the migration, migrated buckets exist on both the source and the destination, because deleting the source rows immediately removes your rollback. So plan for the peak. The average is not what fills a disk. Provision as though the dataset were 1.6TB plus the destination's indexes, and add whatever the change stream buffers if a consumer stalls.

The source shards also lose disk in a less obvious way. Deleting rows in bulk after a bucket flips leaves space that the storage engine may not return to the operating system, and on some engines it leaves the table's physical size unchanged while raising write amplification. Delete in batches with pauses, plan the reclaim explicitly, and do not schedule anything that depends on the space being free the same afternoon.

The reshard succeeds and some queries get worse

The new key fixes the access pattern you noticed. It also changes every other access pattern, and this is where a candidate either sounds like somebody who has done it or somebody who read the runbook.

A query that used to be answerable from one shard because it filtered on the old key now fans out to all of them. If that query is on a hot path, the reshard has traded a visible problem for a diffuse one, and diffuse is harder to get funded a second time. So before starting, list the top queries by volume, mark for each one whether it is single-shard under the old key and under the new key, and decide what covers the ones that regress. Usually that is a second table keyed the other way, maintained by the same write path, which is a design decision to make now rather than to discover in a month.

A strong candidate says the uncomfortable version out loud: "Two of our five top queries get slower. I would rather ship the reshard with a lookup table for those two than have them surface as a mystery latency regression after cutover."

The unit of a reshard is not the table, it is the bucket. Once a small directory decides where a bucket lives, an 800GB migration becomes four thousand small reversible moves, and nothing about it needs a maintenance window.

---

© 2026 Preptima. Originally published at preptima.com.