Cursor Pagination

Offset pagination asks for "rows 5000 to 5050". The database must scan and discard the first 5000, so cost grows with depth. Cursor pagination asks for "the 50 rows after this position", encoded as the sort key of the last row returned, so the database seeks directly and cost is constant.

The performance difference is the well-known half. The correctness difference matters more and is routinely missed.

The correctness problem with offsets

If an item is inserted while a client paginates, everything after it shifts by one position. The client requesting the next page by offset therefore sees one item twice, or skips one entirely, depending on the direction of the shift. Nothing errors. The client's data is quietly wrong.

For a client doing a full traversal of a large collection — which is exactly what integrations do — this is not a rare edge case. Over thousands of pages against an actively changing collection, duplicates and omissions are certain.

Implementation patterns

• Sort on a stable, unique key, or a composite of sort field plus a tiebreaker id. A cursor on a non-unique field silently skips rows that share a value across a page boundary.
• Opaque, encoded cursors. Encode the position rather than exposing a raw column value, so the implementation can change and clients cannot construct cursors by hand.
• A maximum page size, enforced. This is a capacity control, not a courtesy.
• Bidirectional cursors where the product needs backward navigation.
• A separate bulk-access mechanism. Pagination is for browsing. Clients who need the whole dataset should get an export or an event stream — otherwise they will use pagination for it, and full traversals will come to dominate your API load.

Industry example

Developer platforms serving collections that can contain millions of items — repositories, issues, commits, audit events — converge on cursor pagination for both reasons, and the deciding factor is usually the integration population rather than the interactive one. Interactive users rarely go beyond page three; integrations traverse everything, nightly, forever.

The instructive case is the one cursors do not solve: a continuously reranking feed. A cursor fixes instability from insertions by encoding a position in a stable order, but where votes or engagement continuously reorder the list, the sort order itself is unstable. An item can move from page one to page three while the user reads. There is no cursor into a reordering list that guarantees each item is seen once.

That is a property of the data, and the resolution is a product decision: snapshot the ranking per session (correct, but stale), cursor on a stable secondary key (correct pagination, approximate ranking), deduplicate client-side (cheap, gaps stay invisible), or keep a server-side seen-set (correct, expensive). A discovery feed promises interesting content and can tolerate imperfection; a moderation queue promises completeness and cannot.

Failure scenarios

• Cursor on a non-unique sort field, skipping rows at page boundaries.
• Cursors that encode a raw offset internally, inheriting every offset problem while appearing modern.
• Unbounded page size, letting one client request everything.
• Exposing a ranked feed as the bulk-sync path, so integrators build on silently incomplete data.
• No stable ordering at all, where two identical requests return different orders.

Trade-offs

Cursors give up random access — there is no "jump to page 500" and no total count without a separate, expensive query. For a user interface with numbered pages that is a genuine product regression, which is why some products keep offsets for shallow interactive browsing and use cursors for API traversal.

They also constrain sorting: the sort must be on indexed, stable columns, so arbitrary user-selected sorting is harder to support.

Interview question

"A client paginating your API reports that they occasionally miss records during a full sync. Explain what is happening, fix it, and then tell me what you would offer them instead of pagination entirely."