Database backfills: checkpoint progress without racing live writes

Last updated: 5 Oct 20266 min read

tutorial

A database backfill computes or copies data for existing records after a schema change. It differs from a schema migration that merely adds a column: a large table scan can hold locks, amplify replication lag, and contend with user writes. A restartable backfill needs a stable ordering key, durable progress, and an idempotent update rule so retrying a batch does not corrupt data.

Operational decision

A parcel service adds a normalized destination code. Deploy a reader that accepts both old and new representation, then dual-write new parcels. The worker below selects a bounded ID range and updates only rows still missing the normalized value; its application transaction must persist the last completed ID after the batch commits. The SQL fragment illustrates one batch, not the checkpoint table or driver loop. Avoid a long transaction spanning the whole table. Pace batches against user latency and replication delay, and stop if either crosses its limit. Validate counts and a sample of edge-case addresses before making the new column mandatory. Keep the old reader until rollback and retained-message windows close. If live writes can change the source field during backfill, use a version guard or recheck pass rather than trusting the first scan.

UPDATE parcel_destination
SET normalized_code = UPPER(TRIM(raw_code))
WHERE parcel_id > 47000
  AND parcel_id <= 47400
  AND normalized_code IS NULL;

Cost and verification

Batch size trades fewer transactions for longer locks and larger replication bursts. A range of four hundred IDs may update far fewer than four hundred rows; measure actual writes and elapsed time. Checkpoints add state but make interruptions recoverable. A backfill can be logically correct and still harm availability through I/O pressure, so throttle on service metrics rather than a fixed sleep alone. The SQL expression is illustrative; real normalization needs validated business rules and collation behavior.

Common Mistakes

Do not hold one transaction open for the entire table.
Do not advance the checkpoint before its batch commits.
Do not make the new field required before old rows and clients are handled.

Connected lessons

Practice and check

devops

operations