Cursor pagination is commonly justified as a faster replacement for OFFSET. Performance is only half the design. A cursor defines what “continue this listing” means while rows are concurrently inserted, updated, deleted, or hidden by authorization changes.
A cursor is a serialized continuation contract, not a disguised row number.
Before choosing its encoding, decide which consistency experience the API promises.
Make ordering total and immutable enough #
Pagination needs a deterministic total order. ORDER BY created_at is insufficient when multiple records share a timestamp. Add a unique tie-breaker:
SELECT id, created_at, title
FROM documents
WHERE tenant_id = $1
AND (created_at, id) < ($2, $3)
ORDER BY created_at DESC, id DESC
LIMIT $4;The cursor carries the last (created_at, id) pair. The next query uses the same filter and order.
If the ordering column can change, an item can move across the boundary between requests and appear twice or not at all. Prefer an immutable ordering key for traversal. If the product must sort by mutable popularity or status, document weaker semantics or freeze the ranking version.
Choose a mutation model #
There are three useful contracts.
| Contract | Behavior during traversal | Cost |
|---|---|---|
| Live traversal | New changes may appear according to key order | Simple, not a stable snapshot |
| High-water mark | Excludes items created after page one | Stable upper boundary |
| Snapshot | All pages read one logical dataset version | Strongest, needs retained snapshot/state |
For a high-water mark, the first response includes the maximum eligible ordering key and later queries constrain results beneath it. This prevents newly inserted items from shifting the traversal, but updates and deletions still require defined behavior.
A database snapshot held across human-paced requests is usually impractical. An export job can materialize results or retain a version; an interactive feed often accepts live or high-water semantics.
Keep tokens opaque but accountable #
Google’s API design guidance uses page_token and next_page_token for list pagination and recommends opaque page tokens. Opaque does not mean unstructured internally. Version the payload so its schema can evolve.
{
"v": 2,
"lastCreatedAt": "2026-09-05T04:00:00Z",
"lastId": "doc_01K...",
"filterHash": "sha256:...",
"subjectHash": "sha256:...",
"expiresAt": "2026-09-05T05:00:00Z"
}Encode and authenticate the token or store a random handle server-side. Plain Base64 prevents casual reading, not tampering. Reject unsupported versions, invalid signatures, expired cursors, and unreasonable decoded values.
Bind continuation to the original query #
A cursor minted for tenant=A&status=open must not be reusable with tenant=B or status=closed. Bind it to normalized filters, sort direction, API version, and the authorization subject or entitlement scope.
Do not trust tenant data carried inside the cursor as authorization. Derive accessible scope from the authenticated request, then verify that it matches the cursor’s binding. This follows the same principle as treating cache keys as isolation boundaries.
Decide how projection changes behave. If fields= affects only representation, reuse may be safe. If it affects eligibility or joins, include it in the query fingerprint.
Define deletion and exhaustion #
Keyset pagination does not require the boundary row to still exist; the tuple values in the cursor remain sufficient. That is one advantage over a cursor that stores only an object ID and looks the row up later.
Return an absent next token when traversal is exhausted. An empty page may still legitimately have a next token if post-filtering occurs after fetching, but that design can confuse clients and waste calls. Prefer applying visibility filters inside the query so page size describes deliverable items.
Set a maximum page size and treat client size as a hint. Token continuation must preserve the server’s ordering contract even if the client requests a different size later; either permit only size changes that do not alter eligibility or bind size explicitly.
Plan index shape with the query #
The index should support tenant/filter prefix followed by ordering keys. For the sample query, a likely starting point is:
CREATE INDEX documents_tenant_created_id_idx
ON documents (tenant_id, created_at DESC, id DESC);Real selectivity and additional filters determine the final index. Inspect the execution plan with production-like distributions. Cursor pagination avoids scanning skipped offsets, but it cannot rescue an index that does not match the predicate.
Test properties, not examples #
Generate datasets with equal timestamps, delete boundary rows, insert before and after the high-water mark, mutate sortable fields, change authorization between pages, tamper with tokens, and switch sort direction.
Assert:
- no item appears twice under the promised mutation model;
- every eligible stable item is eventually returned;
- ordering is total and monotonic;
- a cursor cannot cross tenant or filter scope;
- invalid tokens fail with a documented client error;
- query work remains bounded for late pages.
Pagination bugs rarely live on page one. The API contract becomes visible only when data changes between requests. Design that change explicitly, and the cursor becomes a dependable continuation rather than an encoded accident.