Operate a Production Data Backfill as a Control Loop

Control pressure instead of guessing a rate

A production backfill should repeatedly measure database pressure, adjust its request rate, checkpoint durable progress, and verify the result. A fixed batch size and a progress counter are insufficient because traffic and query cost change while the job runs. PostgreSQL monitoring statistics provide key signals for the pressure side of this control loop.

Partition work by a stable key

Choose stable, non-overlapping ranges from an immutable primary key or a captured high-water mark. Each worker claims one range, transforms rows idempotently, and commits the change plus checkpoint together. Kubernetes Jobs can retry failed Pods and track completions, but application checkpoints still matter because a restarted process cannot infer which database effects committed.

Make every batch resumable

For 80 million accounts, capture max(id) at launch and create ranges of 10,000 IDs. Update only rows whose target version is old, commit each range, and record source count, changed count, skipped count, and checksum. Newer accounts follow the normal write path and sit outside the finite backfill set. A retry of range 120 performs the same conditional update safely. Kubernetes Job behavior is useful here because the controller retries failed execution, but your application still has to define what counts as durable progress and how a retried worker resumes from the last committed checkpoint, as described in the Kubernetes Job documentation.

Let saturation close the loop

Sample signals such as current activity, replication status, and table statistics before every batch. PostgreSQL’s monitoring views include pg_stat_activity, pg_stat_replication, and table statistics, and the same documentation notes that cumulative statistics do not update instantaneously while current-query information remains up to date. Increase concurrency slowly after several healthy windows; cut it quickly when any guardrail crosses its threshold. Pause completely when replication status or customer-facing latency exceeds the agreed budget. Google’s SRE guidance on handling overload explains why throttling should protect the serving system rather than maximize background throughput, and why static queries-per-second targets are often a poor proxy for real resource cost.

Verify independently of progress

A checkpoint proves attempted work, not correctness. Verification queries should run through a separate path and compare the captured population with rows at the desired version. Sample transformed values, reconcile counts per range, and retain failed identifiers in a bounded dead-letter table. Define how deletes and concurrent edits interact with the transform before launch.

The completion contract

Before implementation, write down these decisions: stable population boundary; idempotent update predicate; atomic checkpoint rule; pressure signals and thresholds; minimum and maximum concurrency; verification query and tolerated discrepancy; owner of failed records. Turn each decision into an automated assertion or a rehearsal step. A design that exists only in prose will drift as callers, operators, and dependencies change. Record the owner and the signal that proves the control still works.

Make the acceptance test concrete

For one captured key range, record the source count, target version, update predicate, and durable checkpoint. Stop the worker before and after commit, restart it, and replay the completed range. Reconcile the result through the independent verification query, then repeat while a row changes concurrently and while database pressure triggers throttling. Record skipped and failed identifiers so the operator can distinguish completed work from work needing investigation.

Run one bounded slice

Execute a one-percent canary over representative key ranges. Stop, restart, and replay a completed range; only expand when counts reconcile and foreground database objectives remain healthy. Revisit the rollout decision whenever the protocol, dependency behavior, traffic shape, or threat model changes.

Record the durable checkpoint, batch boundaries, database pressure, throttle adjustments, and reconciliation counts for the backfill. Keep one stopped or retried batch in the evidence so an operator can identify when to resume and when to investigate missing progress.