Cursor or offset pagination: which one breaks and when
Users report seeing the same row twice while paging, or missing rows entirely, on a list that is being written to while they read it.
That is offset pagination behaving as designed. An offset names a position in a result set that shifts whenever an earlier row is inserted or deleted, so concurrent writes cause duplicates and gaps. Cursor pagination asks for the rows after a specific item instead, which stays correct under concurrent writes and stays fast at depth.
Why offsets drift
LIMIT 20 OFFSET 100 means "skip the first hundred rows of the current result set". If a row is inserted near the front between two requests, everything shifts by one: the reader sees row 100 again. If a row is deleted, one is skipped entirely. Nothing is broken — the query answered the question that was asked.
Why offsets get slower
A database serving OFFSET 100000 still walks the hundred thousand rows it is going to discard. Cost grows linearly with depth even though the page size is constant. A cursor seeks directly to its position using an index, so the last page costs what the first page costs.
The tiebreaker most cursors forget
A cursor must be built on a stable, unique, ordered key. Ordering by a timestamp alone is the usual mistake: when several rows share a value at a page boundary, some are skipped. Adding the primary key as a tiebreaker makes the ordering total and the problem disappears.
What you give up
Cursors cannot jump to page fifty, because the position is defined by the previous item rather than by a count. If arbitrary page numbers are a hard requirement, offsets are the only option and the drift is the price. Most product surfaces need infinite scroll or next and previous, which cursors serve better.
Offset against cursor
Offset against cursor
| Property | Offset | Cursor |
|---|
| Stable under inserts | no | yes |
| Cost at depth | grows linearly | constant |
| Jump to page N | yes | no |
| Needs a unique sort key | no | yes |
| Total row count | natural | separate query |
Key facts
- Offset pagination can repeat or skip rows when the list changes between requests, because an offset names a position that shifts as earlier rows are inserted or deleted.
- Deep offsets get slower as they grow, since the database walks the rows it will discard, while a cursor seeks directly to its position.
- A cursor built on a non-unique key silently drops rows that share a value at a page boundary; a unique tiebreaker such as the primary key fixes it.
- Cursor pagination cannot jump to an arbitrary page number, which is the trade it makes for correctness.
- Cursors are opaque and must be stored and returned unchanged, never parsed or constructed by the client.
Frequently asked questions
Can I show a total page count with cursor pagination?
Only with a separate count query, and on a large table that count is expensive and immediately stale. Most interfaces are better served by "load more" than by a page count that is wrong by the time it renders.
My cursor skips rows at page boundaries. What is wrong?
The sort key is not unique. Rows sharing the same timestamp straddle the boundary and some are never returned. Sort by the timestamp plus the primary key and encode both in the cursor.
Is a cursor just an encoded offset?
It should not be. If the cursor encodes a position rather than a value, it inherits every drift problem offsets have while looking like it does not. Encode the sort key values of the last row returned.
Machine-readable copy of this page:
/guide/choose-cursor-or-offset-pagination.md