Paging over a moving list
LIMIT and OFFSET are correct only if nothing writes to the table while someone is reading it, which is a condition your app does not meet.
LIMIT 20 OFFSET 40 asks the database to skip forty rows and return the next twenty. The skip happens at query time, against whatever the table contains at that moment.
Between page 2 and page 3 the table is not frozen. Someone posts a comment, a job inserts a record, a moderator deletes a row. The offset still means "skip forty", but forty rows into a different table.
Press next a few times with inserts on.
Paging while the table changes
LIMIT 4 OFFSET 0Keep pressing next. With inserts on, offset paging starts repeating rows on page 2.
Inserting one row above the window shifts everything down by one, so the row that ended page 1 gets returned again at the top of page 2. Deleting one shifts everything up, and a row slides from page 2 to page 1 after page 1 has already been sent. Nobody ever sees it.
Neither case throws. The query is valid, the database is behaving correctly, and the bug lands entirely in the gap between two requests.
Keyset paging
The fix is to stop describing position as a count and start describing it as a value. Instead of "skip forty rows", say "give me the rows that sort after this one".
-- offset: position is a count, so it moves when the table does
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 40;
-- keyset: position is a value, so it doesn't
SELECT * FROM posts
WHERE created_at < :cursor
ORDER BY created_at DESC
LIMIT 20;Switch the demo to keyset and page through with inserts still on. New rows appear above your cursor and you never see them, which is the correct behavior. You asked for what comes after a specific point, and things that sort before that point are not after it.
There's a performance argument for keyset too, and it's usually the one people lead with: OFFSET 100000 makes the database walk a hundred thousand rows before discarding them, while a keyset query seeks straight into the index. That's real, but it only matters at depth. The correctness problem starts on page 2.
The cursor has to be unique
Keyset paging has its own way to lose rows, and it's quieter.
Keyset paging over a column with ties
WHERE score < :cursorScore ORDER BY score DESC LIMIT 32 rows are never returned by any page, because the cursor skips the whole tie group.
Sorting by a column with duplicate values gives you a partial order. When a tie group straddles a page boundary, WHERE score < :cursor excludes every remaining row with that same score, including the ones you haven't returned yet. Four rows tied at 80, three fit on the page, the fourth is gone forever.
The fix is to make the ordering total by appending a unique column, usually the primary key, and comparing the pair:
WHERE (score, id) < (:cursor_score, :cursor_id)
ORDER BY score DESC, id DESC
LIMIT 20;Row-value comparison is standard SQL and Postgres, MySQL, and SQLite all support it. If yours doesn't, the expanded form is score < :s OR (score = :s AND id < :i), which means the same thing and reads worse.
The composite index has to match the sort: (score DESC, id DESC). Getting the column order or direction wrong turns the seek back into a scan, which is how people conclude keyset paging "isn't faster" and revert.
What the cursor should contain
The cursor encodes the sort key of the last row of the page. That's it. It's not an opaque token in any meaningful sense, even when you base64 it.
Two things follow. It leaks whatever you put in it, so don't put anything in there you wouldn't return in the response body. And it's tied to a specific sort order, so a cursor issued under "newest first" is meaningless if the user switches to "highest score". Encode the sort in the cursor and reject mismatches, or you'll get bug reports about pagination going haywire after someone touches a filter.
When offset is fine
Offset is fine over data that doesn't change, which is more cases than the above suggests. A report generated from a snapshot, a static export, admin tooling over an archive table, anything where you control the read window.
It's also the only option when you need to jump to page 47, because keyset can only move relative to a row you've already seen. That's the real tradeoff: numbered pages need offset, and numbered pages over live data are the thing that produces the bug. Infinite scroll and "load more" don't need page numbers at all, which is why keyset fits them exactly.
If you're running offset over a live table today, the cheapest partial fix is to sort by something monotonic and dedupe by id on the client. It won't recover the skipped rows, but it stops users seeing the same item twice, which is the half of the bug they notice.