Backfilling a New Store While the Old One Still Takes Writes

A new store starts empty. The legacy store holds years of rows and keeps receiving writes for as long as the migration runs. Copying the history is straightforward in principle and awkward in practice, because the copy takes time and the source does not hold still: rows copied early may be updated or deleted before the copy finishes, and rows inserted during the copy may never be seen.

There are two ways to handle that. Stop writes, copy, restart: simple, correct, and usually unacceptable. Or copy while writes continue and make the copy idempotent and version-aware, then repair the differences. The second approach is the one that ships, and its quality depends on three decisions: where the copy starts, how concurrent changes are captured, and how the result is verified.

Starting from a consistent point

A copy must begin at some defined point in the source’s history. Without one, there is no way to distinguish a row that was missed from a row that was created after the copy passed its key range.

Two starting points are practical. The first is a snapshot: read the source inside a transaction with snapshot isolation, so every batch sees the same consistent view regardless of concurrent commits. The snapshot can be held for the duration only at a cost, because an open transaction prevents vacuum from reclaiming dead row versions in the entire database, and the transaction’s identifier pins the visibility horizon. On a large, busy source, holding one for hours is a deliberate operational decision, not a detail.

The second is a log position: record the write-ahead log location, binary log coordinate, or change-stream offset at the start, copy the data as of that position, and then apply changes from that position onward. This is the standard approach when change data capture is already running, and it composes better because the stream that feeds the live replica also feeds the repair.

Either way the copy needs a watermark per range, so that after the copy a verifier can ask “which rows were written after this range was copied” and expect the stream to have delivered them.

Copying by key range, not by offset

Paging with OFFSET is the default mistake. Each page re-scans and discards the preceding rows, so the copy cost grows quadratically with table size, and concurrent inserts shift the offset window, which causes rows to be skipped or duplicated. Keyset pagination avoids both:

-- One batch: read the next N rows after the last key seen.
SELECT id, version, balance_minor, updated_at
  FROM account
 WHERE id > $1
 ORDER BY id
 LIMIT 1000;

-- Apply a batch into the destination, ignoring anything already newer.
INSERT INTO account_new (id, version, balance_minor, updated_at)
SELECT *
  FROM unnest($1::bigint[], $2::bigint[], $3::bigint[], $4::timestamptz[])
ON CONFLICT (id) DO UPDATE
   SET version       = EXCLUDED.version,
       balance_minor = EXCLUDED.balance_minor,
       updated_at    = EXCLUDED.updated_at
 WHERE account_new.version < EXCLUDED.version;

Two properties matter in the second statement. The comparison uses a per-row version that the source increments on every update, not a timestamp, because timestamps collide at the resolution of the clock and are not monotonic across nodes. And the conflict clause updates only when the incoming version is newer, so replaying a batch or racing with the live stream leaves the newer value in place. Copying in primary-key order also makes progress resumable: the last key written down is the restart point.

The batch size is a throughput-versus-latency choice. Large batches amortise round trips but hold locks and generate large transactions on the destination; small batches are slow and make the copy visible as a long tail of load. The right size depends on row width and index count, and it should be a parameter rather than a constant.

Progress needs to be observable while the copy runs, and the useful observations are the watermark, the rows copied per second, and the estimated remaining ranges. Without the watermark, a resumed copy has to guess where it stopped; without the rate, a copy that slows to a crawl looks identical to one that is nearly done. The estimate is easy to get wrong in both directions, because rows are not uniformly distributed across key ranges, so the remaining work is better estimated from the number of ranges left than from the count of rows.

Throttling belongs in the tool rather than in the operator’s judgement. A copy paced by sleeping between batches is fragile under load spikes; a copy that reads a configured concurrency and a target rate, and backs off when the source’s replication lag or CPU rises, degrades instead of competing with live queries. The feedback signal must be measured on the source, because the copy’s own latency is a poor proxy. Retry behaviour also needs a bound: a batch that fails because of a lock should wait and retry, while a batch that fails because of a constraint violation should stop and report the key, or the copy will retry the same impossible row indefinitely.

The rows the copy cannot see

Three classes of row are systematically missed by a naive scan.

Rows inserted with a key below the current watermark. The copy has already passed that key range, so the new row is never scanned. The live change stream covers this only if it is running and if its events are applied with the same version guard.

Rows deleted during the copy. If the copy read them before the delete, the destination now contains a row the source does not have. Detecting this requires either a tombstone in the change stream or a reconciliation pass that compares existence, not just values.

Rows modified after the copy read them. These are the common case and are handled by the version guard, provided the change stream delivers the update. If the stream lags or drops events, the destination holds an older version that looks valid.

The consequence is that a backfill is not complete when the scan ends. It is complete when the scan has ended and the change stream has caught up to the watermark, which is a condition that can be measured: the stream’s applied position versus the source’s current position.

What reconciliation can prove

Verification compares source and destination and reports differences. It comes in levels of cost and strength.

The cheapest check is counts per key range. It catches missing and extra rows but not wrong values, and it is nearly free when both sides have an index on the key.

The next level is aggregates per range: sum of each numeric column, count of nulls, minimum and maximum of a date column. This catches most value drift at a cost proportional to rows scanned and a small constant per row. Summing money columns in a fixed integer representation is exact; summing floating-point columns is not, and the comparison must account for the order of accumulation or use a decimal type.

The strongest level is a hash over the sorted rows of each range, computed identically on both sides. This detects any difference but costs a full read of both stores, and it is sensitive to representation: whitespace, timestamp precision, and collation must be normalised before hashing, or every comparison reports differences that are not real.

None of these proves correctness during concurrent writes. A running reconciliation sees the source at time T and the destination at time T+δ, so any row modified in between differs legitimately. The practical approach is to reconcile a range only after the change stream has advanced past that range’s copy watermark, and to re-check differences that persist after allowing the stream to catch up. A difference that survives two passes is real.

Operational hazards

Copy load on the source. A full scan reads every row and evicts the buffer cache that serves production queries. Copying from a replica removes that cost and introduces a different one: the replica’s snapshot lags the primary, so the watermark must be expressed in the primary’s log coordinates, not the replica’s clock.

Sequence collisions. If the destination generates identifiers of its own, it can issue values that the source later assigns to a different row. Copying explicit identifiers and advancing the destination’s sequence past the maximum is required, and it must be repeated whenever the source’s range grows.

Constraint and trigger side effects. Loading rows into a table with triggers fires them, which can send notifications, update audit tables, or enqueue outbound messages for historical events. Bulk loading usually needs triggers disabled or the load performed into a staging table, with the switch to the live table done separately.

Storage and index cost. The destination’s indexes are maintained during the load. Building them after the copy is faster for a large table, but it means the verification pass runs without the indexes it would otherwise use.

When the copy has caught up and the differences have been explained, the remaining decision is which store accepts writes. That transition, and the rollback window that follows it, is covered in switching the write path. The version guard used throughout this copy is the same mechanism that makes the outbox relay idempotent, and the two designs should share it.