Returning a correct first page does not make pagination correct. Before a client asks for page two, another writer can insert, delete, or update records. A simple OFFSET 20 then points at a different set of positions, producing duplicates, gaps, or a result that drifts throughout the traversal.

Pagination is first a consistency contract: how the collection is ordered, what “after this cursor” means, which concurrent changes are visible, and whether a token can cross filter boundaries. The database statement is only an implementation.

Make the order unique and stable

ORDER BY created_at DESC is insufficient. Several records can share a timestamp, and a query plan does not promise an order among ties. Add a unique tie-breaker to create a total order:

SELECT id, created_at, title
FROM posts
WHERE tenant_id = :tenant_id
  AND (created_at, id) < (:cursor_time, :cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT :page_size_plus_one;

This is keyset pagination. The cursor stores the last returned item’s ordering values, and the next query selects records strictly after that boundary. In a database without row-value comparison, expand the predicate to created_at < t OR (created_at = t AND id < i). An index should begin with fixed filters and continue with ordering fields, such as (tenant_id, created_at DESC, id DESC).

Prefer immutable ordering fields. If a record’s timestamp or score changes between pages, it can cross the boundary and appear twice or disappear. A feed ordered by mutable popularity must either accept live-view behavior or refer to a fixed ranking version.

State where OFFSET is acceptable

PostgreSQL documents two separate concerns: without a unique ORDER BY, different LIMIT/OFFSET queries can return unpredictable subsets; and rows skipped by a large offset still have to be computed. Small administration tables, frozen reports, and “jump to page 37” interfaces can still choose offsets, but they must accept positional behavior under concurrent writes.

For a continuously growing collection, keyset pagination is usually more stable. New records inserted before the first-page boundary do not push old records onto page two, deleted records disappear naturally, and the query continues from an index boundary. The trade-off is that it cannot jump to an arbitrary page or automatically promise an exact total page count.

A cursor is a server protocol, not a database value

A token can logically carry data such as:

{
  "v": 2,
  "boundary": ["2026-11-06T00:00:00Z", "post_123"],
  "sort": "created_desc",
  "filter_hash": "…",
  "snapshot": "rev_8472",
  "expires_at": 1793923200
}

The client returns it unchanged. Google AIP-158 requires page tokens to be opaque and URL-safe, explicitly noting that Base64 is not sufficient obfuscation. Authenticate or encrypt a stateless token to prevent tampering, or map a random stateful token to a server-side record. Do not expose sensitive filters or enumerable internal identifiers.

Bind the token to ordering, filters, parent resource, and tenant. If a later request changes parameters other than page size, return a clear invalid-argument error instead of applying an old boundary to a new collection. A token version permits future encoding and index migrations; expiry bounds the compatibility and storage burden.

A cursor is never authorization. Recheck the caller and resource scope for every page. A signed token proves only that the service issued those bytes, not that the current caller still has access.

Choose a live view or a snapshot view

A live cursor evaluates current data on every request. Records inserted before the boundary after page one do not appear later; deletion does not create an offset-style duplicate; changing an ordering key can still move a record. This is appropriate for a feed when refreshing is the documented way to discover newer content.

A snapshot cursor promises that one traversal sees one collection version. Its token can reference a materialized result, search-index revision, business watermark, or reproducible as_of condition. Do not hold a database transaction open while a person browses for tens of minutes. Use a versioned view with an expiry and define how a client restarts after expiration.

Both semantics are valid; ambiguity is not. Exporting every invoice needs a snapshot or a resumable deduplication strategy. Browsing recent activity can usually use the cheaper live behavior.

Generate continuation correctly

Fetch page_size + 1 items. Return only the first page_size; the extra item proves that another page exists. Build the next token from the last item actually returned, not the probe item. At the end, omit or empty the token. Relay’s Connection model expresses the same idea with edges.cursor, pageInfo.hasNextPage, and endCursor.

Returning fewer than the requested number does not always mean the end. Authorization post-filtering, shard merging, or a backend work budget can produce a short or even empty page. If the collection is not exhausted, still return a continuation token. Totals also need semantics: an exact COUNT may be expensive and a live total changes immediately. Offer an estimate, a snapshot total, or no total.

Backward pagination must preserve the same logical order. A query may scan an index in reverse internally, but the response must not silently reverse business order. The GraphQL Cursor Connections specification explicitly keeps edge order consistent between first/after and last/before.

A REST response can carry next_page_token in its body or use RFC 8288 Link headers with rel="next" and rel="prev". Whatever the representation, there must be one unambiguous end-of-collection signal.

Test the contract under mutation

Do not test only 100 static fixtures. Between page requests, insert records before and after the boundary, delete first and last items, create tied ordering values, update sort keys, switch tenants and permissions, tamper with and expire tokens, and exercise maximum and negative page sizes. A property test can traverse a snapshot and assert that identifiers are unique and the set is complete.

Monitor token parse failures, expiry, page depth, query latency, scanned rows, returned items, and empty pages that still have a token. When releasing a new cursor version, retain the old decoder until observed usage reaches zero.

Stable pagination is not an offset hidden in Base64. It requires a unique order, explicit concurrent-visibility semantics, an opaque token bound to the query, a matching index, and a verifiable end condition. Define that contract first so clients can continue reliably through a collection that never stops changing.