memos.page 2 min read raw

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

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

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.