Databases
Keyset pagination: interview questions and how to answer them
Keyset pagination asks for rows after a known position rather than skipping a count, so page cost is constant and results stay stable while data changes.
Written and reviewed by Sahil Srivastav
What it actually is
Keyset pagination — also called seek or cursor pagination — identifies the next page by the sort-key values of the last row seen, rather than by a count of rows to skip. The query becomes "give me rows ordered after this point", which an index can satisfy with a single seek.
The contrast with OFFSET is not a tuning difference, it is a different algorithm. OFFSET n does not jump to row n; it instructs the database to produce rows in order, count off and discard the first n, then return the rest. Page 10,000 therefore performs roughly 10,000 pages of work and throws nearly all of it away.
There is a second defect that matters just as much and gets less attention. Offsets are positional, so a row inserted or deleted before your current position shifts everything. Users see a row twice across consecutive pages, or never see it at all. Keyset cursors name a position in the data rather than a count, so concurrent writes cannot shift them.
Why it matters in production
Because the failure arrives exactly where it is least visible. Page one is instant, which is what everyone tests. The degradation is proportional to depth, so it surfaces first for crawlers, export jobs and sync processes that walk every page — which are also the clients least likely to complain, so the load grows silently until it is the dominant query on the table.
And because the correctness problem is genuinely user-visible. A list that duplicates and skips rows during normal paging looks like a data bug and is nearly impossible to reproduce on demand, because it depends on concurrent writes landing between two requests. Teams usually spend significant time chasing it before recognising it as a property of offset paging.
How it works
Row-wise comparison does the seeking
The clean formulation is a tuple comparison: WHERE (created_at, id) < ($1, $2). SQL compares tuples lexicographically — first element, then second as a tiebreaker — which is exactly the index order. An index on (created_at DESC, id DESC) turns this into one seek plus a sequential read of the page, with no sort and no discarded rows.
The sort key must be unique, or pages break
If you order by a non-unique column alone, rows sharing a value have unspecified relative order, and a cursor sitting among them cannot say which were already returned. Appending the primary key makes the key total. This is required even without concurrent writes — it is a correctness requirement, not a robustness nicety.
Stability under concurrent writes
Because the cursor is a data position rather than a count, an insert before it does not shift anything: the next page still starts immediately after the last row you saw. Newly inserted rows that sort earlier simply appear on a page you have already passed, which is honest behaviour rather than a duplicate or a gap.
What you give up
Random access to an arbitrary page number, and easy backwards jumps. You can go forward, and backward with a reversed comparison and ordering, but "page 47" is not expressible. Most product surfaces — feeds, infinite scroll, exports, sync — never needed page numbers; report screens sometimes do, and a bounded offset remains acceptable there.
Opaque cursor tokens
Exposing raw column values in the API couples clients to your sort key and leaks data shape. Encoding the tuple into an opaque token lets you change the underlying key later without breaking clients, and discourages clients from constructing cursors themselves. Sign or validate it, since it is user-supplied input that reaches a query.
Implementing it
Build the index to match the comparison and the ordering exactly, including direction, then confirm with EXPLAIN that there is no Sort node and no large rows-removed-by-filter.
Always include a unique tiebreaker in the sort key. Without it the scheme is subtly incorrect regardless of how the query is written.
Design the API around an opaque next_cursor token rather than a page number, so the implementation stays free to change and clients cannot depend on offsets.
Drop exact total counts where you can. COUNT(*) with a filter visits every matching row and is frequently more expensive than the page itself — an estimate from reltuples or a capped count usually serves the product just as well.
-- Linear in depth, and unstable: a concurrent insert shifts every offset.
SELECT * FROM events
ORDER BY created_at DESC
LIMIT 20 OFFSET 200000;
-- Constant cost at any depth, stable under concurrent writes, resumable.
SELECT * FROM events
WHERE (created_at, id) < ($1, $2) -- last row of the previous page
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- The index must match the comparison AND the ordering, direction included.
CREATE INDEX events_created_at_id_desc_idx
ON events (created_at DESC, id DESC);Interview questions and how to answer them
Why does OFFSET get slower as page depth increases?
Because the skipped rows must be produced before they can be counted off. OFFSET 200000 makes the database generate two hundred thousand rows in order and discard them, so cost grows linearly with depth and the last page is the slowest. No index removes this — it is what the operator means.
Why do rows get duplicated or skipped with offset pagination?
Offsets are positional. A row inserted before your current position shifts everything down by one, so the next page re-serves a row you already saw; a deletion shifts up and skips one. Keyset cursors reference a position in the data, so concurrent writes cannot move them.
Write the keyset query for newest-first ordering.
WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT $3, with an index on (created_at DESC, id DESC). The tuple comparison matches index order so the database seeks once and reads forward; the id makes the key unique so page boundaries are unambiguous.
What do you lose by switching?
Arbitrary page numbers and easy backwards jumps. You move relative to a cursor instead. For feeds, infinite scroll, exports and sync that is no loss at all; for a report screen where users genuinely jump to page 47, a bounded offset is still reasonable. The honest answer names the trade rather than claiming keyset is strictly better.
How do you paginate a full export safely?
Keyset on the primary key. It is the cheapest possible full traversal, constant cost per batch, and it is resumable after a crash — you restart from the last key processed rather than recomputing an offset into a table that has since changed.
Answers that lose the round
- Ordering by a non-unique column with no tiebreaker, which makes page boundaries ambiguous
- Comparing columns separately with AND/OR instead of a tuple comparison, producing a predicate the index cannot seek on
- An index that does not match the ordering direction, leaving a Sort node in the plan
- Exposing raw cursor values, coupling clients to the sort key
- Keeping an exact total count that costs more than the page query
- Offering page numbers in an API over an unbounded dataset, which forces the slow implementation
- Assuming offset paging is merely slow, and missing that it duplicates and skips rows
Practise keyset pagination in a real repository
Gronex ships this as a runnable repository: a listing that degrades with page depth and drops rows under concurrent inserts. The tests assert both bounded work per page and that no row is duplicated or skipped while data changes, so fixing only the speed does not pass.
FAQ
Can I keep page numbers in the UI?
For bounded result sets, yes — offset is fine when the maximum depth is small and known. For anything that grows without limit, exposing page numbers in the API contract forces the slow implementation permanently, which is why the decision belongs at API design time rather than later.
How do I go backwards?
Reverse both the comparison and the ordering — (created_at, id) > ($1, $2) ORDER BY created_at ASC, id ASC — then reverse the resulting rows in the application. It works cleanly; it is just more code than decrementing a page number.
What if users can change the sort order?
The cursor must encode which sort it belongs to, and each sort option needs a matching index with a unique tiebreaker. This is a real cost of flexible sorting, and it is a good reason to offer a small fixed set of sort options rather than arbitrary ones.
Is this the same as what GraphQL connections do?
Yes — the Relay connection spec is keyset pagination with opaque cursors and standardised page info. The encoding and naming are specified; the underlying mechanism and its index requirements are identical.