Pagination: Choosing How Lists End
Every list endpoint needs an answer to "and then what?" before the collection grows. Offset, cursor and keyset pagination are different promises about consistency, cost and navigation — and the consumer's access pattern picks, not fashion.
Frame the contract
API design starts with a consumer, a design question and a guarantee — never with a URL.
Three families, three different promises
All pagination answers the same question — "give me a bounded piece, and a way to get the next one" — but the three mechanisms encode the position differently, and the encoding is the contract. Offset says "skip N rows": the position is arithmetic, which buys page numbers, jumping, and "page 7 of 132", and costs correctness under writes, because row 41 is a different record after an insert. Cursor says "continue after this opaque token": the position is a server-defined bookmark, which buys stability and efficiency and costs random access. Keyset is the usual mechanism behind a cursor — "rows after (created_at, id) = (…)" — turning continuation into an indexed seek instead of a skip-and-discard scan.
The performance difference is not subtle at depth. OFFSET 100000 LIMIT 50 forces the database to walk and discard 100,000 index entries to serve 50 — the cost of page N is O(N × page size). A keyset seek lands directly on the boundary and reads 50 rows — the cost of page N is the cost of page 1. For a 10-page collection this distinction is invisible; for the million-row export it is the difference between a plan and an incident (see Offset Pagination: Simple, Jumpable, and Lying Under Writes for the mechanics).
The consistency difference is the one that produces bugs rather than latency. Under concurrent inserts and deletes, offset pages drift: items repeat across page boundaries or vanish between them, so a sync job "visiting every record" silently does not. A cursor anchored to a stable ordering key is immune to that particular drift — new rows appear before or after your bookmark, never *inside* the pages you already turned. Neither mechanism gives you a snapshot of the whole collection; a cursor gives you a stable *frontier*, which for most traversals is exactly enough (see Cursor Pagination: An Opaque Bookmark, Not a Position).
| Property | Offset (`?page=3&limit=50`) | Cursor (opaque token) | Keyset (exposed keys) |
|---|---|---|---|
| Client model | Trivial — arithmetic | Simple — save the token | Moderate — carry the boundary values |
| Jump to page N / "page 7 of 132" | Yes, natively | No | No |
| Cost at depth | O(offset) — degrades with depth | O(page) — flat | O(page) — flat |
| Stable under concurrent writes | No — duplicates and gaps at boundaries | Yes, along the ordering key | Yes, along the ordering key |
| Server keeps internals hidden | Yes | Yes — token is opaque | No — sort keys are public contract |
| Resumable much later | Misleading — positions shift | Yes, within cursor lifetime | Yes — boundary values do not expire |
The consumer's traversal pattern decides
Ask what the consumer does with the list, and the choice usually makes itself. A feed or infinite scroll only ever moves forward: cursor, no contest. A sync or export job needs completeness under concurrent writes: cursor/keyset, because offset drift is silent data corruption for this consumer. A human-facing admin table with column sorting and "jump to page 40": offset is genuinely better *for the UI the product asked for* — and this is where dogma costs. "Cursors are best practice" is not an argument an admin-table user can click on.
The honest resolution for the admin table is to interrogate the requirement: does anyone actually jump to page 40, or do they search and filter? Most "we need page numbers" requirements dissolve into "we need to find things", which filtering serves better (see Filtering: An Allowlist With an Index Bill and Search Is a Different Contract Than Filtering). When page numbers survive interrogation, offer offset with a bounded depth — max_offset=10000, like most search engines — so the UI works and the pathological deep-scan cannot exist.
Whatever family you pick, three clauses are non-negotiable in the contract: a default page size (an endpoint without one is an Unbounded Collections: The Anti-Pattern With a Fuse anti-pattern with a query parameter), a maximum page size the server enforces, and a total ordering — a deterministic sort with a unique tiebreaker like id, because pagination over a non-deterministic order returns overlapping or gapped pages even without concurrent writes (see Sorting: Determinism or Drift).
- Forward-only consumers (feeds, sync, export) → cursor; drift-sensitive jobs make offset a correctness bug, not a style choice.
- Jump-to-page UIs → offset, with a documented depth cap; interrogate whether page numbers are the real requirement first.
- Always: default limit, enforced max limit, deterministic total ordering with a tiebreaker.
- Expose
has_more/next_cursorrather than exact totals when counting is expensive —COUNT(*)on 50M rows costs more than the page.
Pagination is a v1 decision, not an optimization
Pagination is among the least retrofittable contract clauses. Ship GET /orders returning the full array and every client binds to that shape: response parsing assumes a top-level list, sync logic assumes completeness, UIs assume one fetch. Adding pagination later changes the envelope, the client's control flow and its completeness assumptions all at once — a breaking change to every consumer, made under pressure, because the trigger is production pain (see Unbounded Collections: The Anti-Pattern With a Fuse for the full failure).
The cheap insurance is structural: every collection response ships in an envelope with pagination fields from day one — { "data": [...], "next_cursor": null, "has_more": false } — even when the collection has nine rows and one page. Clients then build the "follow next_cursor until null" loop immediately, and the day the collection needs real pages, nothing breaks. One decision, made once, and the API's most common retrofit never happens (see Design Principles Without Commandments on bounded operations).
GET /orders?limit=50
{
"data": [ … up to 50 items … ],
"next_cursor": "djEuMTcwNj…" | null,
"has_more": true | false
}
Contract clauses:
limit default 50 · max 200 (enforced, not advisory)
ordering created_at DESC, id DESC (total, documented)
cursor opaque · valid ≥ 24h · invalid → 400 invalid_cursor
totals not included (use GET /orders/count if you must)Key points
- Offset encodes position as arithmetic (jumpable, drifts under writes, O(offset) at depth); cursors encode it as a server bookmark (stable, flat cost, forward-only).
- The consumer's traversal pattern decides: feeds and sync jobs want cursors; genuine jump-to-page UIs justify bounded offset.
- Offset drift is silent incorrectness for completeness-sensitive consumers — duplicates and gaps at page boundaries under concurrent writes.
- Every list needs a default limit, an enforced max limit, and a deterministic total ordering with a unique tiebreaker.
- Interrogate "we need page numbers" — it usually means "we need to find things", which filtering and search serve better.
- Ship the pagination envelope in v1 even for tiny collections; retrofitting pagination is a breaking change to every consumer at once.
Progressive depth
Overview
Why paginate at all: a collection endpoint is a promise about response size, and an unbounded one is a promise you cannot keep once the data grows. Every list needs a limit, a default, a maximum and a way to continue — see Unbounded Collections: The Anti-Pattern With a Fuse.
Practical
Offset (?page=40&limit=50) gives random page access and is trivial to implement; cursor (?after=<opaque>) gives stable continuation and cheap deep pages. Pick per collection: admin tables that need page numbers vs feeds that change while you read them (Offset Pagination: Simple, Jumpable, and Lying Under Writes, Cursor Pagination: An Opaque Bookmark, Not a Position).
Advanced
Under concurrent writes, offsets drift: an insert before your position shifts every later page, producing duplicates and gaps. Keyset continuation on a unique, monotonic sort key (created_at, id) is immune — but only if the ordering is total and the cursor encodes the last key, not a position (Sorting: Determinism or Drift).
Internals
OFFSET n makes the engine walk and discard n rows before returning any — O(offset) per page, which is why page 2,000 is slow. A keyset predicate WHERE (created_at, id) > (?, ?) seeks straight into the composite index and reads only the page: a B+ tree range scan starting at the cursor (Why Is This Query Slow? Indexes, B+ Tree Internals: Pages, Splits, Merges).
Pagination Under Concurrent Writes
Change the contract and observe which guarantee moves.
—
Offset re-counts from the top every request, so each insert shifts the window back over rows the client already saw. The cursor names a position in the ordering, so new rows above it are simply ignored.
Follow the failure
How the contract fails or gets misused, hop by hop — and what it costs when it completes.
- 1Team → API: ships
GET /ordersreturning the full array — 40 orders in staging, why paginate? - 2Clients → API: bind to the bare-array shape; sync jobs assume one fetch equals the whole truth.
- 3Production → collection: two years of growth; the response is 30MB, p99 is seconds, mobile clients OOM-parse.
- 4Team → API: adds
?page=&limit=and an envelope under incident pressure — a breaking change shipped as a hotfix. - 5Consumers → integrations: every client must be updated at once; the ones that are not silently process page 1 as the complete dataset.
- Unpaginated collections fail with growth: response size, query time and client memory all scale with the table, not the need.
- Wrong-family choices corrupt quietly: an offset-based export double-counts and drops rows under live writes, and nobody sees it until reconciliation.
- Retrofit breaks everyone simultaneously — the change lands on all consumers, on the provider's emergency schedule, not theirs.
Design, observe, evolve
A contract decision is incomplete until you know how you would notice it failing and how it changes later.
- • Paginate every collection from v1 with an enveloped response (`data`, `next_cursor`, `has_more`), default and enforced-max limits, and a documented total ordering.
- • Choose the family per consumer traversal: cursors as the default, bounded offset where jump-to-page is a real, interrogated requirement.
- • Guarantee the ordering with a unique tiebreaker and back it with a matching composite index — the contract clause has a physical bill (see [[cursor-pagination]]).
- • Document staleness semantics: what a traversal observes under concurrent writes, and how long a cursor stays valid.
- • Histogram page depth per endpoint: real traffic that never passes page 3 licenses a tighter depth cap; a tail of deep offset scans is your next incident.
- • Watch p99 latency versus offset value — linear growth is the offset cost curve announcing itself before the timeout does.
- • Track `invalid_cursor` rates: a spike means clients are storing cursors longer than the contract's lifetime, or a deploy broke token compatibility.
- • An enveloped v1 evolves freely: adding cursor fields beside legacy page fields, raising limits, or adding new orderings are all additive.
- • Moving offset → cursor without breaking clients: add `next_cursor` to responses, deprecate `page` with telemetry on who still sends it, then remove after the window (see [[deprecation]]).
- • Cursor internals (encoding, contents) can change any time precisely because the token is opaque — the payoff for never letting clients see inside it.
- • Cursors trade client capability for correctness: no jumping, no page numbers, no parallel range fetches without extra API surface.
- • Enforced limits push work to clients — a consumer that wants 10,000 rows now writes a loop — which is exactly the point, but it is a real ergonomic cost.
- • Supporting both families doubles the testing surface and invites subtle inconsistency (different orderings per family); most APIs should pick one and bound it.