A partner paging through an endpoint with limit and offset reports that their nightly sync misses records, and occasionally imports the same record twice. The data is ordered by creation time and new records arrive throughout the run. Which explanation is correct?

The mechanism

LIMIT 100 OFFSET 200 means "re-run the query, discard 200 rows, return the next 100". The offset is a position in a result set that is computed fresh on every request, so if the underlying data changes between requests, the row at position 200 is not the row that was at position 200 before.

With newest-first ordering and inserts arriving during the run:

• Each insert pushes every existing row down one position. The row that would have been first on the next page has already been returned - it is seen twice.
• Each delete pulls rows up one position, so a row crosses the boundary in the other direction and is never returned.

A sync over 120 pages with inserts throughout therefore misses and duplicates in proportion to the write rate during the run. The partner's description - some missing, some doubled - is the exact signature.

Why the other options fail

• "Retries explain the duplicates." Retries can duplicate, and they cannot explain missing records, so they do not account for the symptom pair. Fix retries anyway, and it will not fix this.
• "Non-deterministic ordering from ties." A real bug with a related symptom, and it is the second thing to fix: ties in the sort key make page boundaries arbitrary. But it would not produce systematic drift tied to write volume, and it affects records with identical timestamps only.
• "Query timeouts returning partial results." An endpoint that silently returns partial pages is a different and worse defect; it would show as short pages, which the partner would have mentioned.

The fix: keyset pagination

Page by the last value seen rather than by a count:

WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 100

• Stable under concurrent writes, because the boundary is a value in the data rather than a position in a result set.
• Constant cost per page. Offset pagination has to scan and discard the offset, so page 1,000 reads 100,000 rows before returning anything - the reason deep pagination gets slower as the partner progresses, and why search engines cap it (Elasticsearch's default index.max_result_window of 10,000 is exactly this limit made explicit).
• The tie-break key is mandatory. (created_at, id) with a unique id makes the ordering total; without it, rows sharing a timestamp straddle the boundary and the original bug returns in a smaller form.
• Return an opaque cursor rather than exposing the key values, so the encoding can change later without breaking partners.

What to tell the partner tonight

They cannot fix this on their side, and it is worth saying so plainly rather than letting them add retries. The interim mitigation is an overlapping window: re-fetch a period that overlaps the previous run and deduplicate by record id, which costs a little bandwidth and makes the sync idempotent. That is a good property to keep even after the cursor endpoint ships.

When offset pagination is fine

For a human-facing UI over a small, slow-changing set - the first few pages of a settings list - offset is simpler, supports "jump to page 7", and the drift is invisible at that scale. The rule: offset for browsing, cursors for synchronisation. The moment a client is expected to enumerate everything exactly once, offset is the wrong tool.

When this is the wrong fix to prioritise

If the partner's sync covers fewer than about 10,000 records and the write rate during the run is near zero, the drift is real and rare, and an overlapping window with deduplication costs an hour of their time against a week of yours. Keyset pagination is the right design and it is not always this quarter's priority - which is a judgement to state explicitly rather than let the backlog decide. The threshold that flips it: a partner enumerating more than a few hundred thousand records, or any sync whose output feeds billing.