# 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: ```sql -- 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](https://www.linkjob.ai/) runs mock technical interviews with real-time hints.