How do LIMIT and OFFSET work, and what is the problem with deep pagination?

How to think about it

The interviewer is checking whether you know the basic syntax and its scaling problem. The good 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 breaks down at scale because the database must read and discard all m rows before returning anything.

Basic usage

-- First page: rows 1-20
SELECT product_id, name, price
FROM products
ORDER BY product_id
LIMIT 20 OFFSET 0;

-- Second page: rows 21-40
SELECT product_id, name, price
FROM products
ORDER BY product_id
LIMIT 20 OFFSET 20;

ORDER BY is mandatory with pagination — without it the row order is non-deterministic and pages will overlap or skip rows.

The deep-pagination problem

-- Page 50,000 at 20 rows each = OFFSET 1,000,000
SELECT product_id, name FROM products
ORDER BY product_id
LIMIT 20 OFFSET 1000000;

The database must locate and discard 1,000,000 rows before returning 20. Query time grows linearly with page number. On a table of 10M rows, late pages can take seconds even with an index.

Keyset pagination (the fix)

-- After last seen product_id = 48392
SELECT product_id, name, price
FROM products
WHERE product_id > 48392
ORDER BY product_id
LIMIT 20;

The WHERE predicate jumps directly to the right place in the B-tree index. Query time is constant regardless of how deep you are. The trade-off: you cannot jump to an arbitrary page number — keyset pagination is cursor-style only.

See both approaches side by side

Run the OFFSET version, then the keyset version and compare what each returns. On a large table, the keyset version would be dramatically faster — here you can see it selects the correct “next page.”

The key insight

With OFFSET 1000000, the database must locate row 1 000 001 by reading and discarding the million rows before it — even with an index. Keyset pagination uses a WHERE id > last_seen_id predicate that the B-tree handles in O(log n) reads, not O(n). That is why every production cursor-based API (Stripe, GitHub) uses keyset, not offset.