Define the relationship that must hold
A backfill has visited every row, but application writes continued during the run. The reviewer still needs to establish whether each new representation agrees with the current source value. A batch checkpoint records progress through work; accepting the reconciliation requires a comparison rule and evidence that concurrent writers preserve it.
Consider a PostgreSQL 18 configuration table that stores retry_delay_ms and will also store retry_delay_us. For this design exercise, a value is either null, meaning inherit the default, or an integer from zero through 60,000 milliseconds. The new value is the old value multiplied by 1,000, with null preserved. The largest result is 60,000,000, within PostgreSQL's integer range.
Both representations live in the same row under one globally unique configuration identity that is never reused. Milliseconds remain authoritative during the backfill. These conditions bound the example: it illustrates reconciliation within one database, without claiming a migration run or extending the result to separately replicated stores.
Make every writer preserve the relationship
Give each row a non-null source_revision that changes whenever its authoritative value changes. Revisions must not be reused, including when a value changes and later returns to its earlier value. Record derived_revision when the microsecond value is populated. A null derived_revision means the conversion has not been established, even when a null target value would be legitimate.
The proposed write contract updates the source, advances its revision, and stores the corresponding derived value and revision in one transaction. New inserts follow that contract too. PostgreSQL transactions make the committed change visible as a unit. Cover administrative updates and background jobs as well as the main application; an older writer that changes only the source can invalidate the result.
Enforce the relationship for populated rows at the database boundary where practical. PostgreSQL CHECK constraints can compare columns in the same row. Express pending rows separately and make the ready-state comparison definite: CHECK accepts a null expression result. A constraint must preserve the intended null meaning while rejecting a populated value that disagrees with its source.
Bind a backfill update to the version it read
Suppose a batch reads 200 milliseconds at source revision 7 and calculates 200,000 microseconds. Before it writes, an application transaction changes the value to 400 milliseconds at revision 8 and stores 400,000 microseconds. The batch's earlier calculation is now stale. Its write must leave the application's committed result intact.
For this design, the backfill may write only when the row identity and source revision still match what it read. PostgreSQL's Read Committed update behavior rechecks the condition after a concurrent updater commits. The revision condition makes the stale attempt in this example ineligible.
Inspect the update outcome. PostgreSQL considers an UPDATE affecting zero rows successful execution, so the runner must distinguish that outcome from a completed conversion. Re-read or account for a changed or deleted row before retiring the item. A process interruption also requires reconciliation of the stored result before advancing its checkpoint.
- Batch readsRevision 7: 200 ms
The pending calculation is 200,000 microseconds for this identity and source revision.
- Application writesRevision 8: 400 ms
One transaction stores 400 milliseconds and the matching 400,000 microseconds.
- Batch checksRevision 7 no longer matches
The conditional update must not apply the stale calculation to revision 8.
- ReconciliationInspect the current row
Account for the new revision and compare its stored value with the declared conversion.
Compare values from a common snapshot
A comparison assembled from unrelated reads can manufacture a difference. One read might retrieve the old value before an application commit and another retrieve the new value after it. PostgreSQL Read Committed gives each ordinary SELECT its own snapshot, so putting those reads in one transaction does not make their snapshots identical.
For this table, one ordinary SELECT can inspect both representations and revisions together. If an audit needs several queries over one stable view, a read-only Repeatable Read transaction supplies a consistent snapshot for those queries. Keep its scope explicit and assess its operating cost separately.
A series of batches using different snapshots establishes what each batch observed. It does not create a single point-in-time result for the whole table. Name that limitation in the report, retain the coverage boundaries, and require the ongoing writer contract to remain valid after a row has been checked.
Separate an inherited default from unfinished work
Compare the expected and actual microsecond values with null-aware equality. PostgreSQL's IS NOT DISTINCT FROM treats two nulls as equal and a null against a value as different. Ordinary equality can return unknown when null is involved, so a check that counts only false comparisons can miss a disagreement.
A row with null source and target values is matched only when its derived revision is present and equals the source revision. With no derived revision, the same pair of null values is still pending. A non-null derived revision that differs from the source revision is a revision mismatch, regardless of whether the numbers happen to match.
Give each audited row one disposition: invalid source, pending conversion, revision mismatch, value mismatch, or matched. Record identity and revision for exceptions, with enough retained evidence to reproduce the comparison. Total these dispositions against the selected scope. Repair from the authoritative source under the same concurrency rule, then inspect the repaired state again.
Accept a reconciliation report with stated limits
Retain the conversion rule, source scope, snapshot or batch boundaries, and exception counts alongside the backfill checkpoint. A clean result needs complete coverage of that declared scope. Sampling can help investigate a defect; a sampled pass cannot establish that every row agrees.
Before accepting the result, examine concurrent updates, null values, interrupted batches, and deletions in an environment your team controls. Verify the real writer and runner paths. The arithmetic in this example can be checked locally, but it supplies no evidence that a database implementation honors the revision contract.
The assessment decision is whether agreement has been established for the stated scope and can be maintained by ongoing writes. Unresolved rows or an uncovered writer leave that decision open. Keep the source authoritative while the team closes those gaps.