Pagination: offset, cursor and keyset

Why OFFSET pagination gets slow and skips items, cursor and keyset pagination done right, opaque cursors, sorting by score, pagination over sharded data and feeds, and what to return to clients.

Reading is half of it. See this used in a real interview: walk through Design a News Feed →

Every list endpoint needs pagination, and how you do it decides whether page 500 takes 2 ms or 2 seconds, and whether users see duplicates when new items arrive. It looks like a detail; interviewers use it to check whether a candidate has run a real feed or search API.

Offset pagination

SELECT … ORDER BY created_at DESC LIMIT 20 OFFSET 400 returns page 21.

Pros: trivial to implement, supports "jump to page 37", and the total count is easy to show.

Cons:

• Slow at depth. The database still reads and discards the first 400 rows. At offset 1,000,000 it reads a million rows to return twenty.
• Unstable under writes. If three new items are inserted at the top while a user is on page 1, page 2 starts three items earlier and repeats three items they already saw. Deletions make items silently disappear.

Offset is fine for small, slowly changing lists (an admin table of a few thousand rows). It is wrong for feeds, timelines and anything large or live.

Keyset (cursor) pagination

Instead of "skip 400", say "give me the items after the last one I saw":

SELECT … FROM posts
WHERE (created_at, id) < ($last_created_at, $last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20

• Fast at any depth: with an index on (created_at, id), every page is an index seek plus 20 rows. See database indexing.
• Stable: new items at the top do not shift the next page, because the boundary is a value, not a position.
• Needs a unique, ordered key: created_at alone is not unique; ties at the same timestamp would skip or repeat items. Add the id as a tie-breaker. Time-ordered ids (Snowflake, UUIDv7) can serve as both. See unique ids, ordering and time.

Cons: no "jump to page 37" (only next and previous), and totals are expensive.

Opaque cursors

Do not expose the raw values; return an opaque cursor that encodes them:

GET /v1/feed?limit=20
→ { items: [...], next_cursor: "eyJ0IjoxNzI3Njg5MDAwLCJpZCI6ODgxMn0" }

GET /v1/feed?limit=20&cursor=eyJ0IjoxNzI3Njg5MDAwLCJpZCI6ODgxMn0

The cursor is base64 of { t, id } (optionally signed or encrypted). Clients treat it as a token, so you can change the sort key or add fields later without breaking them. Return next_cursor: null at the end instead of making clients guess from a short page.

Sorting by something other than time

For lists sorted by a score (hot posts, search relevance, rating), the cursor must encode the sort value and a tie-breaker: (score, id). Two subtleties:

• Scores change between pages. An item whose score rises may jump above the cursor and be missed, or fall below and appear twice. Options: snapshot the ranked list for the session (store the first few hundred ids and page through them), accept small drift, or deduplicate on the client by id.
• Ranking per request (personalised feeds) usually computes a few hundred candidates, caches them for the session, and pages through that cached list. See Design a News Feed.

Pagination over sharded data and merged feeds

When items come from several shards or sources (a home feed merging many timelines, search across shards), each source returns its next items after the cursor, and the service merges and returns the top N. The cursor must then encode a position per source, or a single sort value that every source can seek to. This is why feed systems prefer one global, time-ordered id: the cursor is one number, and every shard can seek to it.

Totals and counts

SELECT COUNT(*) over a large filtered set is often slower than fetching the page. Options: show "more than 1,000", show an estimate (from statistics or a search engine’s approximate count), or maintain a counter separately. Ask whether the product really needs an exact total; usually it does not.

Infinite scroll versus pages

Infinite scroll suits cursors perfectly. Numbered pages suit offset. If the product wants numbered pages over a large set, cap the depth (search engines stop at a few hundred results) and use offset only within that window.

Pagination in other places

• Search engines: deep offset pagination is expensive across shards; use "search after" cursors (Elasticsearch’s search_after) or a point-in-time snapshot.
• Key-value stores: DynamoDB and Cassandra return a continuation token (the last key evaluated); pass it back for the next page.
• Chat history: page backwards from a message id; on reconnect, fetch everything after the last seen id. See Design Slack.

Checklist

• Cursor (keyset) pagination for anything large or live.
• A unique, ordered sort key with a tie-breaker, and an index that matches it.
• Opaque cursors and an explicit end signal.
• How score-sorted lists stay stable across pages.
• Per-source positions or a global sort key for merged and sharded lists.
• Whether totals are really needed.