Keyset (cursor) pagination

Definition

This is the pagination approach you use when you want to fetch a list "starting from just after the last item you saw" rather than "page number N". It uses each item's sort-basis value (the sort key) as a coordinate and requests only the rows after (or before) that value.

More precisely, keyset pagination (also known as cursor pagination or the seek method) takes the form WHERE (sort_key, id) < :cursor ORDER BY sort_key, id LIMIT n. Instead of "count from the Nth row" (offset), it uses the index to seek directly to the point the cursor (an opaque token holding the sort key of the last-seen row) points to and reads only the next batch from there.

Why it's needed

Paging with an integer offset creates three problems.

1. It gets slower the further you go. LIMIT 10 OFFSET 10000 makes the DB actually count the first 10,000 rows before handing back 10 — O(offset). The deeper the page, the more the cost grows linearly.
2. Inserts and deletes shift the items. If one new post is added at the very top while you were viewing page 1, then when you request page 2 the last post you just saw appears again (a duplicate). If one is deleted, the opposite happens and a post is skipped (a gap). This breaks especially often on screens like infinite scroll where the list grows in real time.
3. The coordinate "page 3" is itself unstable. When the order changes, the same page number points to different content.

Keyset attaches the coordinate to a value (the sort key of the last-seen item) rather than a page number. No matter what is inserted or deleted in between, "after this value" does not change, so there are no duplicates or gaps; and because it is an index seek, deep pages stay consistently fast at O(log n).

How it works

You use a composite coordinate (k, id), built from the sort key k and a tiebreaker (usually id) to break ties, as the cursor.

-- First page: no cursor
SELECT * FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Next (older) page: use the last-seen row's (created_at, id) as the cursor
SELECT * FROM posts
WHERE (created_at, id) < (:cursor_created_at, :cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

• Cursor encoding: Wrap (created_at, id) in an opaque token such as base64 and hand it to the client. This keeps the client from depending on the internal structure, so changing the sort scheme later does not break the API.
• Bidirectional (before / after): An infinite list can grow both downward (older items, after) and upward (newer items, before). For before, flip the comparison operator and the ORDER BY direction to query, then reverse the result order again before handing it to the client (to match the on-screen display order).
• Index: A (sort_key, id) composite index must exist for the seek to be O(log n). Without the index, keyset falls back to a full scan too.

Practical application

The typical contract for an infinite-scroll API:

GET /api/items?cursor=<opaque>&dir=after&limit=20

200 → { items: [...], nextCursor: "<opaque>", prevCursor: "<opaque>" }

• The first request starts with no cursor (firstPageParam = null). A bidirectional list manages two separate cursors, front (prevCursor) and back (nextCursor).
• When the client scrolls up, use dir=before&cursor=prevCursor; when it scrolls down, use dir=after&cursor=nextCursor.
• Validate input at the boundary: an unparseable cursor or an out-of-range limit is rejected immediately with 400. Silently falling back to the first page keeps the client from noticing it lost its position.

When combining with an infinite query like React Query's, have getNextPageParam/getPreviousPageParam return the response's nextCursor/prevCursor as-is, and set a cap on retained pages (maxPages) to evict old pages (keeping memory bounded).

Trade-offs

Keyset's biggest cost is that arbitrary page jumps are impossible. If you need UI like "go straight to page 5", offset or a hybrid is better.

When not to use

• If a numbered pagination UI (1 2 3 … jump to 10) is a requirement, keyset is not the right fit.
• Screens where the sort criteria change freely on every request — a cursor is bound to a specific sort (k, id), so changing the sort means discarding the cursor and starting over from the beginning.
• If the list is small and rarely changes, offset's simplicity is the win (YAGNI).

Common mistakes

• Missing tiebreaker: if the sort key has ties but you do not put a unique value like id in the cursor, rows get skipped or duplicated at the boundary. Always use a (sort_key, unique_id) composite coordinate.
• No composite index: without a (sort_key, id) index, the seek degrades to a full scan and keyset's performance advantage disappears. Actually check the query plan.
• Not reversing order in the bidirectional case: if you hand back the before query result without reversing it, items stack up backwards on screen.
• Silently falling back on a bad cursor: returning the first page on a parse failure means the user does not realize they lost their position — fail explicitly with 400.
• No maxPages cap: in append-only infinite scroll, stacking pages without limit makes the heap keep growing.

Related concepts

• fractional-index-ordering — a different approach to making a stable sort key that does not disturb neighbors on insert
• list-virtualization-windowing — render only as much of a keyset-fetched page as is visible on screen
• virtual-list-scroll-restore-authority — restore the back-navigation/reload position after a bidirectional fetch