Backfills
Moving or computing data across every existing row is a long-running production write workload, and it needs the properties of a job rather than of a migration.
The question, the obvious approach, and why it breaks
Every lesson starts where the work starts: an operational problem, a first attempt that is entirely reasonable, and the way production disagrees with it.
How do I populate a new column across a hundred million existing rows without hurting the service?
The schema change is instant and the data change is not. A backfill runs for hours against live traffic, competes for the same I/O, and is the part of the migration most likely to still be running when something else goes wrong.
Write one UPDATE covering the whole table and run it in the migration. It is one statement, it is transactional, and when it finishes the column is populated.
One statement means one transaction held open for the entire duration. On PostgreSQL that blocks vacuum from cleaning up, so the table and its indexes bloat while the update runs (MVCC: Multi-Version Concurrency Control).
- One statement means one transaction held open for the entire duration. On PostgreSQL that blocks vacuum from cleaning up, so the table and its indexes bloat while the update runs (MVCC: Multi-Version Concurrency Control).
- Every updated row is written, journalled and replicated. A whole-table update generates write volume proportional to the table, which lands on WAL or binlog, on disk, and on every replica at once (Replication Lag: Reads That Are Correct and Stale).
- It cannot be paused, throttled or resumed. The only control available is killing it, and a kill near the end throws away all the work.
- It has no progress. From the outside, a long
UPDATEand a hungUPDATEare indistinguishable, which is a terrible property at 3am. - It takes locks on every row it touches, so concurrent writers to those rows block behind it (Locks and Deadlocks).
- Run inside the migration step, it blocks the deploy behind it — turning a schema change into an hours-long release.
What is actually happening
Underneath the tooling, which is the part that survives a change of tool.
- A backfill is a batch job that happens to write to the production database. The properties it needs are job properties: bounded work per unit, a durable cursor, idempotency, throttling, observability and a stop button (Background Jobs and Workers).
- Bounded batches keep each transaction short, which keeps locks short, keeps row versions collectable, and keeps the replication stream in chunks the replicas can keep up with.
- A durable cursor — usually the primary key of the last processed row — is what makes it resumable. Offsets are not cursors: an
OFFSETscan re-reads everything it skipped and gets slower as it progresses. - Idempotency comes from the predicate.
WHERE new_col IS NULLmeans re-running a batch is a no-op, so a crash mid-batch costs one batch rather than the run. - Throttling needs a feedback signal rather than a fixed sleep. Replication lag is the best available one: it rises before user-visible symptoms do and it captures the load the backfill is actually creating downstream.
- The backfill only handles history. New rows are covered by the dual-write step that preceded it — which is why the ordering in Expand, Migrate, Contract puts the code deploy before the backfill.
The loop, and why each part is there
Every line in this loop is load-bearing, and each corresponds to one of the ways an unstructured backfill fails.
1-- One batch. Runs in its own transaction. Repeat until it affects zero rows.2UPDATE orders3 SET fulfilment_state = legacy_status4 WHERE id IN (5 SELECT id6 FROM orders7 WHERE id > :cursor -- keyset, not OFFSET: constant cost per batch8 AND fulfilment_state IS NULL -- the predicate IS the idempotency9 ORDER BY id10 LIMIT :batch_size11 )12RETURNING id; -- the max id becomes the next :cursor13 14-- Between batches, the job asks the throttle, not the clock:15-- lag = replication lag now16-- if lag > pause_threshold: wait until lag < resume_threshold17-- else: continue immediately18 19-- Completion check. Scans. Run it once, at the end, and mean it.20SELECT count(*) FROM orders21 WHERE fulfilment_state IS NULL AND legacy_status IS NOT NULL;The RETURNING is what makes the cursor durable without a second query. The IS NULL predicate is what makes a crash cost one batch rather than the run. And the throttle reads a signal rather than sleeping a constant, because the load you are adding is only knowable relative to the load already there.
Six properties, and what happens without each
A backfill missing any one of these still works in a test environment. Each missing property shows up only at production scale, which is why they are so often discovered one incident at a time.
- 1Bounded batches
Keeps each transaction short, so locks are short and cleanup can keep up.
fails by One statement over the whole table; bloat, lock waits, and nothing to do but wait.
evidence Transaction duration per batch stays flat as the job progresses.
- 2Durable cursor
Lets the job resume exactly where it stopped.
fails by
OFFSETiteration, which re-scans and degrades, or no cursor at all, which means restarting from zero.evidence Killing the job and restarting it resumes within one batch of where it stopped.
- 3Idempotent predicate
Makes any batch safe to re-run.
fails by An unconditional update, so a retry rewrites rows another process has since changed.
evidence Re-running a completed batch affects zero rows.
- 4Feedback throttle
Slows the job when the database is under strain, without a human watching.
fails by A fixed sleep tuned for a load level that changed an hour later.
evidence Replication lag stays banded, and the throttle logged that it engaged at least once.
- 5Progress and rate
Turns "is it stuck" into a lookup.
fails by Silence, so the only diagnostic is whether the process is still alive.
evidence Rows remaining and current rate on a dashboard, giving a defensible completion estimate.
- 6Stop switch
Ends the job immediately without a deploy.
fails by Stopping requires killing a process someone has to find first.
evidence The switch has been used once, on purpose, before it was needed.
The stop switch is the one most often skipped and the one most often wanted. During an unrelated incident, "stop all background writes" is a standard mitigation, and a backfill you cannot stop is an ingredient in someone else's outage (Load Shedding).
How backfills actually go wrong
These are the failure modes that survive a code review, because none of them are visible in the SQL — they are properties of the job's behaviour over hours.
| Trigger | Symptom | Cause | Response |
|---|---|---|---|
| Batch size raised mid-run to make it finish sooner | Replication lag climbs; read replicas serve stale data; latency on read paths rises | The throttle was calibrated for the old batch size, or there was none | Lower it back, wait for lag to recover, and accept the original completion estimate |
| Job crashes at 80% | Restart begins from row zero | No durable cursor, only an in-memory position | Add the cursor before restarting, and let the idempotent predicate skip the completed range cheaply |
| A row that always fails to convert | The job retries forever and the cursor never advances | No poison-row handling; the batch is retried as a unit | Record the row, skip it, and reconcile the exceptions separately |
| Backfill running during an unrelated incident | Database under strain from two directions; mitigation options are unclear | The backfill is not in anyone's mental model of current production load | Stop it via the switch; annotate the incident timeline with the fact that it was running (Deploys on the Same Timeline as the Symptom) |
| Verification by sampling | A gap discovered weeks later, in rows from one specific window | A write path that was not dual-writing during part of the run | Re-run the backfill; the idempotent predicate makes this cheap, which is the point of writing it that way |
| Backfill also emits an event per row | Downstream consumers see duplicate events after a retry | Idempotency covered the database write and not the side effect | Make the event emission idempotent too, or separate it from the backfill entirely (Job Idempotency) |
How to do it properly
Most important first.
- Batch by primary key range, not by
LIMIT/OFFSET, and commit each batch. Keyset iteration keeps the cost per batch constant. - Make the predicate the idempotency mechanism, so any batch can be re-run safely and the job can crash anywhere.
- Throttle on replication lag, with a pause threshold and a resume threshold. Fixed sleeps are tuned for a load level that will not persist.
- Emit progress: rows processed, rows remaining, current cursor, current rate. A backfill without progress cannot be reasoned about (Dashboards an Operator Can Act On).
- Run it as its own job with its own lifecycle, not inside the migration step and not inside a deploy.
- Give it a stop switch that does not require a deploy, and test that the stop switch works before you need it (Feature Flags: Deploy Is Not Release).
- Verify with a completeness query that scans, at the end. Sampling tells you the common case is fine; the rows that were missed are by definition not the common case.
How much can this affect
Every production change has a blast radius. Stated as a scale so it is comparable between changes rather than adjectival — and paired with what actually contains it, because a wide scope with a real containment mechanism is a different situation from a wide scope with none.
The throttle and the batch size, which together bound the load a backfill can impose. Real containment, and the only one in this module that works continuously rather than at a decision point.
What can go wrong
- A batch size chosen small enough to be safe and small enough that the run will not finish this quarter — which leads someone to raise it during the run, unthrottled.
- Throttling on a signal that lags the problem, so the backfill has already saturated the disk by the time the throttle notices (Saturation: The Reading Utilization Cannot Give You).
- Retrying a failed batch forever against a row that will always fail — a poison row — so the job spins and the cursor never advances (Dead Letter Queues Are an Operation).
- The backfill and the dual-write deploy racing: rows written between the backfill reading and writing them, in a formulation that overwrites rather than fills gaps.
- A backfill that runs from a machine someone's laptop is connected to, killed by a network blip with no record of where it got to (Manual Production Changes).
- The mitigation failing: idempotency that holds for the write but not for a side effect — a backfill that also emits an event per row and therefore duplicates events on retry (Job Idempotency).
- Completing successfully against the primary while a replica silently fell far enough behind that read traffic saw an inconsistent view for hours.
- "It is just an UPDATE." It is a sustained write workload against production, sized by your largest table, running while everything else continues.
- "Batching makes it safe." Batching makes each unit small. A tight loop of small batches with no throttle saturates a disk just as effectively as one big statement.
- "
LIMITandOFFSETis fine for pagination, so it is fine here." AnOFFSETscan re-reads the skipped rows every batch, so the job gets quadratically slower and its last batches are the most expensive (Pagination That Survives a Large Table). - "We can verify by sampling." Sampling finds systematic gaps and misses the ones caused by a single code path or a single time window — which is what actually goes wrong.
- "It finished, so the data is right." Finished means the predicate stopped matching. Whether the value it wrote is correct is a separate question with a separate check.
Operating it
Evidence is the signal, not the intention. Rollback is sometimes 'you cannot, and that is the point'.
- A remaining-rows count that decreases monotonically and reaches zero, from a query that scans rather than estimates.
- Replication lag inside its normal band throughout, with visible throttle activity if it approached the threshold — evidence the throttle works, not just that it exists.
- Service error rate and p99 latency unchanged against the pre-backfill baseline for the whole run.
- A consistency check comparing the backfilled value against the source for a sample of rows spanning the full key range, including rows written during the run.
- The job's own record: start, end, batches processed, batches retried, and the final cursor.
- A backfill that only fills nulls is trivially abandonable: stop it, and the column is partially populated, which is exactly the state it was in before you started.
- A backfill that overwrites existing values is a data change with no inverse unless you kept the original — which is an argument for writing to a new column rather than mutating an old one.
- Rolling back the application during a backfill is safe if dual writes were already deployed and the rollback target also dual-writes. Rolling back past the dual-write release silently stops covering new rows.
- If the backfill wrote something wrong, the recovery is a second backfill computing the correct value, not a restore — a restore would discard every legitimate write since it started (Partial and Logical Data Recovery).
- Automate the whole loop: batching, cursor persistence, throttle, retry with backoff, progress metrics, and completion verification. This is the archetypal case for automation — repetitive, well understood, and terrible when done by hand (Toil).
- Automate the guard: a job that refuses to start if replication lag is already elevated, or if another backfill is running.
- Keep human: starting it, choosing when it runs relative to peak traffic, and the decision to raise the batch size mid-run.
- A throttled backfill is much slower than an unthrottled one, and on a very large table that is the difference between hours and days of an unfinished migration.
- Batching means many small transactions instead of one, which is more total overhead and, on PostgreSQL, more row versions to vacuum.
- Writing to a new column rather than mutating in place doubles the storage for that field during the window, in exchange for a real rollback.
- The job infrastructure is real work for what looks like a one-off task — and it is a one-off task roughly once, after which every migration wants it.
Where this applies
This domain is unusually tool- and organisation-dependent. These labels say what each claim is specific to, and what a different platform, provider or organisation does instead.
- DATABASE-SPECIFICThe cost profile of a mass update differs by engine. PostgreSQL's MVCC writes a new row version per update, so a backfill bloats the table and its indexes until autovacuum catches up, and a long-open transaction prevents that cleanup. InnoDB updates in place and accumulates undo log instead, so the pressure shows up as undo growth and purge lag rather than as table bloat. Both punish long transactions; they punish them differently and the symptoms do not look alike (MVCC Internals: Version Chains and Snapshots).
- GENERALThe job properties — bounded batches, durable cursor, idempotent predicate, feedback throttle, progress, stop switch — hold for any datastore, including ones with no transactions at all.
- SCALE-SPECIFICUnder a few million rows a single statement during a quiet period is genuinely fine and the machinery here is overhead. The threshold is where the statement's duration exceeds the time you are willing to hold one transaction open.
Where the depth lives
This domain teaches delivery and operation, and hands the mechanism off to the domain that owns it.