How do you write a backfill or seed insert so that re-running it after an interruption is harmless and resumes?

## Two different properties They are often said in one breath, but they are distinct and you need both. | Property | Question it answers | What gives it to you | | --- | --- | --- | | **Idempotent** | If this runs twice over the same rows, is the result the same? | Absolute assignments, predicates on un-migrated state, guarded inserts | | **Resumable** | If this stops halfway, can it continue without starting over? | A stable ordering, committed progress, a predicate that describes remaining work | A run can be idempotent but not resumable: correct on a second run, but it redoes everything from the beginning each time — fine for a hundred rows, hopeless for a hundred million. It can also be resumable but not idempotent: it remembers where it stopped, but the last partly-committed unit is applied twice and the numbers are wrong. Aim for both. ## Make the predicate describe the remaining work The strongest technique is to make the query for "what still needs doing" the run's only state. If the new column is null exactly when a row has not been processed, then: ```sql UPDATE orders SET currency_code = 'EUR' WHERE currency_code IS NULL; ``` is both idempotent and self-resuming: after any interruption, re-issuing it processes precisely what is left, and issuing it against a finished table changes nothing. No progress needs to be recorded because the data itself records it. This works whenever the target state is distinguishable from the source state. When it is not — the backfilled value may legitimately be the same as the un-backfilled one, or the column is not nullable — you need an explicit marker instead: a boolean or timestamp column set as part of the same write, or a small progress table holding the highest key processed. Whatever the marker is, it must be written **in the same transaction** as the change it records, or a crash between them reintroduces the problem. ## Absolute, never relative The classic non-idempotent write is arithmetic against the current value: - `SET balance = balance + adjustment` — doubles on a second run. - `SET notes = notes || ' migrated'` — appends twice. - `SET version = version + 1` — desynchronises anything comparing versions. - Inserting a child row unconditionally — one child on the first run, two on the second. Every one of these has an absolute equivalent that is safe: derive the target value from immutable source columns, or predicate the write on not having been done. When the target genuinely is an accumulation, record what was already contributed so the run can compute the remainder rather than adding blindly. ## Guarded inserts For seed and lookup rows, the guard is a **unique constraint plus a conditional insert**: insert the row only where no row with that key exists. Two points are easy to miss. 1. The unique constraint is what makes it safe. Without it, two concurrent runs both observe "absent" and both insert; the guard is only advisory. 2. Deciding what a duplicate *is* is a modelling decision. A backfill that creates a derived row per parent must key that row on the parent, or a re-run cannot tell an already-created row from a missing one. ## Recording progress When the predicate cannot carry the state, record it explicitly: 1. Order the work by a **stable, immutable key** — a primary key or a created-at timestamp that never changes — so "everything above X" is well defined. 2. Commit as you advance rather than holding one transaction over the entire run. A single giant transaction is not resumable at all: an interruption discards everything. 3. Write the high-water mark in the same transaction as the rows it covers. 4. On restart, read the mark and continue above it; on a fresh run with no mark, start from the beginning. One caution: a high-water mark over an ordering that can change — a mutable `updated_at`, say — silently skips rows. The key must not move. ## What cannot be made idempotent Side effects outside the database are the limit. Sending a notification, publishing an event other systems consume, calling a payment interface, writing a file: a re-run repeats them, and no predicate inside the database prevents that. The standard answer is to keep them out of the data run entirely — record the intent in a row, and let a separate, deliberately deduplicated process act on it. If they must stay, they need their own persisted record of what was already sent, keyed so the check and the send cannot both be repeated. Finally, expect the re-run. A run that has never been interrupted in testing has never been tested; deliberately stopping it halfway and starting it again is the only honest check that both properties hold.