Cursor pagination vs offset: why offset breaks under real writes

We had a support ticket queue endpoint, paginated the obvious way, and it passed every test we threw at it. It also quietly showed some users the same ticket twice across two pages, and made other tickets disappear entirely, and it took an embarrassingly long time to realize both bugs were the same bug.

The pagination that looked completely standard

-- page 3, 20 rows per page
SELECT id, subject, created_at
FROM tickets
WHERE account_id = $1
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;

This is the pagination pattern most people learn first, and for a read-only report against a static export, it is genuinely fine. Our table was neither of those things. It was a live support queue, with new tickets landing and old ones closing constantly, sorted by created_at DESC so the newest ticket was always first. OFFSET 40 does not mean “the same 40 rows as last time,” it means “skip however many rows currently satisfy the WHERE clause and come before this one in sort order, counted fresh, right now.” If a new ticket comes in between a user loading page 1 and page 2, every ticket that used to be at position 20-39 shifts down by one, and OFFSET 40 for page 3 now points one row later than the user’s actual next-in-line ticket. Depending on whether tickets were being added or closed, users either saw a ticket repeated across two pages, or skipped one entirely, and neither looked like a bug until someone compared what they saw against the raw table.

Why this only shows up with real traffic

Every automated test we had seeded a fixed set of rows, queried all the pages back to back with nothing else touching the table, and got a perfectly consistent result every time, because the underlying assumption of OFFSET (the set of rows and their order do not change between requests) was actually true in that environment. It stopped being true the moment a second ticket could arrive while a user had page 1 open in their browser, which is every moment in production and none in a script that runs to completion in fifty milliseconds. The bug was invisible everywhere except the one environment that mattered, not because anyone missed a test case, but because the failure requires two things happening concurrently and most testing setups go out of their way to avoid exactly that.

The fix: anchor to a row, not a count

Cursor pagination replaces “skip this many rows” with “give me rows after this specific row,” using a value from the last row of the previous page as the anchor instead of a row count:

-- page after the ticket with created_at = '2026-07-14T09:12:00', id = 8831
SELECT id, subject, created_at
FROM tickets
WHERE account_id = $1
  AND (created_at, id) < ('2026-07-14T09:12:00', 8831)
ORDER BY created_at DESC, id DESC
LIMIT 20;

(created_at, id) as a compound tiebreaker matters because created_at alone is not guaranteed unique, two tickets can land in the same millisecond, and without id as a tiebreaker the boundary is still ambiguous. The client gets back the (created_at, id) of the last row on each page and sends it as the cursor for the next one. New rows arriving anywhere in the table no longer shift anyone else’s position, because the query is not counting rows from the top, it is finding rows strictly after a specific, already-seen one. A ticket inserted after the anchor row simply appears on whichever future page its own position lands on; it cannot cause a row the user already saw to repeat or a row they have not seen yet to vanish.

The tradeoff that makes this worth calling out

Cursor pagination is not a strict upgrade, it costs you the thing offset gives away for free: jumping straight to page 7. A cursor only knows “the row after this one,” so there is no way to compute page 7’s boundary without walking through pages 1 through 6 first, or maintaining a separate index of page boundaries, which is its own maintenance burden. For a search results UI with page number links, that is a real loss. For anything closer to a feed, a queue, or an infinite-scroll list, where users move forward one page at a time and rarely jump to an arbitrary page number, it costs nothing, because nobody was using that feature anyway.

What actually decides which one to use

The question that settles it is not “which is more correct,” cursor pagination basically always is, it is whether your data changes between page loads and whether users need to jump to arbitrary pages. A nightly-generated report against data that will not change until tomorrow can use offset safely, because the assumption it makes happens to be true there. A live queue, a feed, or any list backed by a table with ongoing writes needs a cursor, because offset’s core assumption, a fixed row order that stands still while the user pages through it, is exactly the thing a live table never gives you.