Moving Data Without Losing Any
The Row Was Saved at 10:04 and Became Visible at 10:06
Last timeKnowing Where You Got To
Polling a modified-at column is the obvious way to read changes, and it misses deletes, intermediate states, and anything written inside a slow transaction.
Polling a column
The previous lesson answered where reading got to. This one is about the harder
question underneath it: which rows to read at all, when the source is a database
that people are actively changing.
The obvious answer is to add a column recording when each row was last modified
and select everything stamped after your position.
-- the position from the last run
last_seen = 2026-10-09 10:04:00
select * from orders
where modified_at > last_seen
order by modified_at
limit 5000
-- then: last_seen = max(modified_at) of what came back
Three assumptions are buried in those six lines.
1. Every write sets modified_at. Not enforced anywhere.
2. A row visible to this query has a stamp later than last_seen.
False for anything committed by a transaction that started
before last_seen and finished after it.
3. A row that no longer exists does not need to be reported.
False in every destination that already has a copy of it.It deserves its popularity. There is no new component, no permission to request
from whoever runs the database, nothing to operate, and it is understood by
everybody on the team on sight. For a table where rows are written once and
never deleted, and where arriving slightly late is acceptable, it is the right
answer and the rest of this lesson is overthinking.
The trouble is that most tables are not that table.
| inserts | final-state updates | deletes | intermediate states | writes inside slow trans | |
|---|---|---|---|---|---|
| polling a modified-at co | 1 | 1 | 0 | 0 | 0 |
| reading the database log | 1 | 1 | 1 | 1 | 1 |
The four things it misses
Deletes. A deleted row stops satisfying every query, including the polling
one. The destination keeps its copy forever, and the copy is not merely stale,
it is a record of something that no longer exists, which is worse than missing
data because it will be used. Totals include it, reports count it, and the
person whose account was closed is still in the export. The usual fix is a soft
delete, a column marking the row as removed rather than removing it, which works
and is a commitment the whole application has to keep: every query everywhere now
has to exclude those rows, and the one that forgets is a bug that looks like
nothing.
Intermediate states. A row changed three times between two polls is read
once, in its final state. For a destination that only wants the current value,
fine. For anything counting events, measuring how long something spent in a
state, or auditing who changed what, the data is silently incomplete, and the
amount missing depends on the poll interval, which means a performance
improvement to the poller changes the data.
Writes inside slow transactions. This is the one that is genuinely
surprising, and worth following carefully.
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