Cursor Pagination at Scale

Pagination is not primarily about splitting an array. It is about continuing a database query after the world may have changed. Offset pagination hides that fact behind page numbers: asking for offset 100,000 tells the database to locate and discard a large prefix, while inserts or deletes before that offset shift the meaning of the next request.

A cursor should instead represent a boundary in one stable, total order. The next query asks for rows strictly after that boundary, using the same filters and authorization scope. This avoids scanning every preceding row and gives predictable behavior under many concurrent changes. It does not create a frozen snapshot automatically; the API must still define what mutations clients may observe.

This article develops a cursor API for a tenant-scoped audit log sorted newest first. The design applies to feeds, catalogs, and administrative tables, but the exact ordering, mutation policy, and indexes must come from each workload rather than a reusable base64 recipe.

Turn the Sort into a Total Order #

Suppose the first query sorts audit events by created_at DESC. Timestamps are not unique, so created_at alone defines groups but not an order within each group. A database may return tied rows in any order, and a cursor containing only the timestamp will either skip the remaining ties or return some of them again.

Add a unique, immutable tie-breaker:

ORDER BY created_at DESC, id DESC

The pair (created_at, id) is now a total order. If the last row on a page has values (:last_created_at, :last_id), the next page contains tuples lexicographically below it:

SELECT id, actor_id, action, created_at
FROM audit_events
WHERE tenant_id = :tenant_id
  AND action = ANY(:actions)
  AND (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT :page_size_plus_one;

For a database without row-value comparison, expand the predicate without changing its meaning:

AND (
  created_at < :last_created_at
  OR (created_at = :last_created_at AND id < :last_id)
)

Every ORDER BY component needs a corresponding cursor value and boundary comparison. If the API sorts by severity descending, then time descending, then ID descending, the cursor needs all three. Mixed directions require a predicate derived term by term; copying a tuple < comparison is correct only when the database comparison direction matches every component.

Choose deterministic null semantics too. Prefer non-null sort columns. Otherwise define whether nulls come first or last and encode a non-null rank expression in both ordering and boundary. Collation matters for text: a locale or database upgrade can change ordering. Numeric IDs and normalized timestamps make safer tie-breakers than a mutable display name.

The tie-breaker must be immutable. An event ID works because it never changes. A row’s updated_at can be the primary sort key only if the product accepts that editing a row moves it and may make an in-progress traversal see it twice or not at all.

Make the Cursor Opaque and Query-Bound #

A cursor is server state serialized for a later request. Opaque means clients must return it unchanged and cannot rely on its fields. Base64 encoding alone is not integrity protection; a client can decode and edit it. A practical cursor payload might contain:

{
  "v": 2,
  "order": "created-desc-id-desc",
  "boundary": {
    "createdAt": "2026-06-06T10:14:32.481Z",
    "id": "evt_01842"
  },
  "filterHash": "sha256:8f2c...",
  "tenant": "tenant_17",
  "direction": "after"
}

Serialize it canonically, then append an HMAC or use an authenticated-encryption scheme. HMAC prevents modification but leaves decoded values visible. Authenticated encryption also hides values when timestamps, tenant identifiers, or filters are sensitive. Either approach needs a key identifier and rotation plan. Enforce a small decoded size before parsing so the cursor endpoint cannot become an unbounded decoder.

Bind the token to normalized query identity. The filter hash should cover every option that affects row membership or order: action filters, search mode, sort choice, stable feature variant, and perhaps requested locale. Normalize equivalent inputs first by sorting set-valued filters and applying defaults. If a client changes filters while sending an old cursor, return a stable cursor_query_mismatch error rather than continuing a different relation from a meaningless boundary.

Tenant and actor scope deserve care. Including the tenant in a signed cursor prevents accidental cross-tenant reuse, but the server must still authorize the current request. A cursor is not a capability unless the API explicitly designs it as one. Permission changes between pages should take effect, and row-level policy must be applied on every query.

Version cursor formats. The decoder can support current and recently issued versions while deployments overlap. Reject unknown versions cleanly. Expiry is useful when old sort semantics, retention, or authorization assumptions should not live forever, but it changes the client experience. If cursors expire after an hour, document that they are navigation tokens, not durable bookmarks.

Walk Through Inserts, Deletes, and Ties #

Assume page size three and this canonical order:

The first query returns 109, 108, and 107; it fetches one extra row to establish that more data exists. The next cursor contains the boundary (10:14:32, evt_107). The strict predicate returns evt_103 before moving to the earlier timestamp. No tied row is lost.

Now a new evt_110 arrives at 10:16:00 before the second request. It is above the boundary, so page two does not include it and existing rows do not shift. A client refreshing from the beginning will see it. This is usually the desired feed behavior: one forward traversal moves toward older records while new records accumulate above it.

If evt_108 is deleted, the second query is unchanged because the cursor contains values, not a foreign key that must still exist. If the boundary row evt_107 is deleted, the strict tuple comparison still works. A cursor should not require looking up its boundary row.

If evt_103 is deleted before page two, the result simply contains fewer historical rows overall. Cursor pagination prevents offset shifts, not deletion visibility. If audit records are immutable, that case may be prohibited by domain policy. For mutable catalogs, the API should state that deleted items disappear from traversal.

The difficult case is changing an ordering value. Suppose evt_101 is edited so its timestamp becomes 10:17:00 after page one. It moves above the boundary and page two will not see it; a refresh will. If a previously returned row moves below the boundary, a later page may return it again. No cursor encoding can repair a relation whose order changes without retaining a snapshot or change history.

Choose one of three contracts: accept live-view anomalies for mutable sort fields, sort by immutable creation keys, or materialize a snapshot/result set for consistent traversal. A high-water mark such as the maximum ID from page one can exclude later inserts, but it does not freeze updates or deletions. Long-lived database snapshots across independent HTTP requests are usually expensive and operationally fragile. Exports that require exact consistency are often better modeled as asynchronous jobs over a captured snapshot.

Keep Filters and Search Semantics Stable #

Pagination boundaries only make sense within the same ordered set. Apply filters before the boundary and include their normalized identity in the cursor. Tenant scope, visibility rules, status, category, and soft-deletion policy all affect membership even when they do not appear in the ORDER BY clause.

Filter changes can occur without a client changing parameters. An event may be relabeled, a product may move from active to archived, or a user’s permissions may change. Decide which changes are immediate. Security filters must be current on every page even if that creates gaps. Product filters can use immutable values or a snapshot identifier when repeatable traversal matters more than freshness.

Full-text relevance is a challenging order because scores can tie and can change when the index or corpus changes. Add a unique tie-breaker and pin any search-index generation needed for stable results. If the search engine exposes a native continuation token, treat it as backend-specific state and wrap it with API query identity and integrity protection. Do not translate it into an offset under the hood.

Avoid free-form client sort expressions. Offer named orders such as newest, oldest, or price_ascending, each with a reviewed tuple, cursor schema, and index strategy. This keeps boundary generation auditable and prevents injection. Page size is independent query input: cap it server-side, and either bind it into the cursor for consistent pages or allow it to vary while keeping the same boundary semantics.

Data retention can remove everything after an old cursor. Returning an empty final page is valid. If the cursor references a retired index generation or an ordering no longer supported, return an explicit expiration error and let the client restart. Silently interpreting an old token under new semantics can produce plausible but incorrect results.

Traverse Backward Without Reversing the API #

Backward navigation uses the same canonical display order. If the first row on the current page is boundary B, rows on the previous page are lexicographically greater than B. However, fetching the nearest N greater rows requires reversing the database scan:

SELECT id, actor_id, action, created_at
FROM audit_events
WHERE tenant_id = :tenant_id
  AND action = ANY(:actions)
  AND (created_at, id) > (:first_created_at, :first_id)
ORDER BY created_at ASC, id ASC
LIMIT :page_size_plus_one;

Reverse the returned rows in application code before presenting them, so every response remains newest first. Querying greater rows in descending order would select the very beginning of the entire relation rather than the page immediately before the boundary.

Define cursor names by relation, not screen direction. after means after the last row in canonical display order; before means before the first. A response can emit a next cursor from its last row and a previous cursor from its first row. Empty pages need no invented boundary.

hasNextPage is established by fetching N + 1 rows in the requested direction and discarding the extra row. hasPreviousPage is not automatically known on an initial forward request. The server can perform an existence query, infer only what the supplied navigation proves, or return cursors without Boolean promises. Choose an API contract that does not manufacture certainty to match a familiar response shape.

Deletion between backward requests can make the reconstructed pages contain fewer items or change which items neighbor a boundary, but strict comparisons still avoid dependence on the boundary row. Backward and forward traversal should be symmetric in tests: moving forward two pages and then back one should return the prior page when the underlying ordered relation has not changed.

Build Indexes That Match the Access Path #

Cursor pagination is fast only when the database can seek to the boundary and scan in order. For the audit query, a useful index is:

CREATE INDEX audit_events_tenant_action_created_id_idx
ON audit_events (
  tenant_id,
  action,
  created_at DESC,
  id DESC
);

Equality-constrained columns lead, followed by ordered boundary columns. The database can scan a B-tree in either direction, so the same index can often support backward traversal. Included columns may enable an index-only plan, but wider indexes cost storage, cache space, and write amplification. Add them based on measured read needs, not by copying every selected field.

An action = ANY(...) filter with many values can complicate ordered scanning because results from several index ranges must merge. A leading (tenant_id, created_at, id) index may be better when action filters are broad, with action checked during the scan. A separate index may serve one highly selective filter. There is no universal column order: inspect representative cardinalities and query plans.

Run EXPLAIN with early, middle, and old boundaries, small and maximum page sizes, common filters, and skewed tenants. The plan should show an index range scan near the cursor, not a large sort or a scan proportional to traversal depth. Offset latency grows with the requested position; a correctly indexed cursor query should depend mainly on page size and filter selectivity.

Beware expressions that break index alignment. Casting timestamps, applying functions to sort columns, using a different collation, or ordering by a computed value not present in the index can force sorting. Keep cursor values in the database column’s exact precision. Truncating a microsecond timestamp in JSON recreates ties that the database comparison does not share.

Too many named sort and filter combinations can imply too many indexes. That is a product tradeoff. Restrict expensive combinations, provide search-specific infrastructure, or accept a slower path with explicit limits. Cursor pagination removes deep-offset waste; it does not make every arbitrary query cheap.

Define API Errors and Operational Boundaries #

Decode cursors through one strict component. Verify outer encoding, size, authentication tag, version, required fields, data types, timestamp precision, recognized order, direction, and filter identity. Map failures to stable client errors without revealing whether a guessed tenant or boundary exists. Never splice decoded column names or operators into SQL; map named orders to static query builders.

Set maximum page size and execution deadline. A malformed or extremely selective filter can scan many index entries before producing a page even when the boundary is indexed. Monitor rows examined relative to rows returned, database time, result size, and cancellation. Rate-limit expensive traversals where appropriate, but do not cache authorized pages under a key that omits actor and filter scope.

Cursor format deployments require overlap. New servers may issue version 3 while old instances still receive the next request. Either all instances decode both versions or issuance waits until the fleet is compatible. Keep retired verification keys for at least the cursor lifetime. Record version and named order in telemetry, but avoid logging complete tokens.

Useful metrics include page latency by named order and filter class, rows returned, empty-page rate, invalid and expired cursor counts, direction, database rows scanned, and query timeout. Sudden query-mismatch errors can identify a client incorrectly retaining cursors after filter changes. A rise in old-page latency may indicate an index or planner regression.

Retention and privacy policy apply to cursor payloads too. Signed plaintext can expose tenant IDs or search terms through browser history and logs. Encrypt sensitive state or store a short random handle to server-side cursor state, accepting the storage and expiry tradeoff. Keep cursor URLs out of analytics systems that do not need them.

Test Ordering as an Invariant #

Example-based tests should cover empty sets, one row, exactly one page, one extra row, many equal timestamps, minimum and maximum IDs, every named filter, maximum page size, and both directions. Assert that concatenating an unchanged forward traversal yields every expected row exactly once in canonical order.

Use a simple in-memory sorted-and-filtered model as an oracle. Generate rows with deliberately colliding sort values, paginate the database implementation with random page sizes, and compare the complete sequence. Then inject mutations between requests and assert the documented live-view contract: newer immutable inserts do not appear after an older boundary, deletion does not invalidate a cursor, and sort-key mutation may create the explicitly accepted anomaly.

Security tests should modify each cursor byte, swap tenant or actor context, change filters while retaining the token, use an unknown version, exceed decoded limits, and test key rotation. Database tests must preserve timestamp precision across serialization. Backward property tests should compare adjacent pages under an unchanged dataset and verify response order after reversing the ascending scan.

Load tests should not merely request page one repeatedly. Seed realistic tenant skew and traverse to old boundaries while concurrent inserts occur. Capture plans and rows examined. Test retention removing the boundary row, index creation or migration, and rollout with mixed cursor versions. Alerting and runbooks should distinguish invalid client tokens from slow valid queries.

Cursor pagination works when its invariant is visible: every token names a strict boundary in one authenticated query and one total order. Immutable tie-breakers prevent ambiguous ties, matching indexes make the boundary seekable, and explicit mutation semantics tell clients what continuation can and cannot promise. The token is the smallest piece; the real design is the ordered relation around it.