How do LIMIT and OFFSET work, and what is the problem with deep pagination?
LIMIT restricts the number of rows returned; OFFSET skips that many rows before returning results. Deep pagination with large OFFSET values is slow because the database must scan and discard all skipped rows — keyset pagination on an indexed column is the production-scale alternative.
How to think about it
The interviewer is checking whether you know the syntax and its scaling problem. A strong answer names keyset pagination as the production fix and explains why it’s faster — not just that it is.
LIMIT n OFFSET m returns n rows starting after the first m. Simple to write, but it degrades at scale: the database must read and throw away all m skipped rows before it can return anything. ORDER BY is mandatory — without a deterministic order, pages overlap or skip rows.
A worked example — OFFSET vs keyset, same page
“Page 2, three per page” via OFFSET, then the keyset form that returns the same page by jumping past the last id you saw:
-- OFFSET pagination: page 2 (skip 3, take 3)
SELECT id, name, price FROM products
ORDER BY id
LIMIT 3 OFFSET 3;| id | name | price |
|---|---|---|
| 4 | Monitor | 399 |
| 5 | Headphones | 149 |
| 6 | Webcam | 89 |
-- Keyset: after last seen id = 3
SELECT id, name, price FROM products
WHERE id > 3
ORDER BY id
LIMIT 3;| id | name | price |
|---|---|---|
| 4 | Monitor | 399 |
| 5 | Headphones | 149 |
| 6 | Webcam | 89 |
Identical pages — but the engine reaches them differently, and that difference is the whole point.
Why deep OFFSET is slow
-- page 50,000 at 20 rows each = OFFSET 1,000,000
SELECT id, name FROM products ORDER BY id LIMIT 20 OFFSET 1000000;To return 20 rows, the database locates and discards 1,000,000 rows first — even with an index on id. Query time grows linearly with page depth, so late pages on a 10-million-row table can take seconds.
Keyset sidesteps this. WHERE id > 48392 ORDER BY id LIMIT 20 lets the B-tree seek straight to the cursor position in O(log n) reads, so latency is constant no matter how deep you page. The trade-off: you can only go forward/back from a cursor, not jump to “page 873.” That’s why every production cursor API — Stripe, GitHub — uses keyset, not offset.