Offset pagination silently skips rows when data moves

Offset pagination is not a performance problem. It is a correctness problem, and the symptom looks like a performance problem, which is why it survives so long.

The bug: a feed paginated with LIMIT 20 OFFSET n skips rows. Not occasionally — deterministically, whenever the underlying data changes between page loads. With a live price table, that is every time.

Why it skips

OFFSET does not mean "continue from here". It means "count this many rows and throw them away". The database re-evaluates the whole ordering on every request. If a row that was on page one has since dropped out of the filter — a stale quote expired, a delisted pair was removed — every subsequent row shifts up by one, and the row that was first on page two moves onto page one and is never shown.

The user sees a feed that appears to be missing entries. Support sees a report that cannot be reproduced, because by the time they look, the data has settled and the gap is not there.

With a fast-moving dataset the same mechanism duplicates rows instead: a new row lands at the top of the ordering, everything shifts down, and the last row of page one reappears as the first row of page two. Duplicates are easy to dismiss as a rendering glitch. Missing rows are not, and they are the same bug.

A cursor is an ordering, not an id

The fix is to paginate on the ordering key rather than on a row count. But "cursor pagination" is usually described as WHERE id > :last_id, and that only works when the ordering is by a unique, immutable key.

For a price feed the ordering is by (price_updated_at DESC, pair_id), and price_updated_at changes whenever the row is refreshed. So the cursor has to carry the whole ordering, and the comparison has to be a row-value comparison:

WHERE (price_updated_at, pair_id) < (:last_ts, :last_pair)
ORDER BY price_updated_at DESC, pair_id DESC
LIMIT 20

The pair_id tiebreak is not decoration. Without it, two rows with the same timestamp have an arbitrary relative order, and a cursor built from one of them can skip the other. Timestamps collide far more often than people expect — batch refreshes write hundreds of rows inside the same millisecond.

One more thing that took a bug report to learn: the cursor must be stable across the page load. If you build it from the last row as displayed after your application has re-sorted or de-duplicated the results, the cursor no longer corresponds to a position in the database ordering, and you get a different flavour of the same skip.

The cost you are actually paying

Cursor pagination gives up the ability to jump to page 47. For a feed with "load more", nobody wants that. For an admin table with page numbers, they do, and then the honest answer is that page numbers over live data are inherently inconsistent — the number of pages changes between requests. If the feature is required, snapshot the result set (a materialised view, or a token that pins a as_of timestamp) rather than pretending offsets are stable.

There is a real cost too: the index has to match the ordering exactly, or the cursor query becomes a sort over the whole table. (price_updated_at, pair_id) needs a composite index in that order; a single-column index on the timestamp will not do, and the query plan will not tell you why it got slow until the table is large enough that it matters.

How to test it

The test that catches this is not "does page two return 20 rows". It is:

1. Fetch page one.
2. Mutate the dataset the way production does — delete a row from page one, insert a row at the top.
3. Fetch page two using the cursor.
4. Assert that no row from the original page one appears again, and that no row between them is missing.

That test fails on offset pagination and passes on a row-value cursor. It is also the only version of the test that would have caught the original bug, because a static dataset makes both implementations look correct.

The same lesson shows up in every "it works in staging" story: the bug lives in the interaction between the query and the mutation, and a fixture that never mutates cannot reproduce it. This is a close cousin of the rounding problem — the rule is correct on the happy path and wrong at the boundary — and of the day-boundary bug in fee tiers, where the value was right and the window was wrong.

---

If you want to see a feed that survives this — exchange fee tiers with a stable cursor and a timestamp on every figure — it is at fipst.com. The pagination there is on (updated_at, pair_id), and the reason it is not on OFFSET is a support ticket that took three days to reproduce.