Bootstrap: snapshot and streaming handoff

How do you combine a snapshot with a live stream without locking the source?

CaseConstructed case · Tariq at Quillpost

4,000 orders with an old status after the copy

Tariq starts the log reader. It streams new changes, but the warehouse needs the 300 million existing rows too. He first copies the table with a plain SELECT * while the app keeps running, then starts the stream. After the first day, finance reports that about 4,000 orders show an old status in the warehouse, and a few orders that were deleted during the copy reappear.

A week later a second problem appears. The dashboard shows a customer transfer (a refund of 200 from one wallet balance to another) where the money left one wallet but has not yet arrived in the other. It stays that way for minutes. And one night Lena changes the type of the total column from integer to decimal, and the consumer crashes.

How it ends →

The problem

The change stream starts at a point in time, but the table existed before it. You need the state at the stream's beginning, and the snapshot and the stream must meet exactly once: no gap (lost rows) and no unresolvable overlap (wrong final state). Tariq's plain copy broke this rule: changes made during the copy were either missed or applied in the wrong order.

The simple, correct, unacceptable way

Take a locked consistent snapshot at log position L, then start streaming from L. This is correct. It is also unacceptable on a large table, because the lock lasts as long as the snapshot. Copying 300 million rows can take hours, and during that time writers wait.

The lock-free way

The lock-free resolution exploits a property of the target, not the source. If the sink is keyed and applies upserts, then a replayed change is harmless: applying "set order 17 to status shipped" twice leaves the same row.

So you may:

read the snapshot slowly, with no lock, while streaming changes concurrently;
resolve any collision by a total-order rule: a streamed change at a higher log position always wins over a snapshot row that was read earlier.

The incremental snapshot variant is more refined. It splits the table into chunks by key range (for example order ids 1 to 100,000, then 100,001 to 200,000, and so on). Before and after reading each chunk, it writes watermark markers into the log itself. Changes that appear in the log between the two markers are changes that happened while the chunk was being read. The log's own ordering then decides, chunk by chunk, which version of each row wins. The advantages: no long lock, and the process can pause and resume after a crash, one chunk at a time.

One chunk, two markers, one rule

The limit of this trick

This is idempotence purchased by restricting the consumer's semantics. It does not work if the consumer is path-dependent. An append-only history cannot deduplicate a replayed update, because "again" is meaningful there: it would record the same status step twice.

In one line: consistent bootstrap without locking requires an idempotent, keyed sink; the source's total order arbitrates, and the sink absorbs the overlap.