Key takeaway: Offset pagination answers “skip 400 rows” — a question whose answer changes as rows are inserted. Cursor pagination answers “continue after this specific row”, which stays stable regardless of writes.
Two Independent Problems
Correctness. A client fetches the first page with OFFSET 0 LIMIT 20. Before it requests page two, three new records are inserted at the top of the sort order. Now OFFSET 20 begins three rows later in the reordered set, so three records that already appeared on page one appear again on page two — and three others are skipped entirely, never returned to the client at all.
For a paginated table on screen this is a confusing display that a user might not even notice. For a client synchronising records into its own store, it means silent data loss with no error and no signal that anything went wrong.
Performance. OFFSET 100000 requires the database to locate and then discard one hundred thousand rows before returning the twenty you asked for. There is no shortcut available to the query planner; the work is directly proportional to the offset value.
| Offset | Rows examined | Relative cost |
|---|---|---|
| 0 | 20 | 1× |
| 1,000 | 1,020 | ~50× |
| 100,000 | 100,020 | ~5,000× |
Deep pagination therefore degrades most severely in exactly the places it is used most heavily — crawlers walking a full catalogue and export jobs reading an entire table.
How Cursors Fix Both
A cursor encodes the position of the last returned row along with the sort key, then continues from that point:
SELECT * FROM events
WHERE (created_at, id) < ('2026-03-14 09:12:00', 88213)
ORDER BY created_at DESC, id DESC
LIMIT 20;Insertions elsewhere in the table do not shift this window, because the window is defined by a value rather than by a count. The query uses an index seek directly to the position instead of scanning and discarding, so retrieving page one thousand costs the same as page one.
The composite key matters more than it appears. Paginating on a non-unique column alone loses or repeats rows whenever values tie, because the database has no defined order among equal values and may return them differently between queries. Appending a unique tiebreaker — normally the primary key — makes the ordering total and the cursor position unambiguous.
Encode the cursor as an opaque token rather than exposing raw column values. Clients that parse and construct cursors themselves become coupled to your sort implementation, which prevents you from ever changing the sort order or adding a tiebreaker without breaking them.
What You Give Up
Cursors cannot jump directly to page fifty, because there is no page fifty without counting the rows before it. They also make exact total counts awkward, since a cursor query deliberately avoids scanning the full result set.
| Requirement | Offset | Cursor |
|---|---|---|
| Stable results under concurrent writes | No | Yes |
| Constant cost at depth | No | Yes |
| Jump to an arbitrary page | Yes | No |
| Exact total count | Yes | Expensive |
For infinite scroll, activity feeds, synchronisation and bulk export, cursors are simply correct. For an administrative table where a human genuinely selects page numbers from a control, offset over a stable filtered set is acceptable — keep the maximum depth bounded and never use it for programmatic synchronisation.
Total counts can be handled separately when needed: an approximate figure from table statistics, or a cached exact count refreshed on a schedule. Most interfaces display a precision they do not actually require, and “about 12,000 results” serves users as well as an exact number computed at real cost.
The Bottom Line
Default to cursor pagination for any API a program consumes, use a composite sort key with a unique tiebreaker, and return opaque cursor tokens so you retain freedom to change the implementation. Reserve offset pagination for bounded human browsing where the page-number control is genuinely required by the interface.
