Keyset Pagination
Keyset pagination fetches the next page by a sort-key cursor — where (created_at, id) after the last seen values — keeping every page an index seek regardless of depth.
Definition
Keyset pagination (seek method) pages through results using an ordering cursor: the query filters for rows strictly greater than the tuple of the last row seen, ordered by the same tuple. Unlike offset pagination, whose cost grows with skipped rows, each page costs an index range seek. It requires a deterministic, indexed ordering.
Context and scope
Used for infinite scroll, API cursor pagination, exports, and large dataset iteration where offset degradation makes deep pages unusable.
Common misconceptions
- Cursors are opaque tokens — they usually encode the sort tuple; making them arbitrary loses the seek property
- Keyset pagination sorts by a unique column alone — non-unique sort keys need a tiebreaker column for determinism
- It cannot jump to an arbitrary page — it is forward and backward sequential by design; random access needs different machinery
Anti-patterns
- Sorting by created_at alone with duplicate timestamps and shipping pages that repeat or skip rows
- Encoding the cursor with an offset number and calling it keyset while retaining linear degradation
- Sorting by a column the composite index does not lead with and falling into a sort node on every page
Related concepts
Related examples
Authoritative source
What it is
OFFSET 10000 LIMIT 20 scans and discards ten thousand rows first — page fifty costs fifty times page one. Keyset pagination instead remembers the last row of the previous page and asks for whatever sorts after it. The database seeks into the index and reads exactly twenty rows.
How it works
The cursor is the value tuple of the ordering columns from the last delivered row: WHERE (created_at, id) > (last_created, last_id) ORDER BY created_at, id LIMIT 20. The tiebreaker makes ordering total. The matching composite index turns the predicate into a range start. Encoded cursors carry the tuple, often signed or encrypted to prevent client tampering.
When to use it
Any unbounded feed: infinite scroll, activity streams, exports that cannot hold a transaction open. APIs expose it as opaque next cursors. Backward paging uses a mirrored predicate and ordering.
Pitfalls
Filtering with other predicates can invalidate the simple range shape; the planner may still choose well if indexes align. Stable pagination under concurrent inserts means new rows may or may not appear on later pages — acceptable for feeds, contractual for ledgers. Sorting by mutable columns lets rows move between pages and be seen twice or never.