Backfills that do not fight production

Define what completion means before scanning

Suppose a hypothetical orders table needs a new normalised country field. The easy specification is to visit every row. The useful specification is to ensure every eligible order has a value computed by a named transformation version, without overwriting a newer application decision. Those definitions diverge as soon as live writes continue. An order can change after the worker reads it, a new order can arrive behind the scanning cursor, and a transformation can fail for malformed historical data. A row count by itself cannot distinguish those outcomes.

I would record a migration identifier, transformation version, inclusion rule, starting boundary and completion evidence. For an immutable identifier scan, capture an upper identifier limit and make the application handle newly created records with the new behaviour. For mutable inputs, introduce a source revision and require the backfill write to match the revision it read. Completion then means that the bounded population is either transformed at the expected revision or explicitly recorded for reconciliation. This definition makes skipped races visible rather than treating them as successful progress.

Choose a consistent cut deliberately

PostgreSQL's isolation documentation distinguishes statement snapshots at Read Committed from the stable transaction snapshot used by Repeatable Read. That distinction matters when a migration joins several tables: a sequence of independent reads can observe different committed states. A long snapshot offers a coherent view but has operational costs. The vacuum documentation explains that obsolete row versions must remain when a transaction could still need them. I would therefore treat a long-lived snapshot as a resource commitment, not as a free correctness switch.

An alternative is a snapshot plus a change stream, provided the handoff has an explicit position that prevents a gap. Another is an application-specific migration that tolerates different read times because each row is independently versioned. These choices solve different problems. A country normalisation backfill may only require per-row consistency, while rebuilding a cross-table accounting view may require a coordinated cut. I would choose the weaker, cheaper condition only after stating why inter-row skew cannot violate the result. Otherwise, reducing snapshot duration simply moves the inconsistency into the output.

References: [1] PostgreSQL: Transaction Isolation[2] PostgreSQL: Routine Vacuuming

Checkpoint committed work, not attempted work

Use keyset pagination over a stable ordering rather than repeatedly asking the database to skip an ever-growing offset. In an illustrative batch, read rows with identifiers greater than the checkpoint and no greater than the initial upper bound. Transform them, perform conditional writes, and advance the checkpoint only after those writes commit. If the process dies before the commit, replay the batch. If it dies after the commit but before recording a checkpoint in a separate store, replay is still possible, so the writes must remain safe to repeat.

Putting progress and output in the same database transaction can make that boundary straightforward. When the destination is another system, progress requires a different protocol; a local cursor cannot atomically certify a remote write. There is also a subtle parallelism trap. If batches covering identifiers 100 through 199 and 200 through 299 finish out of order, the global checkpoint cannot advance to 299 while the lower range remains incomplete. Store completed ranges or maintain a contiguous committed frontier. The largest identifier any worker has touched is not a safe restart position.

UPDATE orders
SET country_normalized = $1,
    normalization_version = $2
WHERE id = $3
  AND source_revision = $4
  AND (normalization_version IS NULL OR normalization_version < $2);
-- A zero-row result requires classification or reconciliation.

Budget the bottleneck that actually hurts users

A fixed worker count is only an indirect load control. Four workers can be harmless on cached reads and destructive on a cold scan that competes for storage bandwidth. I would constrain outstanding requests, batch size and sustained throughput separately. The feedback signals should include application tail latency, database lock waits, replication delay and storage pressure, with explicit stop thresholds. CPU utilisation alone misses a backfill that holds row locks or creates enough write amplification to slow unrelated transactions even while processors appear comfortably idle.

For a worked estimate, assume a test establishes 800 safe update operations per second under the desired production latency limit. Live traffic currently consumes 500, and the operator reserves another 100 for bursts. The provisional backfill budget is 200 updates per second. For 12 million rows, the optimistic runtime is 60,000 seconds, or about 16.7 hours, before pauses and retries. These are hypothetical inputs, not a benchmark. If each row also updates three indexes, measuring a row rate without its associated storage cost can make even this cautious estimate misleading.

Use feedback without creating an oscillator

An adaptive controller should reduce pressure quickly and increase it gradually. If production latency breaches its threshold, halve the backfill budget or pause; after several healthy intervals, raise the budget by a small fixed amount. A cooldown prevents every worker from reacting to the same transient sample at once. I would centralise the budget or distribute a shared quota rather than let each worker independently decide that the whole remaining capacity belongs to it. Otherwise a sensible local rule becomes an aggressive global one.

The measurement window matters. A one-second latency spike should not necessarily suspend a day-long migration, while an average across thirty minutes can hide an immediate customer problem. Use an operational policy that distinguishes transient congestion from sustained degradation and separately handles hard limits such as low free disk space. Keep checkpoints durable during pauses, and expose the reason the job is waiting. A progress display that only shows elapsed time encourages operators to override the controls precisely when the controls are protecting the live service.

A replica is isolation with a new failure boundary

Reading from a replica can move scan pressure away from the primary, but it does not automatically solve correctness or resource contention. The replica may lag behind live writes, share underlying storage limits, or be needed for failover. If its result drives updates on the primary, version checks still matter. A logical replication slot also creates retention obligations: PostgreSQL documents that a slot can retain resources needed by a lagging consumer. A paused migration must therefore have a plan for its retained log, not merely a stopped worker process.

The strongest counterargument to all this machinery is that a small table can be migrated with one ordinary statement during a quiet period. That is often right. A resumable worker fleet is not inherently safer than a short, well-understood transaction. My threshold is the ability to predict and bound the operation. If a representative test shows the work fits comfortably inside the maintenance and recovery budget, keep it simple. If the duration spans traffic cycles or the dataset cannot be retried cheaply, invest in controlled progress rather than optimistic scheduling.

References: [3] PostgreSQL: Logical Decoding Concepts

Finish with an invariant, not a celebratory counter

After the scan reaches its boundary, run a reconciliation query for eligible rows that still have the old transformation version. Classify each exception: concurrent change, invalid input, deleted source, or a worker failure. Where practical, compare independent aggregates or partitioned checksums between source and destination using a stable representation. Sampling can find broad mistakes cheaply, but it cannot prove that a rare missing category is absent. The final validation should target the migration's actual invariant and include the edge cases that the transformation was designed to handle.

I would also test the stopping procedure before the full run. Kill a worker between reading and writing, restart from a checkpoint, deliver the same batch twice, and change an input while its batch is pending. Verify that these actions produce retries or classified exceptions rather than silent corruption. Keep rollback behaviour concrete: reverting a deployment may not reverse data already transformed. A good backfill leaves a record of what changed, where progress is safe to resume, and what evidence supports completion while ordinary production work continues.