Keyset Pagination: Why OFFSET Gets Slow and What to Use Instead
Most APIs start with LIMIT 20 OFFSET 40. It's simple, and it works until the table gets big or the data changes while people are paging. Keyset pagination (sometimes called seek pagination) fixes both problems.
The problem with OFFSET
- It gets slower the deeper you go. To return page 5,000 the database still reads and throws away the first 100,000 rows. The cost grows linearly with the offset, even with a good index.
- It skips and duplicates rows. If a new row is inserted at the top while a user moves from page 1 to page 2, every row shifts down one place, and the user sees the last item of page 1 again. Deletes cause the opposite: a row is silently skipped.
How keyset pagination works
Instead of "skip N rows", you say "give me the rows after the last one I saw", using an indexed, unique sort key:
-- first page
SELECT id, created_at, title FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- next page: pass the last row's (created_at, id)
SELECT id, created_at, title FROM posts
WHERE (created_at, id) < ('2026-11-14 10:02:11', 98231)
ORDER BY created_at DESC, id DESC
LIMIT 20;
With an index on (created_at, id) the database jumps straight to the right place in the B-tree, so page 5,000 costs the same as page 1. Inserts and deletes elsewhere don't shift the window, because the cursor points at a value, not a position.
The id tie-breaker matters: created_at alone isn't unique, and two rows with the same timestamp could be skipped or repeated at a page boundary.
Exposing it in an API
Encode the last row's sort values into an opaque cursor (for example base64 of JSON) and return it as next_cursor. Clients send it back unchanged. Keeping it opaque lets you change the sort key later without breaking clients.
Trade-offs
- No "jump to page 37". Keyset gives you next and previous, which fits infinite scroll and feeds but not numbered page links.
- Sorting by arbitrary user-chosen columns needs a matching composite index for each sort order you support.
- Previous-page queries flip the comparison and the sort direction, then reverse the results in the application.
- The total count still needs a separate (often approximate) query.
In interviews
"Design a feed" or "design a paginated API" questions often probe this. Explain why OFFSET degrades, show the tuple comparison with a tie-breaker, mention the opaque cursor, and name the lost random-page access as the trade-off. If you want to practise saying that clearly under time pressure, Linkjob AI runs mock technical interviews with real-time hints.