Moving Data Without Losing Any
Start the Stream Before You Read the Old Rows
Last timeReading Only What Changed
Loading history and following live changes are two jobs, and the join between them is where records vanish. The fix is an order, and a deliberate overlap.
Two jobs, one table
The last lesson ended with a limitation worth taking seriously: the log has no
history. It begins wherever the database currently keeps it from, typically hours
or days back, and the rows that existed before that point appear in it nowhere.
So standing up a new consumer means two jobs. Read the rows that already exist,
which is a query against the table. Follow the changes from now on, which is a
read of the log. Neither does the other job, and no amount of configuration makes
one of them do both, because the information is not there to do it with.
Two jobs means a boundary, and the boundary is the whole problem. Every row has
to be covered by at least one of the two paths, and the obvious way of arranging
them leaves a set of rows covered by neither.
The window that loses
The natural order is to load the history and then start following changes. It is
how everybody does it the first time, and it reads as obviously correct: finish
the old, begin the new.
| step | time | action | row 7781 | in the destination | what happened |
|---|---|---|---|---|---|
| 1 | 09:00 | snapshot query begins | status packed | not yet | The snapshot reads the table as it was at this instant. Row 7781 is read early, with status packed. |
| 2 | 09:40 | the row is shipped | status shipped | packed | The snapshot has already passed this row. The stream is not running yet. This change is observed by nothing. |
| 3 | 11:20 | snapshot finishes | status shipped | packed | Two hours and twenty minutes of changes have happened during the snapshot, none of them captured. |
| 4 | 11:20 | stream subscribes from now | status shipped | packed | The stream starts here and will carry every future change. It carries nothing from before. |
| 5 | next year | nothing further happens to the row | status shipped | packed, wrong forever | The destination says packed. Nothing will ever correct it, because a correction requires a change event and the change already happened. |
Three things about that window are worth drawing out, because each one defeats a
tempting shortcut.
It is proportional to the snapshot. A hundred million rows take hours, and every
minute of those hours is a minute of changes nobody captures. The more data you
have, the more you lose, which is precisely the wrong direction.
Shrinking it does not fix it. A faster snapshot, a read replica, a maintenance
window at four in the morning: each makes the window smaller and none makes it
zero, and a window that loses data silently is not safe at any size. Teams have
run this design for years and discovered the gap when a customer asked why a
closed account was still receiving reports.
And doing it the other way round is no better. Subscribe first, then snapshot,
discarding the stream output while the snapshot runs, and the same changes fall
in the same hole.
The lesson stops here
3 more paragraphs to go
You have read the opening. The rest of the argument, the problems that check whether it landed, and the lines worth keeping at the end all come with a plan.
The first lesson of every course in the library reads the whole way through, free, so you can see exactly what the rest of them are.
See the planThe contentsThis is the reading half
Starting the course gives you your own copy of it. Every idea on every page has problems standing under it, marked with a reason rather than a tick, and any sentence you do not believe can be opened and argued with. None of that can happen on a page nobody owns.
The contents