You need to populate a newly added column for roughly 500 million existing rows while the system stays fully online. How do you design, run and supervise that backfill?

## Why a single statement is wrong `UPDATE t SET new_col = f(old_col)` over half a billion rows fails in several ways at once. It runs as one transaction, so it holds row locks for its entire duration, blocking conflicting writers. It generates undo or old row versions for every row, which bloats storage and, under MVCC, holds back garbage collection so unrelated tables grow too. It produces a single vast burst of replication traffic, pushing followers into lag. Its plan is a full table scan whose intermediate state is invisible. And if it fails or is cancelled near the end, the engine rolls the whole thing back — often taking as long again — leaving zero progress. ## Batch selection Order the work by the primary key and advance a cursor: ``` WHERE id > :last_id ORDER BY id LIMIT :batch ``` This is **keyset pagination**. Never use `OFFSET`, whose cost rises linearly as the job advances, so the last batches are the slowest. If the key is a UUID or otherwise unordered, iterate over its ranges anyway, or over a partition or time column that has an index. Batch size is a tuning knob, not a constant. Start small — 1,000 to 5,000 rows — measure the duration of a batch, and target something short, say under a second, so no batch holds locks long and cancelling is immediate. Adapt: shrink when latency or lag rises, grow when the system is idle. ## Idempotence and resumability Assume the job will be killed, redeployed, or run twice concurrently by accident. Two properties defend against that: - **Idempotent batches.** Guard with `AND new_col IS NULL`, or compute a value that is stable for the same input. Re-running then costs a scan and changes nothing. This guard is also what prevents the backfill from overwriting a fresher value written by live dual-write traffic — a real bug when the backfill reads a row, the application updates it, and the backfill then writes a stale derived value back. - **Persisted cursor.** Store the last completed key in a table so a restart resumes. Storing it in memory or in a shell variable means a restart begins again from zero. If the two could genuinely race, do the read and the write in one statement, or re-check the source value in the `WHERE` clause so a concurrent change causes the batch row to be skipped rather than overwritten. ## Throttling — the part that makes it invisible Between batches, sample the health signals that the backfill can damage: - **Replication lag.** The single most important one. Backfills write heavily and lag is the first casualty; pause above a threshold and resume below it. - **Lock waits and long transactions.** If the batch is queueing, back off. - **Application latency and error rate.** If the service degrades, stop. A fixed sleep is a crude version of this and better than nothing; feedback on measured lag is much better, because load varies through the day. ## Bloat and vacuum pressure An `UPDATE` in an MVCC engine writes a new row version and leaves the old one behind, so backfilling a whole table can double its physical size and every index on it before cleanup catches up. Batching with frequent commits lets garbage collection reclaim space as you go, but the job must run slowly enough for it to keep up. Watch table and index size during the run, not only afterwards. ## Operating it Run it as a supervised job — a script or a service task — not as a one-off statement in a console session that dies with the SSH connection. Give it: - structured progress logging and a metric for rows/second, remaining rows and estimated completion; - a kill switch (a flag or a row in a control table) so it can be stopped without a deploy; - a bounded runtime per invocation so it can be scheduled into quiet periods and picked up later. Expect it to take days on 500 million rows if it is throttled properly. That is the correct outcome, not a failure: the point is that nobody notices. ## Finishing Completion is a measured claim, not an assumption. Count remaining rows where the new column is still unset, and re-run the sweep once more after the count reaches zero to catch anything written by a path that was missed. Only then tighten constraints — `NOT NULL`, checks — since those assertions are only valid once every row and every writer complies. And keep the verification query around while dual write continues, so a straggler writer shows up as a non-zero count rather than as a constraint violation later. ## When it is not worth it On a table of a few million rows on a quiet system, a single throttled statement during a low-traffic period may be entirely fine. The batching machinery is proportional to risk: table size, write volume, and how much the business notices a latency spike.