How we backfill a 900-million-row table without pausing the stream

A change stream only tells you what happened after you started listening. The first time you point Driftbase at an existing products table, the index needs every row that already exists, and it needs them while new changes keep arriving. That is a backfill, and it is where most CDC tools ask you to stop writes for a while.

We don’t, and the reason fits in one sentence: a row from the live stream always wins over the same row from the backfill. The rest of this post is how we make that sentence true.

Keyset chunks, not OFFSET

The backfiller walks the table by primary key in chunks of 5,000 rows:

SELECT * FROM products
WHERE id > $last_id
ORDER BY id
LIMIT 5000;

Each query is an index range scan that costs the same on the first chunk as on the 180,000th. OFFSET would make the last chunks scan nearly the whole table.

Watermarks around every chunk

Before reading a chunk we write a low watermark into a tiny signalling table, and after reading it we write a high watermark. Both writes travel through the WAL like any other change, so the stream sees them in commit order. Any live change to a row in the chunk that arrives between the two watermarks means our copy of that row may be stale, so we drop it from the chunk before emitting. The live event is already on its way.

What it costs the source

One read-only connection running short range scans, throttled by replica lag if you point it at a replica. On the run above the primary’s CPU rose by 6 percentage points. You can cap it with --max-rows-per-second.

Pausing and resuming

Since v1.8 the cursor is persisted after every chunk, so drift backfill pause is safe and a deploy in the middle of a backfill picks up where it left off. The details are in the backfills guide.