Offset vs cursor pagination
Every list API starts the same way: ?page=3&size=20, which becomes LIMIT 20 OFFSET 40. It is
easy to build, easy to read, and it breaks in two separate ways that interviewers like to probe.
It gets slower the deeper you go, and it skips or repeats rows when the list changes
while someone is paging through it. A social feed, a chat history or an orders table being written
to every second hits both.
Cursor pagination, built on a keyset, asks for "the next 20 after this row" instead of "20 rows starting at position 40". That one change fixes both problems, and it costs you the ability to jump to page 37.
You scroll an Instagram-style feed. While you read the first 20 posts, 3 new posts are published at the top. With ?page=2 (OFFSET 20), what do you see next, and why?
The two ways OFFSET breaks#
It gets slower with depth. OFFSET 100000 does not jump to row 100,000. The database finds
the first 100,020 rows in order, throws away 100,000 of them, and returns 20. An index on the sort
column helps it walk in order, but it still walks. Page 1 is fast, page 5,000 reads a hundred
thousand index entries and fetches far more rows than it returns.
It drifts when rows move. Positions shift every time a row is inserted or deleted ahead of where the reader is.
On a feed this looks like a bug: the same three posts twice. On an admin export or a sync job that pages through a table, it is worse. Skipped rows mean a job that "processed everything" quietly missed records, and nobody notices until a customer asks where their invoice went.