Keyset pagination for SaaS APIs: preserve order at the page boundary

Keyset pagination preserves a page boundary by continuing after the last ordered key, not after a changing row offset. Choose a deterministic order with a unique tie-breaker, encode the complete boundary and validate its scope. It avoids offset shifts caused by newly inserted earlier rows, but it does not automatically provide a frozen snapshot or prevent duplicates when existing sort keys change.

Make ordering part of the API

For a descending message list, order by created_at DESC, id DESC. Timestamp alone is insufficient when several rows share the same time. The next page uses a lexicographic comparison on both fields and the same ordering. Define null handling, sort direction and tie-breaker type explicitly. Mixed directions or nullable keys need a predicate that matches that exact ordering rather than a copied tuple comparison.

GitLab's keyset pagination documentation explains its unique ordering requirements and cursor behavior. Its library examples are Rails-specific, but the boundary principle transfers to a PostgreSQL API. Use your own schema and driver contract, and inspect the supporting index and query plan before making a performance claim.

GitLab: keyset ordering and cursor design

An executed equal-key example

Our isolated Python fixture models ordered pairs of synthetic time keys and IDs: (3,5), (3,4), (3,3), (2,2), (1,1). Page one is (3,5), (3,4). A newer row (4,6) is inserted before requesting page two. OFFSET 2 now returns (3,4) again, while continuation below the boundary (3,4) returns (3,3), (2,2). A full traversal of the unchanged list returns IDs 5,4,3,2,1 exactly once.

-- Proposed PostgreSQL shape; tuple-list fixture ran, SQL did not.
SELECT id, created_at, content
FROM fixture_messages
WHERE workspace_id = $1
  AND (created_at, id) < ($2, $3)
ORDER BY created_at DESC, id DESC
LIMIT $4;

The fixture demonstrates ordering and offset shift, not database isolation or speed. The first page needs a query without the boundary predicate. Fetching page_size plus one can establish whether another page exists, provided the endpoint returns only the requested page and forms the next cursor from the last returned row. Do not accidentally use the extra lookahead row as the boundary and skip it.

Bind the cursor to a query contract

A cursor can contain a version, sort identity and last key values. Validate its length, types and supported version before binding parameters. Associate it with the current tenant and filter contract; a cursor from active-only results should not silently continue an all-status query. Base64 encoding is not authorization or integrity protection. Use a signed opaque token or server-side state when tamper resistance is part of the contract.

Keep access checks on every request. A valid cursor must not grant access to a record after membership changes. If cursor expiry is required, return a clear restart instruction. Avoid exposing sensitive sort fields in a client-readable cursor when an opaque representation is appropriate. The cursor is a continuation hint under current authorization, not a credential for the dataset.

Define concurrent change behavior honestly

An insertion newer than the current descending boundary belongs before the remaining traversal and will not appear later in that walk. That can be correct for a feed. A report that needs a stable snapshot requires an additional snapshot or watermark design. Updates that move existing rows across the sort boundary can still cause duplicates or omissions; prefer immutable ordering keys where possible and document what happens when rows are deleted.

Before release, test equal timestamps, page size one, malformed cursors, changed filters and concurrent inserts in the actual database. Verify a matching index under the real tenant predicate and role. The saved tuple fixture is a useful deterministic mechanism check; it is deliberately not presented as a PostgreSQL benchmark or a guarantee of snapshot consistency.

SaaS data imports: turn the first CSV into a safe launch

Observability for small teams: start with the failed customer task

Bring one API or data workflow and a sanitized failure example for a bounded implementation assessment.

AptixLabs saas development