A Backfill Is a Long-Running Mutation, Treat It Like One

At some point you have to change every row in a big table — re-encode a column, migrate a format, decrypt data in place, populate a new field. The instinct is to write one big UPDATE and let it rip. Don’t. At millions of rows, that’s not a query, it’s a long-running mutation against a live system, and it fails in ways a query never does: it runs for hours, it competes with real traffic, it gets interrupted halfway, and if it goes wrong it can take the whole system with it. A backfill is its own kind of operation, and it earns its own set of rules.

The bottleneck isn’t your loop, it’s the pressure

The first surprise is that the raw work is rarely the slow part. When I sized a real backfill, the per-row transformation was trivial — the node could do far more per second than I’d ever let it. What actually set the pace was everything the writes provoked downstream: storage churn, replication, index maintenance, background compaction, all straining under a flood of rewrites. Push too fast and you don’t just slow the backfill, you degrade the live system the data belongs to.

A backfill’s speed limit isn’t how fast you can transform a row. It’s how much collateral pressure the system can absorb while still serving real traffic.

That reframes the whole job. You’re not trying to go as fast as possible; you’re trying to find the sustainable rate — fast enough to finish in reasonable time, slow enough that production never notices. Which means the first feature a backfill needs is a throttle.

The properties a backfill has to have

Because it’s long-running and lives alongside real traffic, a backfill needs to be built, not just typed:

• Chunked. Work in bounded ranges — by key or token range — not one transaction over the whole table. Small chunks bound the blast radius of any single failure and give you natural checkpoints.
• Throttled. A deliberate, tunable rate limit, set below the pressure ceiling. This is the main dial, and it’s the one that keeps the backfill from becoming an outage.
• Resumable. It will get interrupted — a deploy, a hiccup, a human stopping it. It has to restart from where it left off, using the last completed range as its checkpoint, not start over from zero.
• Idempotent. Re-running a chunk that’s already done must be a harmless no-op. Combined with resumability, this is what makes “just run it again” safe after any interruption.
• Guarded against clobbering live writers. If real traffic is still changing rows while you backfill, every write must be conditional on the value you read — a compare-and-set, so a row a user changed mid-backfill is skipped, not overwritten.
• Non-destructive by construction. It only ever reads and conditionally rewrites. No TRUNCATE, no blind deletes, no writing empty values — so a bug can’t destroy data, only fail to convert a row you can retry.

Miss one of these and the backfill is fine right up until it isn’t: it stampedes production, or it dies at 60% and can’t resume, or it overwrites a live user’s change, or a bad chunk nukes something unrecoverable.

Rehearse the timing, don’t guess it

The other thing a backfill needs is a number for how long it’ll take, and that number should come from measurement, not optimism. “It’ll be a few hours” is a guess; a guess that’s wrong by 10x is how a maintenance window blows up. So before a real run I build the throttle curve — try a few rates, watch the downstream pressure, find where latency starts to suffer — and get an honest projection at the rate I can actually sustain. That’s building the rehearsal instead of extrapolating, applied to a mutation: the safe sustainable rate times the row count is your real duration, and it’s usually longer and calmer than the optimistic one.

Snapshot first, dry-run first

Two cheap habits bracket the whole thing. Take a snapshot before you start, so there’s a known-good point to fall back to if the plan is wrong at a level no per-row guard can catch — just remember a snapshot is a rollback anchor, not a durable backup, which is exactly the short-lived undo you want here. And do a dry run first — the same code path, reporting what it would change without changing it — so the backfill’s judgment is boring and correct before it’s live, the way any automation should preview before it acts.

A big data change feels like it should be a big query. It’s really a small, careful program that happens to run for hours next to production. Build it that way — chunked, throttled, resumable, idempotent, guarded, rehearsed, snapshotted — and a scary migration becomes a slow, boring, safe one. Boring is the goal. If you’ve run a backfill that went smoothly because it was built like this (or roughly because it wasn’t), I’d like to hear about it.