Compare offset pagination with cursor (keyset) pagination for a list endpoint. When is offset acceptable?
Key points
- Offset/limit is simple and supports jumping to page N, but the database still scans and discards offset rows, so deep pages get slowIn plain words: Skipping to page fifty is easy to ask for, but the database still walks past every earlier row, so far pages are slow
- Inserts or deletes between requests shift offsets, causing skipped or repeated itemsIn plain words: If rows are added or removed while you page, everything shifts and you see an item twice or miss one
- Cursor pagination encodes the last seen sort key and uses an indexed WHERE clause, giving constant cost per page and stability under writesIn plain words: A cursor is a bookmark that says continue after this item, so every page costs the same and new rows do not shift it
- Cursors need a unique, stable sort order (tie-break on id) and make arbitrary page jumps hardIn plain words: The bookmark only works if the order never ties, and you cannot jump straight to an arbitrary page
- Offset is fine for small, slowly changing lists and admin tables, cursors for feeds and anything infinite-scrollIn plain words: Use page numbers for short lists that rarely change, and bookmarks for feeds and endless scrolling
Model answer
Offset pagination
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 40000;
The database must still produce and throw away 40,000 rows before returning 20, so page 2,000 is far slower than page 1. Worse, if a new post is inserted between two requests every subsequent offset shifts by one and the client sees a duplicate; a deletion makes it skip one. Its virtues are simplicity and the ability to show "page 7 of 52".
Cursor (keyset) pagination
SELECT * FROM posts
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;
The client sends back an opaque cursor that encodes the sort key of the last item it saw. With an index on (created_at, id) the query seeks directly to that position, so every page costs the same and concurrent inserts do not shift anything. The tie-break on id is essential whenever the sort column is not unique.
Costs of cursors. No random access to page N, no total count without a separate query, and the cursor must be opaque (base64 of the key, optionally signed) so clients do not depend on its structure. Sorting by something that changes, like score, is tricky: use a snapshot timestamp or accept minor instability.
When offset is acceptable. Admin tables with a few thousand rows, search results where users rarely go past page 5, or any list that is small and changes slowly. For feeds, logs, messages and infinite scroll, use cursors.
Say the performance problem, the correctness problem, the keyset fix with its index, and the limitations you accept.
Likely follow-ups
- How do you encode a cursor so clients cannot tamper with it?
- How would you paginate a result set sorted by a non-unique column like score?