The shape
You changed something, and the change needs to apply to data that already exists. A new column needs populating for a billion rows. A better image codec means re-encoding every image you have ever stored. A bug wrote wrong values for three weeks and they have to be recomputed. A new search field needs indexing across every document.
A backfill is running logic over data that was written before the logic existed. It looks like an ordinary job and it is not, for three reasons. All three come from the same two facts: the volume, and the live system still running while you sweep.
It has to get through years of data in weeks. Your live system is sized to take one day of writes per day. A backfill takes every day you have ever had, and has to finish inside whatever window you were given. At 100 million writes a day, a year of history is 36.5 billion rows. Getting through that in 30 days means running at 12 times the live write rate, while the live path carries on underneath.
It competes with production for the same resources. The same database, the same object store, the same downstream services. A backfill that runs at full speed is a self-inflicted load test that you scheduled.
The data is moving while you read it. Rows are being written, updated and deleted as you sweep. Any backfill that takes longer than a few minutes has to say what happens when it meets a row that changed after it started.
One part of this is decided years before you need it: whether the source data still exists. Switching to a better image codec means re-encoding from the original uploads, not from the copies you have been serving. Those copies already went through the old codec once, and that loss cannot be undone. Recomputing a wrong value needs the inputs that produced it, not the wrong value itself. If you discarded the source to save money, this pattern is not available to you at all, and you learn that at the moment you need it.
The moves
Every durable backfill has the same four properties, and the first is the one that gets skipped.
A durable cursor. As the job works through the table it has to record how far it has got, and that record has to survive the job dying. A row in a table, or a key in Redis. Not a variable in memory. What you are recording is a position, so you need a column you can sort by and that never changes underneath you. The primary key is usually the right choice, because it is unique, it is already indexed, and nothing updates it. Resuming is then a single query: give me the next 1,000 rows after this id.
Backfills get killed all the time. A deploy restarts the pod it was running in, or the scheduler evicts it, or somebody stops it because the live path got slow. If the job cannot pick up where it left off, it starts again from the first row. At this scale that is days of work thrown away, and a second round of load on the systems you were trying to protect in the first place.
A throttle you can change without a deploy. The right rate is not knowable in advance because it depends on live traffic, which varies. Put the rate in configuration, watch the p99 of the live path, and turn it down when that moves. A backfill with a fixed rate is either too slow to finish or too fast to be safe.
A dry run that reports without writing. Run the new logic, compare against the existing value, count the differences, and write nothing. The count is what you are after: if you expected 2 percent of rows to change and 60 percent differ, the logic is wrong and you have learned it for free.
Idempotent units of work. Any row may be processed twice, because retries and resumes overlap. Reprocessing must produce the same result, which usually means computing the new value from the source rather than mutating the current value.
Two strategies for the live writes:
In-place updates the live column as it goes. Fast and simple, and if the logic is wrong you have destroyed the old values and the recovery is a restore. Acceptable when the transformation is derivable from data you still hold.
Shadow write puts the new value beside the old one, in a new column or table. Reads keep using the old value while you compare the two in production, then you switch over and drop the old one. Slower and it costs storage, and it is reversible, which usually matters more.
The plan is to move every stored image to a better codec. The question is how long that takes, and what actually limits it. Size the compute first, then the read side. images stored 100M/day x 365 = 36.5B bytes 36.5B x 3 MB each = 109.5 PB CPU per image 400 ms Turn that into machines. A core delivers 1 second of CPU per second, so one core-day is 60 x 60 x 24 = 86,400 s: total CPU 36.5B x 0.4 s = 14.6B s ─────────────────────────────────────────────── core-days 14.6B s / 86,400 = 169,000 Say you want it finished inside 30 days: cores 169,000 / 30 = 5,633 machines 5,633 / 32 cores each = 176 176 machines is large but ordinary. Now the read side, because the source images sit in archive storage: restore time 109.5 PB / 1 GB/s = 109.5M s in days 109.5M s / 86,400 = 1,267 1,267 days to read the input, against 30 days of compute. The restore rate is the constraint and the CPU never was. You can rent more cores. You cannot rent a wider pipe. ⇒ raise the restore throughput, or accept a campaign that runs for years, or re-encode lazily on read and only ever touch the images somebody actually asks for. The lazy option is usually right, and you never see it if you size only the CPU.
That result generalises. The bottleneck in a backfill is rarely the part you would think to optimise. It is the read side, the downstream service with a rate limit, or the lock contention with live writes. Size all of them before committing to a date.
How to tell you have this problem
A migration added a column with a default and you called it done. For rows that already existed, that default is not the right value. So either those rows are wrong, or something on the read path is computing the right answer every time it meets one. That second one is the backfill you skipped, charged to every read forever instead of run once.
A bug fix corrects behaviour going forward. Everything written before the fix is still wrong. Somebody has to decide whether that matters, and "we fixed it" is not that decision.
You want to adopt a better format and nobody knows if you can. The question is whether the source survives and what the restore rate is. Both are answerable today and they decide whether the option exists.
Your derived data can drift from its source. Any index, cache, aggregate or denormalised copy can diverge. A reprocessing job that rebuilds it from source is the only way to prove it has not, and it belongs on a schedule rather than being written during the incident.
What it costs
It is production load that you scheduled. The backfill competes with live traffic for exactly the resources live traffic needs. Run it against replicas where you can, throttle it always, and expect to pause it.
The window is not the runtime. A 30-day backfill occupies 30 days of somebody's attention, spans deploys and on-call rotations, and will be interrupted. Anything that cannot survive being interrupted will never finish.
Shadow writes double the write cost for the duration. Every live write now updates two places, and the backfill is updating the second one too. That is a capacity increase on your busiest path for the whole campaign.
The comparison step gets skipped under pressure. The dry run and the shadow comparison are what make this safe, and they are the first things dropped when the change is urgent. That is exactly backwards: urgency is when the logic is least reviewed.
Deleted and changed rows during the sweep need an explicit answer. A row deleted after the cursor passed is fine. A row updated after the cursor passed may now hold a value computed by old logic. Either the backfill re-sweeps changed rows, or the live path applies the new logic from the start of the campaign.
Where it shows up in the course
| article | the form it takes there |
|---|---|
| a re-encoding campaign over archived originals, which is the reason to keep them and the thing the storage tier decides | |
| Partitioning and Sharding | phase one of a live resharding cutover, and what it owes rows that change after the cursor passes |
| URL Shortener | an archive sink that drops nothing, so click history can be recomputed when the bot rules turn out to be wrong |
| YouTube | a codec migration across an exabyte archive, made possible or impossible years earlier by whether sources were kept |
Getting it wrong
No cursor. The job gets killed and starts again from the beginning. It will be killed.
A rate that requires a deploy to change. The correct rate depends on live traffic, so it has to be adjustable while running.
Sizing only the compute. The constraint is usually the read side or a downstream rate limit. The re-encoding case above is bounded by restore throughput, and no amount of extra cores moves it.
Mutating in place when the transformation is not reversible. If the old value cannot be recomputed, write beside it and compare before switching.
Skipping the dry run because the logic is obvious. The difference count is nearly free and it is the only check that catches a wrong assumption before it has touched a billion rows.
Ignoring rows that change mid-sweep. A backfill over a live table has to say what happens to a row updated after the cursor passed. Say it explicitly rather than discovering it in a consistency report months later.