Prefer keyset pagination, also called cursor pagination: order by a stable unique key, return an opaque cursor for the last row and query for rows after it. Offset pagination makes the database read and discard every skipped row, so page five thousand is slow, and rows inserted or deleted mid scroll cause duplicates and gaps. Keyset stays constant time per page and is stable under writes.
Why interviewers ask this
It is a small design decision that separates people who have run an API at scale from people who have not. The interviewer wants the two specific failures of offset, cost and instability, plus awareness that a cursor must encode a deterministic sort. Follow ups usually probe the tricky parts: sorting by a non unique column, jumping to an arbitrary page, and total counts, which are expensive and often not needed.
How to structure your answer
- Give the recommendation first, then justify it.
- Name both offset failures: deep page cost and shifting results.
- Explain what the cursor encodes and why it needs a tiebreaker.
- Acknowledge what you lose, such as jumping to page fifty.
Example answer
I default to keyset. The client asks for a page, gets back items plus an opaque cursor, and the next request says give me rows after this cursor. Under the hood that is a where clause on the sort key, so with the right index it is an index seek and the cost is the same on page one and page a thousand. Offset cannot do that, because the database still walks and throws away everything before the offset, and worse, if a row is inserted while someone scrolls, every subsequent page shifts and they see a duplicate or miss an item entirely. The detail that catches people is the tiebreaker: if I sort by created at and two rows share a timestamp, the order is not deterministic, so the cursor encodes created at plus the id and the comparison is on the pair. What I give up is arbitrary page jumps and cheap total counts. For an infinite scroll feed nobody misses those; for an admin table that needs page numbers I will use offset with a bounded maximum.
Walking into this interview soon? GhostPilot listens to your live call, spots the question the moment it is asked, and puts a structured answer on your screen in real time. Try it on your next mock, or grab a $29 Session Pass, no subscription, for the real thing.
See how it worksFollow-up questions to expect
- How would you support sorting by a user chosen column with a cursor?
- How do you return a total count without scanning the table?
- What should the cursor contain, and should clients be able to decode it?
Related backend developer questions
Your interviewer will ask their own version of this. Paste your actual job description into the free Question Predictor and get the 20 questions that role is most likely to ask, with what each one is really probing.
Predict my questions