Cursor Pagination Without Missing or Repeating Rows

Cursor pagination becomes interesting when the data changes between requests. Returning twenty rows is easy. Explaining which twenty come next, after another user inserts or edits a row, is the actual API design problem.

For a hypothetical activity feed, I would define the contract as “continue after the last position in descending creation order.” That contract intentionally does not promise a frozen snapshot. Making the distinction explicit prevents frontend and backend teams from solving different problems under the same word, pagination.

Give every row an unambiguous position

Consider two activities with the same creation timestamp. Sorting only by created_at cannot tell the client which of those tied rows comes first. PostgreSQL documents that paginated queries require a predictable ordering, and that large offsets still require computation of skipped rows. See its LIMIT and OFFSET documentation.

For this proposed schema, assume created_at and id are non-null, immutable, and the ID is unique. An illustrative next-page query is:

SELECT id, created_at, summary
FROM activity
WHERE tenant_id = $1
  AND (created_at, id) < ($2, $3)
ORDER BY created_at DESC, id DESC
LIMIT $4;

The timestamp and ID together form a position. PostgreSQL supports the row comparison used here, with comparison semantics and null behavior described in Row and Array Comparisons. This example assumes both ordering directions are descending; do not copy the tuple predicate unchanged into a mixed-direction sort.

For the first page, omit the position condition. Fetch one more row than the requested page size, return only the requested count, and use the extra row to decide whether to offer continuation. The next token must describe the last row actually returned, not the extra row.

Prove the boundary with a tiny fixture

I would begin with six synthetic records rather than a million-row benchmark:

Request a page size of two. Page one ends at (12:02, 105). The next query should begin with 104. If it starts with 103, the implementation lost a tied row. If it starts with 105, it used an inclusive boundary and repeated the last row.

Now delete 105 before fetching page two. The continuation should still work because its token describes a position, not a row that must remain present. Insert 107 at 12:04 and continue: under this contract, the new row belongs above the current traversal and appears after a refresh.

Decide what edits mean

Changing an ordering field can move a record across a page boundary. For an activity log, I would avoid mutable ordering fields. For a customer directory sorted by display name, I would explicitly allow a moving view or provide a snapshot-based export instead.

Separate HTTP requests normally do not share one database snapshot. PostgreSQL's transaction isolation documentation explains snapshot behavior within transactions. A cursor token by itself does not create that transaction or preserve a snapshot across requests.

A high-water timestamp can exclude newly created records, but it does not freeze subsequent edits or deletions. If the requirement is a reproducible audit export, design an export job with a documented consistency model. Avoid claiming that a timestamp filter provides snapshot isolation.

Bind the token to the query

I would encode a version, the last position, and a fingerprint of the active filters. If a user changes from “all activity” to “security events,” the old token should be rejected or discarded. Reusing it silently can make a valid query appear to have missing results.

Treat tenant and authorization scope as server decisions. A decoded token is not permission to read another tenant. Opaque encoding is also not encryption: do not put secrets in a base64 token. If clients must not modify token contents, authenticate the token with a server-held signing key and define its expiration policy.

These are application design recommendations. Token signing is useful for integrity, but the SQL authorization predicate remains necessary even when the signature is valid.

Measure the real query, then ship the contract

A candidate index for this example begins with tenant ID and follows the ordering fields. Whether it helps enough depends on the actual distribution, filters, and query plan. Measure with representative tenants and deep continuations; do not publish a generic speed multiplier.

Document forward navigation, refresh behavior, filter changes, and token errors alongside the endpoint. This is the same API-contract concern raised in the structured API discussion. Pair it with the website checklist when translating that contract into navigation controls.

My release criterion is simple: the six-row fixture behaves exactly as documented, the moving-data cases have deliberate answers, and authorization is independent of whatever the cursor claims.