Pagination divides a changing dataset into several observations. A cursor can make traversal efficient, but it cannot by itself decide whether those observations belong to one snapshot or a live view. I would define what may be missed, repeated or newly included before selecting LIMIT, OFFSET or a continuation token.
Begin with the user's traversal
A person scrolling a live activity feed may accept that new events appear only after refreshing the first page. A billing export may require every eligible record exactly once from a fixed cutoff. An administration table may need convenient page numbers more than a stable historical view. These are different products, even if each endpoint accepts a page size. I would ask what the caller intends to do with the complete traversal and what changes are allowed while it is in progress. The answer determines whether a moving view is acceptable.
PostgreSQL's LIMIT and OFFSET documentation emphasises predictable ordering for consistent subsets and notes that skipped rows still need to be computed. That makes a useful starting point, but deterministic ordering within one query does not freeze the data between queries. Elasticsearch's pagination guidance makes the separate consistency concern explicit through search_after and point-in-time searches. My interpretation is that an API should expose its traversal contract in terms users can understand: a live continuation, a bounded snapshot or a resumable export, with a clear lifetime for any retained state.
References: [1] PostgreSQL LIMIT and OFFSET[2] Elasticsearch: Paginate search results
A total order needs a tie-breaker
Ordering only by created_at is incomplete when several records share a timestamp. The database may return equal-valued rows in different orders across plans or requests, so a cursor containing only the timestamp can skip or repeat records at the boundary. Adding a unique immutable identifier produces a total order within the selected dataset. The order must be consistent across the query, index and cursor comparison. Timestamp precision, null handling and collation also matter when they affect how values compare. A display-formatted timestamp is not necessarily a lossless cursor value.
For the illustrative PostgreSQL query below, created_at and id are both non-null, id is unique within the tenant, and created_at does not change after insertion. The descending tuple comparison seeks records strictly below the last returned pair. The tenant predicate remains part of every query; possession of a cursor does not authorise access to another tenant. This is a keyset continuation over a live dataset unless the surrounding API also provides a consistent snapshot. The SQL solves ordering and efficient seeking under its assumptions, not every form of pagination consistency.
SELECT id, created_at, title
FROM posts
WHERE tenant_id = $1
AND (created_at, id) < ($2::timestamptz, $3::bigint)
ORDER BY created_at DESC, id DESC
LIMIT $4;References: [1] PostgreSQL LIMIT and OFFSET
A live cursor handles some mutations better than others
With immutable ordering keys, inserting a new record above the current cursor does not shift the remaining keyset boundary the way it can shift an OFFSET. The new record simply belongs before the position already traversed. Deleting a previously seen record also does not require the client to subtract one from a numeric position. Those properties make keyset pagination attractive for feeds. However, a record inserted later with an older ordering key may still appear in the remaining traversal, and a record deleted before it is reached will be absent.
Updates to ordering keys are more troublesome. A record can move from below the cursor to above it and be missed, or move from an already visited position to a later position and be seen again. Filtering changes create similar effects even when the order stays fixed: an item becoming eligible halfway through the traversal may or may not appear depending on its key. I would not describe keyset pagination as an exactly-once walk of a changing collection. Its contract should say which fields are immutable and whether the caller must tolerate movement or duplication.
References: [2] Elasticsearch: Paginate search results
A snapshot spends resources to buy a stable view
A point-in-time search or database snapshot can make successive pages refer to one consistent view, subject to the system's documented semantics and retention limits. Elasticsearch recommends a point in time for preserving index state across search_after requests. That mechanism has a lifecycle: the client must carry the appropriate continuation state, renew or finish within supported limits and handle expiration. I would treat the snapshot identifier as a capability with bounded scope and lifetime rather than an eternal bookmark into the dataset.
Holding old views can retain storage or other resources that current readers no longer need. A large export lasting hours may therefore deserve a dedicated job that materialises its result or records a durable cutoff and membership set. The export can then page through that stable artifact without holding an interactive database transaction open indefinitely. This adds preparation and storage cost, but it gives a clearer recovery contract. The choice is a trade between freshness, resource retention and resumability; a long opaque cursor does not remove those costs merely by hiding them from the client.
References: [2] Elasticsearch: Paginate search results
The token must bind the whole query
A continuation token should bind the ordering position to the filters, sort order, tenant scope and relevant query version. Otherwise a client can accidentally reuse a cursor from one search in another and obtain a confusing partial view. An opaque server-side token can store this state indirectly; a self-contained token can encode it and use an integrity check. Encoding JSON as base64 provides no integrity by itself. I would validate the authenticated caller and authorisation scope independently on every page rather than treating a valid token as permanent permission.
Versioning matters because query semantics can change during a deployment. If a new release changes collation, status interpretation or default filters, an old cursor may no longer describe the same traversal. The API can reject it with a clear restart response, preserve the older interpretation for a bounded period or use a materialised snapshot whose meaning is already fixed. Silent reinterpretation is the risky option. Error responses should distinguish an expired snapshot from a malformed token or revoked access so a well-behaved client can choose whether to restart, request a new export or stop.
References: [2] Elasticsearch: Paginate search results
OFFSET remains useful inside the right boundary
The counterargument to cursor-first design is that people often want to jump to page twenty or see a simple total page count. OFFSET is straightforward for small, stable or explicitly approximate result sets, and replacing it can complicate the interface without meaningful benefit. Its cost grows when the database must compute and discard many preceding rows, and changes before the offset can alter which records appear. Those are concrete limitations, not a reason to declare every use incorrect. I would keep it where the workload and consistency expectations make those limitations acceptable.
Total counts deserve a separate contract too. A count computed before the first page can drift from later pages in a live view, and an exact count can be more expensive than fetching the next small batch. The product might show an estimate, omit the count or compute it asynchronously. Backward navigation also needs deliberate ordering logic rather than blindly reversing a comparison. If the endpoint supports both directions, test equal timestamps and boundary records in each direction. Small inconsistencies there can make a user believe records have disappeared even when the underlying database is sound.
References: [1] PostgreSQL LIMIT and OFFSET
Test an entire traversal while the data changes
I would test a dataset containing many tied timestamps, then insert, delete and update records between page requests. For a snapshot contract, compare the complete traversal with the fixed expected membership. For a live contract, assert the documented ordering and duplication rules rather than an impossible frozen result. Include empty pages, the exact page-size boundary, expired tokens and a deployment that changes query version. Testing only the first page cannot reveal most pagination bugs because the important state is carried from one request to the next.
The final design should say what the cursor remembers, what remains live and how long the promise lasts. Efficient seeking is valuable, but correctness comes from matching that memory to the caller's purpose. A feed can prioritise responsiveness and refreshability; an export can prioritise a reproducible cut of the data. I would make those separate endpoints or explicit modes when their guarantees differ materially. Pagination becomes much easier to operate when it is treated as a consistency protocol with a resource budget, rather than a decorative pair of query parameters.
References: [1] PostgreSQL LIMIT and OFFSET[2] Elasticsearch: Paginate search results
Sources and further reading
- PostgreSQL LIMIT and OFFSET
Primary documentation explains ordering requirements and OFFSET work. The tenant-scoped SQL and mutation schedules are illustrative examples with stated assumptions.
- Elasticsearch: Paginate search results
Primary documentation distinguishes search_after traversal and point-in-time consistency. Token design and product contract recommendations are this essay's interpretation.