Backfills and data migrations at scale

Rewrite a live table as many small, repeatable, resumable batches, slowed by the replicas and proved before anything reads the result.

Before you read: predict

ExamHall is filling a new column, attempts.deadline_at, on a copy of the table: 50,000 attempts, ids 1 to 50,000, every deadline_at NULL. The job runs this statement once per batch, each batch in its own transaction, with $1 set to 0, 10000, 20000, 30000 and 40000 in turn:

UPDATE attempts
SET deadline_at = started_at + duration_min * interval '1 minute'
WHERE id > $1 AND id <= $1 + 10000
  AND deadline_at IS NULL;

The worker is killed just after the batch with $1 = 20000 commits, and nothing else writes to attempts. An engineer then runs the whole job again, all five batches from $1 = 0.

How many rows do the second run's five batches change in total?

The 30-second version

A backfill rewrites existing rows while production keeps running. Run it as a job, never one statement: walk the primary key in small guarded batches, each committed alone, keep a progress record, and wait while replicas fall behind. Prove the result by recomputing it before reads switch.

Rewrite a live table as many small, repeatable, resumable batches, slowed by the replicas and proved before anything reads the result.

How it works

Step 1 of 10

One statement, one transaction. One UPDATE over the table commits all or nothing, leaving a dead version of each changed row (db-transactions-wal-mvcc).

In the kitchen

re-coding the whole register in one night, the office shut to new registrations → one big UPDATE (step 1)

Analogy: A council's electoral registration office moving every elector onto new polling districts after a boundary review, while registrations go on

At the electoral office	In the backfill
re-coding the whole register in one night, the office shut to new registrations	one big `UPDATE` (step 1)
working through the register in elector-number order, a few streets a day	key ranges, each batch committed alone (steps 2–3)
the clerk passes over any entry already carrying a new district code	the guard (step 5)
the day book, noting how far the re-coding has got	the progress record (step 7)
the clerk stops while the county's copy of the register, sent each day's changes, is more than a day behind	throttling on lag (step 8)
a second officer works each elector's district out again from the address and compares it with the code entered, before poll cards are printed from the new codes	verify, then switch (steps 9–10)
the old district codes are removed last	delete last (step 10)

In real terms: a backfill is a resumable job of small guarded batches by key, slowed by the replicas, checked by recomputing.

Breaks down when: an officer interrupted mid-street leaves part of the street re-coded; a batch killed mid-statement leaves none of its rows changed.

Contrasting case: a parish meeting's list of forty electors, re-coded in one sitting with the office shut: no traffic to protect, so one pass will do.

Examples, run for real

4.1 ExamHall EXH-4360: the deadline backfill in the migration file

EXH-4360: ExamHall's first plan filled deadline_at with one UPDATE in the deploy's migration. On a copy of 50,000 attempts it ran as one transaction. MR !4660 moves it into a job: batches of 10,000 by id range, each guarded and committed alone, then the verification query. Between batches, the script runs VACUUM as a stand-in for autovacuum.

4.1 EXH-4360 one UPDATE in the migration: 1 transaction, 50,000 rows changed · dead row versions 50,000 · table 319 -> 687 pages
4.1 MR !4660 job: 5 batches of 10,000 by id, each committed alone, VACUUM after each · dead row versions at most 10,000 · table 319 -> 432 pages · still NULL 0 · verification differences 0

The single statement left 50,000 dead row versions at once and grew the table to hold both versions of every row (step 1). The job never left more than one batch of dead versions, and VACUUM made their space reusable, so the table grew less. Verification found no difference before reads switched (steps 9–10).

Try it

Answer first, then check. Nothing is saved.

Question 1 of 7

A music-streaming service fills tracks.duration_ms from duration_s. Its copy of the table holds 45,500 tracks, ids 1 to 45,500, every duration_ms NULL. The backfill job:

Each statement commits on its own, and nothing else writes to tracks while the job runs.

When the loop stops, how many tracks still have duration_ms NULL?

for (let page = 0; ; page++) {  const { rows } = await db.query(    `SELECT id FROM tracks WHERE duration_ms IS NULL     ORDER BY id LIMIT 10000 OFFSET $1`, [page * 10000]);  if (rows.length === 0) break;  await db.query(    `UPDATE tracks SET duration_ms = duration_s * 1000     WHERE id = ANY($1)`, [rows.map((r) => r.id)]);}

Question 2 of 7

The streaming service runs its next backfill on the primary database while the analytics replica replays every change. The team plans with this model: each batch writes 10,000 rows in 2 s; the replica replays 2,500 rows a second; lag is the rows still waiting on the replica divided by 2,500, in seconds. Before each batch the job reads the lag, and while it is over 5 s it waits 1 s and reads it again. The job starts with lag 0 and runs 8 batches.

What is the highest lag the replica reaches?

Question 3 of 7

The streaming service's backfill of tracks.duration_ms (45,000 tracks) finished last night. Since then, a support script ran

UPDATE tracks SET duration_s = duration_s + 1 WHERE album_id = 77;

which changed 23 tracks. 40 tracks still have duration_ms NULL while their duration_s holds a value, and 12 tracks have both duration_s and duration_ms NULL; none of these NULL tracks is in album 77. Every other track's duration_ms equals its duration_s × 1000. The verification job runs:

SELECT count(*) FROM tracks
WHERE duration_ms IS DISTINCT FROM duration_s * 1000;

What count does it return?

Question 4 of 7

An interviewer asks: "Your backfill of tracks.duration_ms (45,000 tracks, batches of 5,000 by id range, each batch's UPDATE guarded with AND duration_ms IS NULL and committed on its own) crashed at 60%. How does it carry on?" Three candidates answer; in each answer the progress write is a transaction of its own. The interviewer follows up:

If the worker can die between any two statements, or in the middle of a batch's UPDATE, and wherever it dies it is restarted once and runs until it stops, which answer still leaves no track with duration_ms NULL?

Question 5 of 7

The streaming service fills a second column, tracks.loudness_db, on its 45,000-track copy, every loudness_db NULL. Each batch runs:

The job repeats the batch until its SELECT returns no rows. Every SELECT runs as an index scan on tracks_pkey in id order, checking loudness_db IS NULL on each row it reads.

Over the whole job, how many rows do the SELECTs read and throw away?

const { rows } = await db.query(  `SELECT id FROM tracks WHERE loudness_db IS NULL   ORDER BY id LIMIT 5000`);await db.query(  `UPDATE tracks SET loudness_db = -14   WHERE id = ANY($1)`, [rows.map((r) => r.id)]);

Question 6 of 7

Last week a ticketing service ran:

ALTER TABLE orders ADD COLUMN fee_cents bigint DEFAULT 0;

For an hour afterwards, pods still on the old release inserted orders without naming fee_cents; the new release names fee_cents in every insert. The table now holds 40,000 orders, ids 1 to 40,000: 39,000 from before the ALTER, 880 inserted by the old release, 120 by the new one. The backfill runs, in its own transaction for each $1 from 0 to 30000 in steps of 10000:

UPDATE orders SET fee_cents = round(total_cents * 0.04)
WHERE id > $1 AND id <= $1 + 10000 AND fee_cents IS NULL;

How many orders does the backfill change?

Question 7 of 7

A payments ledger converts entries.amount from euros to cents in place. Its copy holds 50,000 entries, ids 1 to 50,000. Each batch runs, in its own transaction, with $1 from 0 to 40000 in steps of 10000:

UPDATE entries SET amount = amount * 100
WHERE id > $1 AND id <= $1 + 10000;

After every second batch, the job saves the last id done to backfill_progress in another transaction. The worker is killed just after the fourth batch commits, before anything else runs. On restart it continues after the saved id and runs to the end.

How many entries end up with an amount other than their original euros × 100?

Go deeper

Pro tips

Make each batch idempotent with a guard in the WHERE clause (WHERE new_col IS NULL), so a crashed or duplicated run simply continues instead of applying twice. When: writing any backfill (step 5). Verify (RAN, tips.mjs): MR !4660's job run to its end, then run again in full.

tip 1 MR !4660's job run to its end, then all 5 batches again: the second run changes 0 rows

Expect a mass update to leave dead rows behind, and let VACUUM keep up between waves. When: planning a backfill on a large table (step 1). Verify: "Plain VACUUM may not be satisfactory when a table contains large numbers of dead row versions as a result of massive update or delete activity." Status: CITED, not run as a tip.

Source

Stripe engineering blog, "Online migrations at scale" (the row's first source): https://stripe.com/blog/online-migrations · fetched 2026-10-15 with curl. "There’s a common 4 step dual writing pattern that people often use to do large online migrations like this." and "Removing old data that relies on the outdated data model." (step 10); "The most expensive part of backfilling the new table on the live database is simply finding all the objects that need migration." (steps 3–4); "Once the migration is complete, run the Scalding job once again to make sure there are no existing subscriptions missing from the Subscriptions table." and "Scientist is a Ruby library that allows you to run experiments and compare the results of two different code paths" (step 9). Valid as of 2026-10-15.
PostgreSQL 17 and 18 docs, "Routine Vacuuming" (the row's second source): https://www.postgresql.org/docs/17/routine-vacuuming.html and https://www.postgresql.org/docs/18/routine-vacuuming.html · fetched 2026-10-15. "In PostgreSQL, an UPDATE or DELETE of a row does not immediately remove the old version of the row." and "The standard form of VACUUM removes dead row versions in tables and indexes and marks the space available for future reuse."; "Plain VACUUM may not be satisfactory when a table contains large numbers of dead row versions as a result of massive update or delete activity." (tip 2). Each passage is present in both versions (quote-check.mjs). Valid as of 2026-10-15.
PostgreSQL 17 and 18 docs, "ALTER TABLE" (supporting step 6): https://www.postgresql.org/docs/17/sql-altertable.html and https://www.postgresql.org/docs/18/sql-altertable.html · fetched 2026-10-15. Valid as of 2026-10-15.
PostgreSQL 17 and 18 docs, "Comparison Functions and Operators" (supporting step 9): https://www.postgresql.org/docs/17/functions-comparison.html and https://www.postgresql.org/docs/18/functions-comparison.html · fetched 2026-10-15. Valid as of 2026-10-15.

Checked against the tools and docs as of 2026-10-15.

Predict first