Cursor pagination under concurrent writes is a consistency contract, not merely a base64 replacement for offset. A client traversing a changing collection can see duplicates, miss records, or receive a surprising order when new rows arrive, sort values change, or rows are deleted. Keyset pagination improves performance and stability only when the API defines a unique total order, encodes the correct boundary, and states whether traversal represents a live view or a bounded snapshot.
PostgreSQL's LIMIT and OFFSET guidance requires a unique order for predictable subsets and notes that skipped rows still must be computed. Offset also identifies a position, not a record boundary; inserts before that position shift later pages. A cursor should instead carry enough state to resume after a known ordered record, while remaining opaque to clients and bound to the query that created it.
Build the six-stage pagination consistency path
Write the ordering contract first. Choose business-facing sort fields and append a unique immutable tie-breaker. createdat DESC, id DESC is a total order if id is unique and both values used for traversal do not change. updatedat DESC is not stable when updates move records between pages. If users need that view, acknowledge live reordering or freeze an as-of boundary. Never assume timestamps are unique, even at microsecond precision.
Specify null placement and collation. Names can compare differently by locale or database configuration; nullable ranks need an explicit before-or-after rule. The keyset predicate must match the exact tuple ordering, directions, and null semantics. For descending creation order, the next-page predicate is typically lexicographic less-than on (created_at, id), not independent comparisons joined casually with AND. Test equal primary sort values heavily.
| Model | Cursor carries | Concurrent insert behavior | Best for | Tradeoff |
|---|---|---|---|---|
| Live keyset | Last ordered tuple and query identity | New rows before the boundary are not inserted into later pages | Feeds and interactive browsing | Traversal is not a complete point-in-time export |
| High-water bound | Last tuple plus maximum tuple or sequence at start | Rows beyond the bound wait for a later traversal | Incremental sync with append-heavy data | Updates and deletes still need policy |
| Snapshot token | Snapshot or version identifier plus boundary | All pages read one logical snapshot | Audits and consistent export | Snapshot retention and server state cost |
| Offset | Numeric position | Earlier writes shift page membership | Small immutable lists | Poor scaling and weak stability |
| Seek by ID | Last unique ID only | Stable if ID order matches desired semantics | Monotonic logs | Cannot express arbitrary user sort |
Implement the keyset predicate and matching index
Translate the order into a seek predicate. For ORDER BY createdat DESC, id DESC, fetch the next page where (createdat, id) < (:cursorcreatedat, :cursorid) using datastore-supported row comparison or its logically equivalent disjunction. Request pagesize + 1 records to determine whether another page exists, return only the requested size, and build the next cursor from the last returned edge. Do not infer continuation from a separate count that races with writes.
Create an index whose leading columns, direction, filters, and tenant scope support the query. The planner may use a matching index to retrieve limited ordered rows without sorting the entire result. Validate with realistic cardinality and skew, not a tiny fixture. A cursor design can be semantically correct yet operationally fail if each page scans a large range, performs a broad sort, or cannot use the tenant predicate.
Choose live, bounded, or snapshot semantics
For an activity feed, a live view is often correct: new items belong at the beginning and a client can refresh to see them. For a financial export, a stable snapshot may be required. For incremental synchronization, a high-water mark can bound records eligible for this run, followed by a separate change mechanism for later updates. State the model in API documentation because clients cannot infer it from an opaque token.
Database transactions rarely span user think time safely. Holding one transaction open across minutes of pagination consumes resources and may retain old versions. Prefer a datastore snapshot identifier designed for external continuation, a materialized export, a versioned query service, or a high-water rule. Include snapshot expiry in the cursor contract and return a recoverable error when the retained view no longer exists.
| Mutation | Live keyset outcome | Snapshot outcome | Design decision |
|---|---|---|---|
| Insert before current boundary | Not seen on later pages | Not present if after snapshot | Client refresh or next sync discovers it |
| Insert after boundary | May appear on a later page | Not present if after snapshot | Accept live inclusion or use a high-water bound |
| Delete unseen row | Row is absent | May remain visible if snapshot preserves it | Decide whether tombstones are required |
| Delete boundary row | Resume still works if cursor stores values | Snapshot follows retained view | Never require boundary row lookup |
| Sort key changes toward front | Record may be missed in this traversal | Position remains at snapshot | Use immutable traversal key or change log |
| Sort key changes toward back | Record may appear twice | Position remains at snapshot | Deduplicate client-side only as a supplement |
Make cursors opaque, bound, and tamper-evident
Encode version, direction, ordered values, filter or query fingerprint, tenant scope, snapshot or high-water value, and expiry as needed. Then authenticate the token with a server-side key or store server-side state under a random identifier. Base64 alone is not security. A client must not be able to change tenant, expand a filter, extend expiry, or seek to values it was not authorized to query. Avoid sensitive plaintext because cursors commonly enter logs, analytics, and browser history.
Version the cursor format independently of the API where practical. During key rotation or format migration, accept a bounded set of prior versions and always emit the newest. Return a specific invalid-cursor response for malformed, tampered, expired, query-mismatched, and unsupported-version tokens without revealing cryptographic detail. Do not silently restart at page one; that creates duplicates that look like valid continuation.
Handle deletes and mutable sort keys deliberately
A cursor should contain boundary values rather than only a row ID that must be looked up. If the boundary row is deleted, the next query still seeks from its stored tuple. For synchronization, deletion may need a tombstone or change stream; absence from a page cannot prove deletion. Soft deletion can preserve position but changes authorization and retention concerns, so it is not a pagination fix by itself.
If users sort by a mutable field, decide whether duplicates and omissions are acceptable during a live traversal. Alternatives include freezing the sort value for the traversal, using an immutable event sequence, maintaining a versioned search index, or exposing change tokens rather than list pagination for synchronization. Client-side deduplication by ID reduces visible duplicates but cannot recover a record missed because it moved behind the cursor.
Implement backward traversal symmetrically
Backward traversal is not next-page logic with a different parameter name. Seek on the opposite side of the boundary, reverse database order to retrieve the nearest prior records efficiently, then restore the public order before returning edges. The connection should preserve the same ordering semantics in both directions. Define hasPreviousPage and hasNextPage from bounded queries rather than guesses based on cursor presence alone.
Test round trips: fetch forward several pages, walk backward, and compare edge identities under no writes and controlled writes. Include ties, nulls, first and last pages, page size one, empty filters, expired snapshots, deleted boundaries, and changed authorization. Property-based tests can generate ordered datasets and mutations to assert no duplicates for immutable keys and no results outside the requested scope.
Give clients a clear recovery contract
Cursors can expire because snapshots age out, signing keys rotate, indexes or filters change, or the server retires a format. Return an error that distinguishes restart-required from temporary failure. For interactive lists, restarting at the beginning is reasonable. For large exports, provide a resumable export job. For synchronization, use durable checkpoints or change tokens with defined retention rather than asking a client to rescan millions of records silently.
Observe page latency, scanned-to-returned ratio, invalid cursor classes, snapshot age, empty continuation pages, and duplicate reports. Log a safe cursor fingerprint and query version, not the whole token. During an ordering change, invalidate old cursors explicitly or support both contracts until expiry; interpreting old values under new ordering is worse than a clear restart.
Key takeaways
- Define a unique total order with immutable tie-breakers, null rules, and collation.
- Use a lexicographic keyset predicate and a matching index.
- Choose live, high-water, or snapshot semantics from the user workflow.
- Store boundary values so deletion of the boundary record does not break continuation.
- Authenticate opaque cursors and bind them to tenant, filter, direction, version, and expiry.
- Use change tokens or exports when pagination cannot provide required synchronization consistency.
Cursor pagination FAQ
Is a base64-encoded offset a cursor?
It is opaque in appearance but retains offset behavior: concurrent inserts shift positions and large offsets may remain expensive. A useful keyset cursor resumes from stable ordered values and is bound to the query.
Can updated_at be the only sort key?
Not safely for a complete traversal. It is nonunique and mutable. Add a unique tie-breaker, then decide how movements caused by updates are handled, or use a change log designed for synchronization.
Should the API return a total count?
Only if the product needs it and can define its consistency and cost. A count taken separately can differ immediately from page membership. Approximate, snapshot-bound, or omitted counts are often more honest for large mutable collections.
Conclusion
Cursor pagination remains stable under writes only when its consistency model is explicit. Start with a total order, seek by the full tuple, support it with an index, bind the opaque token to the query, and define mutation and expiry behavior. For workflows that demand a complete point-in-time result, provide snapshot or export semantics rather than presenting a live cursor as stronger than it is.