Backfilling a Table While Its Stream Keeps Running

A table gains a column that the pipeline never captured, a transformation turns out to have been wrong for a year, or a sink is rebuilt after a corruption. In each case the fix is to re-read history: a backfill. The complication is that the table is also being written to, so the live stream is delivering changes for the same keys the backfill is reading, and both arrive at the sink.

The backfill is not a query problem. Reading a year of rows is straightforward. The problem is that the sink now has two writers with different notions of what the correct value for a key is, and one of them is describing the past.

Two writers, one key

The backfill reads row 4711 as it exists at the moment the backfill’s scan passes it, say with total_cents = 129900. The live stream delivers an update to row 4711 at the same time, setting total_cents = 134900. If the stream’s change lands first and the backfill’s older value lands second, the sink ends up with the stale value, and it stays stale until the next update to that row, which may never come.

This is a lost update in reverse: not a concurrent write overwriting another, but a historical read overwriting a current one. The backfill’s value is not wrong at the time it was read; it is wrong by the time it is written.

Three mechanisms prevent it, and each has a different cost.

Ordering by a version that the sink understands. If every record carries a monotonic version, whether a log position, a commit sequence, or an update counter, the sink can reject any write whose version is older than the stored one. The backfill’s record carries the version of the row as read, and the stream’s record carries a later version. The sink’s conditional upsert, WHERE stored_version < incoming_version, resolves the race without coordination. This requires the source to have such a version, which is exactly the property cursor columns found lacking in application timestamps.

Serialising the two writers. If the backfill and the stream write to the same key range under a lock or a single-writer discipline, the race cannot occur. The cost is throughput: a lock per key across a year of history serialises the backfill against the live traffic on every row that traffic touches, and the live traffic is the one that cannot wait.

Separating the key spaces. If the backfill writes to a staging table and the merge happens afterwards in one pass, there is no race at the sink, but there is a race at the merge. The merge must know, for each key, which of the two records is newer, which returns to the version question.

Every correct design reduces to the version comparison. The designs differ in where the comparison happens and how much work it costs.

The chunking decides how much overlaps

A backfill of a year of rows is executed in chunks, by key range or by time range. The chunk size sets the window during which a key is read but not yet written, and therefore the window in which a stream change can arrive.

A chunk of 100,000 rows read over ten minutes has a ten-minute window per chunk. A stream change to a row in that chunk that arrives after the read and before the write will be either overwritten by the write (if the sink has no version check) or correctly ordered (if it does). The window’s length does not change the outcome when the version check is present; it changes the probability of the race occurring and the volume of duplicated work.

Smaller chunks shorten the window and multiply the number of position records and commits. Larger chunks reduce overhead and increase the replay volume after a failure, since a failed chunk is re-read entirely.

The chunk boundary also interacts with the source’s consistency. A chunk read under read-committed is not a snapshot, so a row modified during the chunk’s scan appears in the chunk in its new form while the stream also delivers that modification. With a version check, the duplicate is harmless. Without one, the sink receives two writes for one change and one of them is redundant but consistent, since the value is the same and both read the post-update state. The dangerous case remains a modification that arrives at the sink before the chunk read reaches that row.

Rate limits are a correctness parameter

The backfill competes with the live traffic for the source’s I/O and for the sink’s write capacity. On the source, a full scan over a year of rows evicts the buffer cache that the live workload depends on, so the live queries slow down. On the sink, the backfill’s writes interleave with the stream’s, and the sink’s own compaction or merge work grows.

Throttling the backfill by rows per second is the usual control, and the useful quantity is not the raw rate but the rate relative to what the source can absorb without changing the live workload’s latency. Since that is not measurable from inside the backfill, the practical approach is a configured rate with the ability to reduce it, plus a check on the source’s replication lag or query latency rather than on the backfill’s own progress.

On a replicated source, the backfill reads the replica, which protects the primary from the scan’s I/O but adds a different constraint: the replica’s apply lag. A backfill that reads a lagging replica reads rows that are older than the log position the stream is consuming, so the version attached to a backfilled row can be older than a stream change the sink has already applied, which is exactly what the version check is for. It also means the backfill’s “as of” point is not a single instant, and the chunk positions must be recorded against the replica’s own position, not the primary’s.

Backfill as a replay of the log

When the source retains enough log, the backfill does not need to query tables at all. Replaying the log from a position a year back reconstructs the history with exact commit order and with the same identity per change that the live stream uses, which removes the version-comparison problem: the replayed records carry their original positions, so the sink’s ordering is the same ordering the live stream uses.

The limits are the retention window and the cost of decoding. Log retention of a year is unusual; retention measured in days or weeks is typical, and the disk cost scales with write volume. Where the retention exists, replay is strictly better than a table scan: it needs no snapshot isolation, it produces deletes, and its records are ordered.

Where it does not, the practical alternative is a snapshot of the table at a known position, the same snapshot-to-stream handoff used to bootstrap — with the stream positioned at that boundary and the backfill’s writes stamped with the boundary’s position. Rows changed after the boundary are then corrected by the stream, and rows unchanged are written once.

The transformation change is the harder backfill

A backfill that adds a column is a copy. A backfill that changes a transformation is a recomputation, and it has an additional problem: the old and new versions of the derived data coexist while the backfill runs.

If the sink holds one row per key, the backfill overwrites the old derived value with the new one, key by key, so the table is a mixture of old and new logic for the duration. Any consumer reading it during the backfill gets inconsistent results: some rows computed under the old rule, some under the new. Whether that is acceptable depends on the consumer; a report that sums a column computed under two different rules is wrong in a way that is hard to attribute.

The alternative is to write the new version to a separate table or partition, and switch readers when it is complete. That doubles the storage for the duration and requires the switch to be atomic from the readers’ perspective, which usually means a view or a table rename rather than a data copy.

There is no way to make a recomputation atomic across a large table without either a full copy or a versioned read path. The choice is between inconsistent intermediate states and doubled storage.

Detecting the keys the backfill missed

A backfill driven by a key range covers the keys that exist at the time it runs. A key inserted after the backfill’s range was scanned but before the backfill finished is not in the range, and its changes come only from the stream. That is correct as long as the stream’s coverage starts at or before the key’s creation. It is incorrect if the backfill’s range was chosen to include the key’s predecessor and the new key falls outside it.

The check is a comparison of the backfill’s key coverage against the source’s key set at the boundary position, and it is the same operation as the completeness reconciliation performed on the result. Without it, a backfill can appear to complete while a slice of keys was never read.

Deletes make this concrete. A row deleted during the backfill is absent from the chunk that had not yet reached it, and its delete is in the stream. The sink applies a delete for a row it does not have, which is harmless if the sink tolerates it, and the row never appears. But a row deleted before the backfill started is absent from the source and absent from the stream’s coverage if the stream started after the delete, so the sink retains a row the source no longer has. Only a tombstone retained by the source, or a full comparison, catches that, and it is why deletes and tombstones in append-only sinks has to be settled before a backfill is designed.

Scheduling the backfill against the stream’s lag

A backfill consumes the same sink capacity as the live stream. If the sink’s write throughput is fixed, the backfill’s writes delay the stream’s, and the stream’s lag grows. On a source with finite log retention, a growing lag is a countdown to the position being recycled and the stream losing its place.

The backfill must therefore be scheduled against the stream’s lag, not against its own progress: pause when the stream’s lag exceeds a threshold, resume when it falls. This makes the backfill’s completion time a function of the stream’s load, which is unpredictable, and it means a backfill can stall indefinitely on a source that is continuously busy. The alternative, a fixed rate that ignores the stream, trades a predictable backfill duration for an unbounded stream lag, and the stream’s failure mode is the more severe of the two.