Database and SQL Developer interview question
How do you paginate large SQL result sets?
Prefer keyset pagination for large changing datasets: order by a unique stable tuple and fetch rows after the last seen key. OFFSET scans and discards growing prefixes and can duplicate or skip rows under changes.
Direct answer
What a strong answer covers
How it works
OFFSET pagination asks the engine to produce and discard all the rows before the requested page, so page 1,000 scans a million rows, and if rows are inserted or deleted between requests, items shift and appear twice or vanish. Keyset pagination orders by a unique, stable tuple such as (created_at, id), remembers the last seen values, and asks for rows after them, which uses an index seek regardless of depth and is stable under concurrent changes. It cannot jump to an arbitrary page number, which is rarely needed in feeds and APIs. Cursors encode the last tuple opaquely so clients cannot depend on its shape.
Example
An activity feed API returns 50 events ordered by (created_at DESC, id DESC) and a cursor encoding the last pair; the next request runs WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 50 with an index on those columns, taking 2 ms whether it is page 2 or page 20,000. The admin table that needs page numbers keeps OFFSET but caps it at 100 pages and shows a total count computed separately.
Common mistakes
- Paginating a changing feed with OFFSET and showing duplicates as new rows arrive.
- Ordering by a non-unique column so ties make the page boundary ambiguous.
- Exposing the raw last-row values as the cursor and letting clients depend on them.
A useful answer structure
Answer from your own work.
Do not memorise a script: use only examples you can explain honestly in follow-up questions.
Open the desktop workspace and rehearse a clear answer in your own words.