Mid-way through an expand-contract migration, the application writes each order's country to both the legacy varchar column and a new normalised column. After the dual-write deploy, write p99 on the orders table rises from 18 ms to 140 ms, but only between 20:00 and 23:00 and only on 2 of 16 shards. Read p99 is flat, CPU sits near 35% and no errors are logged. Where is the time going, and what do you change first?

The first three things I would look at, and why in that order

1. What else is running in that window on those two shards. A regression bounded in both time and shard space is almost never the code path that was deployed everywhere - the deploy went to all 16 shards and all 24 hours. Something shard-local and time-local is contending.
2. Backfill progress by shard. Expand-contract has three concurrent writers: the application's old write, the application's new write, and the backfill populating the new column for historic rows. The two slow shards are the ones where the backfill is still running, and 20:00 is when its schedule overlaps the evening order peak.
3. Wait events and I/O, not CPU. 35% CPU with a latency tripling means the time is spent waiting - on locks, on buffer flushes, or on storage. CPU is the misleading signal here and the reason the team has been staring at the wrong dashboard.

The diagnosis

The backfill updates rows in batches. Each update rewrites a row that was previously packed tightly, and the new value does not fit in place, so the engine writes a new row version and leaves the old one behind. On those two shards - which hold the two largest tenants, so the backfill has the most rows left - the table's live pages are being churned under the write path, the buffer pool hit rate on the hot pages drops, and foreground writes start paying for storage reads. The dual write adds perhaps a millisecond; the backfill adds the rest.

The second contributor is the index created on the new column during the expand step. It is maintained on every insert and every backfill update, roughly doubling the index work in the window.

The fix, in order

1. Throttle the backfill against a live signal, not a fixed rate. Target a ceiling on observed write p99 or replication lag, with the batch size adapting. A fixed "500 rows per second" is wrong on every shard it was not tuned for.
2. Move it out of the peak for the shards still running, and accept a longer backfill. Nothing downstream depends on it finishing on a particular day.
3. Create the new index after the backfill, not before, unless a reader needs it during coexistence. Building it once at the end is cheaper than maintaining it through the rewrite of every row.
4. Only then consider the dual write itself. Its cost is real and small, and it is the part that cannot be deferred.

The alert that would have caught it earlier

Write p99 broken out per shard, on the same chart as backfill rows-remaining per shard. The aggregate p99 across 16 shards moved from 18 ms to about 33 ms, which looked like noise. Per shard it was an eightfold regression visible on the first night. The general rule: during any migration, publish the migration's own progress next to the latency of the system it is migrating, because the two are a single causal picture and nobody correlates them from separate dashboards at 21:00.

Common weak answers

• "Roll back the dual-write deploy." It is the most recent change and roughly a millisecond of the 122 ms regression. Rolling it back stops the migration and leaves the backfill running.
• "Scale up the two shards." More CPU on a path that is waiting on I/O buys nothing, and it rewards the team for not finding the cause.
• "It is only p99, and reads are fine." At 20:00 on an order path, p99 write latency is checkout latency for one request in a hundred.