The cursor must describe a total ordering. A timestamp alone is not unique, so this pattern adds the immutable row identifier as a tie-breaker.
Stable Keyset Pagination
A cursor is a position in a total order, not a page number.
SELECT id, created_at, payload
FROM events
WHERE tenant_id = $1
AND (created_at, id) < ($2, $3)
ORDER BY created_at DESC, id DESC
LIMIT 50;
-- Cursor = base64url(last.created_at + ',' + last.id)Invariant: Every page continues after a unique, immutable ordering key.
Use when: A large or changing table makes OFFSET slow and inconsistent.
Why this boundary matters
OFFSET shifts under inserts and scans discarded rows. A compound cursor preserves traversal position across concurrent writes and deletion of the anchor row.
Failure policy
| Boundary | Action |
|---|---|
| Valid cursor | Continue strictly after its compound ordering key |
| Rows inserted before the cursor | Exclude them from this traversal |
| Cursor row deleted | Continue from the encoded values, not a row lookup |
| Ordering values are not unique | Add an immutable unique tie-breaker |
| Sort definition changes | Version and reject incompatible cursors |
Trade-offs
Keyset pagination is stable and index-friendly but cannot jump to an arbitrary page and needs cursor semantics per sort order. Mutable ordering columns can still move records between pages.
Decision rule: Choose keyset pagination for large or changing ordered datasets when forward traversal matters more than page numbers.