Skip to content
AITroveRead. Build. Understand.
Reading list
Make this comfortable

Resumable Data Backfills and Reconciliation

Last updated: 4 Oct 20268 min read
tutorial
IntermediateBy AITrove Editorial

A backfill copies or computes a new representation for existing records after an application begins supporting it. Unlike a one-time migration in an empty environment, a live backfill competes with user reads and writes and may be interrupted midway. A checkpoint says where one worker has progressed; it does not by itself prove that rows changed concurrently are correct. The job must be idempotent, use a stable traversal key, keep each transaction bounded, and reconcile against the authoritative record before switching readers. The write path must preserve both old and new representations, or capture changes through a proven update stream, throughout the migration window.

Working case

The permit service replaces a free-text inspection duration with an integer minute field. There are 2.7 million old cases. A single update transaction locks too much and generates a large recovery log, so an operator processes 470 rows per chunk by immutable case ID. During the run, reviewer 62 edits case 47, which lies behind the current checkpoint. A high-water mark alone would miss that edit. The application writes the new field for current changes; the backfill updates only rows that still need conversion, stores its checkpoint after each committed chunk, and runs a final reconciliation before any reader stops accepting the old form.

Implementation boundary

javascript
function nextBackfillKeys(caseIds, lastCommittedCaseId, chunkLimit) {
  return caseIds.filter(caseId => caseId > lastCommittedCaseId).sort((left, right) => left - right).slice(0, chunkLimit);
}
console.log(nextBackfillKeys([91, 29, 62, 47], 29, 2).join(","));
// Output: 47,62

Deploy readers that tolerate both representations and writers that keep the new field correct before launching the historical job. Choose an immutable, indexed traversal key, record the last committed key, and fetch the next bounded batch in key order. The conversion must be repeatable: rerunning a chunk should not double a numeric value or overwrite a newer user edit. Use a conditional update or row version check so work based on an old snapshot cannot replace a concurrent change. Commit a chunk and its checkpoint together where possible; otherwise design for replay of the last chunk. Throttle based on replica lag, lock waits, and application latency. After the first pass, reconcile nulls, mismatches, and rows created or modified during the run before making the new field mandatory.

Cost and boundaries

A backfill reads O(N) records and may write O(N) rows, with additional index maintenance and recovery-log traffic. A chunk of 470 rows is an initial budget, not a universal optimum: the right size depends on row width, write rate, lock behavior, and replication capacity. More workers can shorten wall time while increasing contention and duplicate scanning. Track rows visited, rows changed, chunk time, error rate, last checkpoint, outstanding mismatches, database load, and replica lag. Do not declare completion from a counter of processed keys alone; the final invariant is data agreement for every record the new reader may use.

Failure trace

Kill the worker after writing a chunk but before its checkpoint is persisted. On restart it must repeat safely and reach the same stored value. Edit a processed case behind the high-water mark while the job continues; the write path or reconciliation must restore agreement. Create a new case below an assumed numeric range by importing a legacy ID and check that it is not skipped. Force one malformed duration and route it to a reviewable exception set rather than halting every later chunk. Raise replica lag above the agreed limit and confirm the job slows or stops without delaying foreground permit review.

Verification

  • Chunk replay is idempotent after a crash.
  • Concurrent edits behind the checkpoint reach the new representation.
  • Final reconciliation counts unresolved mismatches before reader cutover.

Practice drill

Start with cases 29, 47, 62, and 91 and a checkpoint before 47. Convert at most two rows per transaction. Crash after the first commit, replay, then edit case 29 and insert a late legacy case. Reconcile the old and new duration fields and list any row that cannot be converted without a business rule. Record an acceptance threshold for mismatches, a pause threshold for latency or lag, and the evidence required before the old field can be removed.

Decision note

A resumable checkpoint tracks progress; only write-path coverage and reconciliation prove correctness.

Common Mistakes

  • Using offset pagination while the source keeps changing.
  • Calling a high-water mark a complete correctness check.
  • Running one unbounded write transaction against a live table.

Related lessons

Database Capacity and Online Change Operations; Connection Pool Budgets and Queue Admission; Transaction Retry, Deadlock, and Side-Effect Boundaries; Online Index Builds and Query Plan Release Gates; Expand-and-Contract Schema Migrations; Replica Lag, Read-Your-Write, and Version Cursors.

Apply and check

Build Project: permit data change under live traffic and review Web Development: database online operations quiz.

web-tech
web-development
Storage details