Pagination Contracts and Why Cursors Drift Less Than Offsets

A client walks a collection of invoices, one page of fifty at a time, building a report. While it walks, another process inserts an invoice near the top of the sort order and deletes one from the middle. The client finishes with a report that contains one invoice twice and omits another entirely, and nothing in the responses indicates that anything went wrong. Every page was well formed. The totals were even plausible.

Pagination is not a presentation detail bolted onto a query. It is a contract about which elements of a changing collection a caller will see, in what order, and how many times. Offset pagination and cursor pagination make genuinely different promises, and the difference only shows up when the underlying data moves.

What offset pagination actually promises

?offset=100&limit=50 asks the server to skip a hundred rows according to some ordering and return the next fifty. The promise is purely positional. It says nothing about which rows those are, because the answer is computed at request time against the current state of the collection.

If a row is inserted before the next page’s starting position, everything shifts down by one: the last row of the page the client already read moves to the boundary and is returned again. If a row is deleted before that position, everything shifts up: the first row of the next page moves into the previous page and is never seen. The error accumulates with the number of mutations: at offset 10,000, a single deletion near the front shifts every later row up by one, so the row that belonged at the boundary is never returned. The Microsoft Azure REST API Guidelines make the same admission explicit: a pageable collection must be documented as potentially skipping or duplicating resources across pages unless the operation takes a time-expiring snapshot.

Offsets also get expensive in a way that is invisible in the API. OFFSET n in most relational databases requires generating and discarding n rows, so the work per page grows with depth even when the page size is constant. A bounded page size protects the response body, not the query.

The offset form remains reasonable for small, mostly static collections, for interfaces where the user jumps to an arbitrary page number, and for exports taken from a snapshot. It is a poor fit for anything that streams a large, active table.

Keyset pagination and the total order it requires

Keyset pagination, also called seek or continuation-token pagination, replaces the position with a value. ?after=<last-seen-key> asks for rows strictly greater than that key under the collection’s ordering. Because the predicate is on data rather than on position, concurrent inserts and deletes elsewhere in the collection cannot displace already-read rows.

-- The ordering must be unique, so the identifier breaks ties on created_at.
SELECT id, created_at, total_cents
FROM invoices
WHERE (created_at, id) > ($1, $2)
ORDER BY created_at, id
LIMIT $3;

The row-value comparison is the important part. Ordering by created_at alone is not a total order, because two invoices can share a timestamp; when they do, the page boundary becomes ambiguous and rows can repeat or vanish even without concurrent writes. Adding the primary key as a tiebreaker makes the order deterministic, and the index must match it: an index on (created_at, id) lets the database seek directly to the boundary, so the cost of a page is proportional to the page size rather than to the depth of the walk.

That last property is the main reason to prefer keyset beyond correctness. Constant work per page means the hundredth page costs the same as the first, which is what makes deep traversal of large collections viable at all.

What a cursor should contain

The token between pages is an interface of its own. It can be the raw key, base64-encoded key material, or an opaque blob produced by the server. Opaque is the better contract, because it lets the server change what the position means without changing the client, and it prevents callers from constructing positions by hand and thereby depending on the sort key.

An opaque cursor typically encodes the ordering key values, the sort direction, and the filter parameters that produced it. Encoding the filter is not decoration: a cursor issued for status=open and then presented to a request for status=all describes a position in a different sequence, and the server should reject the mismatch rather than return arbitrary rows. The same applies to a page size change mid-walk, which is usually tolerable but should be understood rather than accidental.

Signing or encrypting the cursor prevents tampering but does not make it trustworthy in the semantic sense. A caller who cannot forge a cursor can still replay an old one against changed data, and the result is a position that exists but no longer means what it did.

Snapshot tokens and the read-after-write problem

Keyset pagination guarantees no duplicates and no skips for rows that are not themselves modified. It does not guarantee a consistent snapshot: a row updated after the client passed it will not appear again, but a row inserted behind the cursor will never be seen at all, and a row deleted ahead of the cursor simply disappears.

Some APIs need stronger semantics, particularly exports and reconciliation jobs where completeness matters. The standard technique is to bind the walk to a snapshot. The first request establishes a snapshot identifier, either from a database-level consistent read, a materialised list of identifiers, or a timestamp watermark such that all rows created before it are visible. Subsequent requests carry that identifier, and the server answers from the same view. The cost is retention: the snapshot has to be kept alive for as long as a walk may continue, which in a database with MVCC means holding back vacuum or holding a long-lived transaction.

A cheaper approximation is a watermark: the cursor carries the maximum creation timestamp seen at the start of the walk, and the query adds created_at <= watermark. That makes the walk stable against insertions, at the price of excluding everything written during it, and it says nothing about updates or deletes.

Counting is a separate contract

total is a commonly requested field and one of the hardest to keep honest. A count of the filtered set requires a second query whose cost is proportional to the collection size rather than to the page, and in a system with concurrent writes the number can change between pages regardless of how it is computed. A client that trusts a total to decide when to stop will either stop early or loop past the end.

Three approaches are defensible. Omit the total and let clients stop when a page comes back empty or short. Return an approximate count explicitly labelled as such, which is honest and cheap on engines that maintain estimates. Or return the exact count only when the caller asks for it with an explicit parameter, so the cost is opt-in.

Whatever is chosen, the meaning of an empty page and a short page should be defined. A page shorter than the requested limit that is followed by a non-empty page is a legal outcome under concurrent deletes, and clients that treat “fewer than limit” as end-of-collection will silently truncate their results.

Limits, defaults, and the signals around the edge

Every paginated endpoint needs a maximum page size, enforced server-side, because an unbounded limit is a memory exhaustion vector. A default that is smaller than the maximum keeps ordinary clients efficient, and a documented maximum lets clients plan batching.

Where the collection is exposed over HTTP, the Link header defined in RFC 8288 gives intermediaries and generic clients a standard way to find the next page with rel="next", without inventing a response envelope. APIs that prefer a body envelope follow AIP-158 and return a next_page_token that is empty on the final page; the documentation has to state whether the field is absent or empty there, since the two are different signals once the response is parsed.

Page-size and ordering behaviour also interact with the wire contract: the sort order is a documented guarantee, so changing the default ordering later is a breaking change even though no field changed type. The general rules for deciding what counts as breaking are covered in breaking change detection in schemas.

Choosing under real constraints

Offset pagination is simpler to implement and simpler to explain, and it supports random access. Keyset pagination is more code, loses random access, and requires a stable total order backed by an index. The trade is correctness and per-page cost against implementation simplicity and jump-to-page navigation.

A workable compromise is offset for small bounded collections and keyset for anything a caller might traverse in full, with the two exposed as distinct parameters rather than one overloaded interface. That avoids the worst outcome, which is an endpoint whose pagination looks like it guarantees stability and does not, because callers build reports on it and the duplicates are discovered downstream by an accountant rather than by a test.