Database migration safety
Backfill without overwriting a newer edit
A migration worker and a live request can touch the same row. Make the update conditional on the state the worker actually read.
In this article
Define the conversion before running it
Suppose a service is replacing a free-text account code with a normalised value. Write a deterministic conversion and decide what happens to invalid or ambiguous inputs. Do not silently invent a valid code for a record that needs review.
Add the destination field and deploy the live writer behaviour before starting the historical backfill. New and changed records should follow the intended transition policy while older rows are processed.
Track migration state separately from a legitimate empty result. Using null to mean both not processed and intentionally absent makes completion difficult to establish.
Read a revision with the source value
The backfill should know which row version it converted. A conditional update can refuse to replace a value when a live edit has changed that version.
UPDATE accounts
SET normalised_code = :converted_code,
code_migrated = TRUE
WHERE id = :account_id
AND row_revision = :observed_revision
AND code_migrated = FALSE;This is illustrative SQL using application-bound parameters. It assumes every relevant writer advances the revision and participates in the migration policy. Without that invariant, the predicate does not protect against all concurrent changes.
Check the affected row count. Zero rows can mean the record changed, was already processed or disappeared. Resolve that state rather than counting the attempted update as completed work.
Keep progress resumable
Process bounded batches and commit progress so an interruption does not require starting from the beginning. Use stable selection criteria. Offset pagination over a changing eligible set can skip rows as earlier records leave the set.
Record successful conversions, conflicts and rejected inputs separately. A high-water mark alone may not describe unfinished records behind it.
Apply the conversion again safely when a batch is retried. The job should recognise already completed work without overwriting a later business edit.
Verify the resulting data
Compare converted values with independently calculated expectations for representative records. Count remaining eligible rows and unresolved exceptions.
Pause and resume the worker during a test while live requests update the same records. Confirm that the final value reflects the current source and that conflicts are visible.
Only move readers exclusively to the new field once coverage and correctness meet the release conditions. A completed job process is not evidence that every row was successfully migrated.
Primary sources
PostgreSQL: transaction isolationReferences checked 11 September 2026.