The Source Keeps Changing While You Read It

Summary

When you receive a source that keeps changing in pages, if you mark the position by number (offset), rows quietly disappear, and if you mark the position by the last value seen (cursor), they do not. That value must be not one timestamp but two slots, (updated_at, id).

Why this was needed

There is a synchronization that "receives the whole customer list every day." It receives 600,000 records in 600 passes of 1,000. But while it runs those 600 passes, the source keeps changing too.

Suppose you sort with ORDER BY updated_at and receive LIMIT 1000 OFFSET 10000 at a time. Just before we receive the 11th page, 3 rows that were on a page we have already passed are updated and their updated_at becomes the current time. In the sort order those 3 rows go to the very end. Then every row behind them is pulled forward by three places. Offset 10000 used to point to the 10000th row but now points to the 10003rd row. The 3 rows in between pass by without anyone reading them.

This incident has three characteristics. No error occurs. The count is roughly right. A different row is missing each time. So it cannot be reproduced, and it comes back months later as a report, "this customer is not on our side."

How it works

The fix is to mark the position by value, not by number. Remember the sort key of the last row read, and in the next request ask for "everything greater than that value." It is commonly called keyset pagination or cursor pagination.

오프셋:  "10000번째부터 1000개"     ← 앞이 밀리면 가리키는 곳이 달라진다
커서:    "이 값보다 큰 것 1000개"   ← 앞이 밀려도 이 값보다 큰 것은 그대로다

Here comes the second trap. The sort key is not unique. updated_at is in units of seconds, so it is common for several rows to have the same value. If you take the cursor with updated_at alone, one of two things happens.

• With WHERE updated_at > :last, the remaining rows with the same timestamp vanish entirely.
• With WHERE updated_at >= :last, you read rows already read again. If the rows with the same timestamp outnumber the page size, you go round the same place forever.

So the cursor adds slots until it becomes unique. Usually two slots, (updated_at, id), are enough, and the comparison also compares the two slots together. It is (updated_at, id) > (:last_at, :last_id).

The watermark is carrying that cursor to the next run. To avoid receiving from the beginning again when interrupted, the watermark must be on disk. And when you save the watermark matters. If you save before processing the received rows, you lose that page on interruption, and if you save after processing, you receive that page again on interruption. Receiving again is better than losing — so most synchronizations are designed as at-least-once and make the receiving side idempotent.

And in this approach duplicates are normal. If a row we passed is updated, it comes behind the cursor and is caught again. That is not a bug but exactly the fact "it changed in the meantime." If the copy side overwrites keyed by id, the result is correct.

The last is reconciliation. Do not believe you received everything; count. Compare the count at the source and the count at the copy, and even whether the values of rows present on both sides are the same. Having only the counts match is not matching — if one is missing and one is duplicated, the count stays the same.

When using time as a key, you must agree on the notation too. RFC 3339 defines the date and time notation used on the internet. If you plan to compare as strings, the number of digits and the time zone notation must be the same in every row — 2026-09-10T00:00:00Z and 2026-09-10T00:00:00+00:00 are the same instant but differ in a string comparison. As for how pages are handed over, there are also many APIs that give next through the Link header of RFC 8288, so check what the other side gives before attaching.

What it looks like in the field

First, "can't we just receive everything again?" comes up often. If the data is small, that is right. But since a full re-collection also changes while it runs, the point that the snapshot moment is not a single instant but spreads across the entire traversal time is the same.

Second, there are sources that do not update updated_at. If the timestamp stays the same however you fix a field, the cursor approach will never see that change. Before attaching, always ask "what must be changed for this timestamp to change?" Deletion is the same — when a row disappears you cannot know it with a cursor, so there must be a separate way of announcing deletions.

Third, the clock goes backward. If the clocks of several source servers differ slightly, the updated_at of a row written later can be smaller than an earlier row. Then it is a place the cursor has already passed, and it is never caught. So many implementations set the watermark pulled back a little (for example a few seconds earlier) and accept the duplicates.

Fourth, the first run is the most dangerous. While receiving 600,000 records for the first time, the source keeps changing. It is safe to run the first run in a quiet time and, right after it ends, run once more to catch up with the changes in between.

What you will do in the next lab

You start a source server that keeps changing and take a snapshot of the full 60 rows. While traversing with the offset method, you change the source midway and confirm with numbers that rows go missing and are duplicated at the same time. Then you move to the (updated_at, id) cursor and see the omissions become 0 in the same situation. You leave a watermark in a file so the second run receives only what changed, make a page split across the boundary of five rows with the same timestamp to see whether the cursor holds, and finally resume an interrupted synchronization and compare the source and copy to confirm that even the values match.