Pagination, cursors and incremental sync tokens

Why is offset pagination silently wrong under writes, and what makes a sync position safe?

This is the purest form of this article, because here the position is the interface, and there is nothing else.

The forcing constraint: a result larger than one response must be split, and the split must survive the gap between responses, during which the underlying data changes. So the question is what the position is anchored to. There are exactly two answers, with different failure modes.

Anchor to an ordinal: `OFFSET`

-- Newest customers first, 100 per page. This is page 3.
SELECT * FROM customers ORDER BY created_at DESC LIMIT 100 OFFSET 200;

"Skip N, take M." The position is meaningless as soon as a row is inserted or deleted before it.

One change between pages, and OFFSET 3 points elsewhere

An insert before the position shows a row twice; a delete before it hides one. There is no error in either case.

It is also increasingly expensive, because reaching offset N generally costs O(N) (the database walks past N rows). Page 10,000 is the slowest query in the system. With 100 rows per page, page 10,000 means skipping about 1,000,000 rows before returning 100.

Anchor to a value: keyset pagination

-- "everything after this key"
SELECT * FROM customers
WHERE id > :last_seen_id
ORDER BY id
LIMIT 100;

The position stays meaningful across changes, and lookup is cheap because it is a seek (jump straight to the place using an index) rather than a count. The catch is the one from connectors: the anchor must be totally ordered and stable.

Worked example (non-unique timestamp)

Three rows share the timestamp 10:00:00.000, with ids 7, 8 and 9. The page size is 2. Page 1 returns ids 7 and 8; the last seen timestamp is 10:00:00.000. The next query WHERE ts > '10:00:00.000' skips id 9. The fix is a tiebreaker:

WHERE (ts, id) > (:last_ts, :last_id)
ORDER BY ts, id
LIMIT 2;

This is Priya's "same millisecond" problem.

Offset pagination is the most common correctness defect at this boundary, and it should be said plainly:

It survives because it is trivial to implement and its failure is invisible: nobody sees the records they did not receive. This is the unverifiable assumption again, and the consumer pays without ever knowing.

Opaque sync tokens: a third bargain

The provider gives you a token that means "the state you have seen", keeps the meaning to itself, and thereby keeps the freedom to change its internals. You gain a correctness you could not implement yourself. You lose the ability to reason about, repair or construct a position. A lost token often means a full resync, with no partial recovery. This is early binding of the position's meaning to the provider, and the failure appears as an operational cliff instead of a gradual slope.

In one sentence: paginate by value, never by count, and never trust a position that is not unique.