Cursor Pagination Under Concurrent Writes: A Consistency Contract

Specify the traversal the client is buying

An activity feed, an administration table, and a financial export may all display fifty rows at a time. They do not need the same consistency contract. A feed can tolerate new items appearing above the current position. An export may need a complete, reproducible set with no duplicates or omissions.

Before choosing a cursor format, decide whether the client is browsing a changing collection, traversing records below a boundary, or reading a fixed snapshot. Write this into the API contract. Pagination bugs often begin when a live feed is implicitly treated as an audit export.

PostgreSQL requires a predictable ordering for meaningful limited result sets and notes that skipped offset rows still have to be computed. Large offsets can therefore become expensive even when the page size is small. PostgreSQL LIMIT and OFFSET

Order by an immutable tuple

For a descending activity feed, use a non-null creation timestamp plus a unique immutable ID as the tie-breaker. Timestamp alone is insufficient because many records can share it. The continuation query must use the same tuple and direction as the ordering.

SELECT id, created_at, event_type
FROM activity_events
WHERE workspace_id = $1
  AND (created_at, id) < ($2::timestamptz, $3::uuid)
ORDER BY created_at DESC, id DESC
LIMIT 51;

CREATE INDEX activity_feed_idx
ON activity_events (workspace_id, created_at DESC, id DESC);

This example fetches one extra row to determine whether a next page exists. The next cursor comes from the last row actually returned, not the extra row. Use a separate first-page query without the continuation predicate.

The index is a starting point, not a universal guarantee. Additional filters, skewed workspace sizes, and selective predicates can change the plan. Inspect representative execution plans and row counts before declaring the endpoint fast.

Bind the cursor to the query

A cursor should encode a format version, ordering values, direction, and a normalized query fingerprint. The fingerprint can include workspace, filters, sort order, and relevant snapshot identity. Reject reuse with a different query rather than returning a plausible but incorrect page.

Sign the cursor to detect tampering. Encryption is a separate decision if its contents reveal sensitive values; base64 is neither signing nor encryption. Bound decoded size, validate types, enforce supported versions, and avoid placing raw SQL fragments inside the token.

Permissions must be checked on every page. If access changes between requests, the API should remove unauthorized records even if that means an earlier estimate of total results is no longer accurate.

Understand what concurrent writes can still change

With immutable descending keys, new records above the current boundary do not shift the next page the way offsets do. But edits to sort keys can move records across the boundary. Deletes remove records. Updates to other fields change what the client sees.

PostgreSQL Read Committed uses a new snapshot for each statement. Repeatable Read can retain a transaction snapshot, but separate HTTP requests do not automatically share a transaction. Holding database transactions open while users browse introduces its own operational costs. PostgreSQL transaction isolation

An upper timestamp boundary is also not a complete snapshot. A late commit can insert a record with an earlier timestamp after the traversal began. Do not advertise fixed-snapshot semantics merely because the cursor carries a start time.

Give exports their own architecture

For reproducible exports, create a job with an authorized scope and a defined source snapshot. Materialize the result or its identifiers in a controlled transaction, then stream pages from that stable result. Recheck download authorization and expire the artifact.

Materializing identifiers alone fixes membership, not field values. If the export must reproduce exact values, store those values or retain a source version that can reconstruct them.

Test boundaries that ordinary fixtures miss

Create many rows sharing one timestamp. Delete the final row of the previous page. Insert a row between page requests. Change a permitted filter while reusing the cursor. Attempt another workspace's cursor and a token with an unsupported version.

Verify that traversal matches the documented contract, query plans stay bounded at deep positions, and malformed cursors produce a controlled client error. Measure scanned rows as well as response time; a warm cache can disguise a query that will collapse at production scale.