Cursor vs offset pagination: choose based on data movement, not taste

LIMIT 50 OFFSET 50000 is an easy API to explain. It is also a poor fit for an infinite feed that changes while someone scrolls, and it tends to get slower as the offset grows. Cursor pagination solves a different problem, not a fashionable one.

The rule of thumb: offset pagination for bounded admin lists where someone needs page 37; cursor (keyset) pagination for large, ordered, frequently changing result sets. A cursor has to encode a deterministic sort position, usually a timestamp plus a unique ID.

SELECT * FROM events
WHERE (created_at, id) < (:cursor_time, :cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT 51;

Fetch one extra row to decide whether hasNextPage is true. Return the final item's (created_at, id) as an opaque cursor—base64 is encoding, not secrecy, so sign or encrypt it if clients must not be able to alter it.

A cursor on created_at alone is not stable when several rows share the same timestamp. Add a unique tiebreaker or you will eventually skip or duplicate results.

Offset is not wrong. It is the honest choice when users navigate numbered pages and the query is small. Cursor pagination earns its complexity only when ordered data moves underneath the reader.

Decide what “next” guarantees

With offset pagination, rows inserted before page two shift the offset: a reader can see a duplicate or miss an item. A keyset query anchors page two after the last tuple returned on page one. That gives a forward scan a stable boundary, but it does not create a snapshot of the whole dataset.

For a bidirectional UI, define both directions. Use > with ascending order for “after”, < with descending order for “before”, then reverse the returned slice if the API promises a consistent display order. Do not reuse an opaque cursor across a changed filter or sort; encode a version and reject incompatible cursors rather than returning a plausible but wrong page.

Index the query you wrote

The database needs an index matching the filter and order, for example (tenant_id, created_at DESC, id DESC). A cursor is not a performance spell if the planner still scans every tenant or sorts a giant intermediate result.

Cover photo by Pixabay on Pexels.