What is the difference between offset and cursor pagination?
Offset pagination uses page number and size (LIMIT 20 OFFSET 40), which is simple but slow on deep pages and shifts when rows change. Cursor pagination continues after the last item seen, so it stays fast and stable.
Explain it simply
Offset pagination is like saying "skip the first 40 results, show the next 20". Cursor pagination is like a bookmark: "continue after this item".
Offset problems:
- Slow for deep pages: the database still walks over the skipped rows, so OFFSET 1000000 is costly.
- Unstable: if a new item is inserted while the user is on page 2, items shift and page 3 repeats one or skips one.
Cursor (keyset) pagination: the client sends the last seen sort key, and the query is "rows after this key". With an index it jumps straight to the spot, so speed does not depend on depth, and inserts do not cause repeats.
Trade-offs: no jumping to page 50 directly, and sorting needs a unique, ordered key (add the id as tie-breaker). Cursors are often returned as opaque encoded strings.
Use offset for small admin tables where page numbers matter. Use cursors for feeds and large datasets.
Example
-- offset: reads and discards 1,000,000 rows
SELECT * FROM posts ORDER BY id DESC LIMIT 20 OFFSET 1000000;
-- cursor: starts at the index position after id 4821
SELECT * FROM posts WHERE id < 4821 ORDER BY id DESC LIMIT 20;What to say in the interview
"Offset pagination is simple but gets slower with depth and can duplicate or skip items when data changes. Cursor pagination uses the last seen key, so it is fast and stable, but doesn't allow jumping to arbitrary pages. I use cursors for feeds and large tables."