Pagination strategies: offset vs. cursor-based

Returning every row of a large table in one response wastes bandwidth, memory, and time for both client and server — pagination breaks a large result set into pages a client fetches incrementally. The design question isn't whether to paginate large lists; it's how to identify which page a client is asking for.

The client asks for 20 items starting at position 40 — conceptually simple, and directly maps to a SQL LIMIT/OFFSET clause. This is genuinely fine for small, mostly-static datasets, and it's why it's such a common default choice.

Offset pagination assumes the list stays stable between requests. If a new row is inserted (or an existing one deleted) while a client is paging through results, every subsequent offset now points to a different logical position than it did a moment ago — a client can see the same item twice, or skip one entirely, without either client or server doing anything wrong individually. This is invisible in a quiet demo and a genuine, reproducible bug on any live, actively-changing dataset — a social feed, a live order queue, anything with real write traffic.

There's also a real performance cost at scale: a database typically still has to scan and discard the first offset rows to reach the requested page, so ?offset=1000000 can be meaningfully slower than ?offset=0, even though both return the same number of rows.

Instead of a numeric position, the client passes an opaque cursor — a token (often an encoded ID, or an ID plus a timestamp) identifying the last item it saw. The server returns the next 20 items after that specific item, regardless of anything inserted or deleted elsewhere in the list.

Because the cursor identifies a specific item rather than a numeric position, insertions and deletions elsewhere in the list don't shift what "the next page" means — this is exactly the property offset pagination lacks, and it's why cursor pagination is the standard choice for anything with meaningful write volume (social media feeds, chat message history, most modern public APIs).

A cursor doesn't support jumping directly to "page 7" the way an offset does — cursors are inherently sequential, one page reachable from the previous one's cursor, not addressable by arbitrary position. Most APIs accept this trade-off because arbitrary page-number jumping is rarely a real user need (nobody scrolls a social feed by typing a page number), while correctness under concurrent writes usually is.