The full answer
Use LIMIT and OFFSET (or OFFSET ... FETCH) for small tables and when users jump to page numbers, and keyset pagination (also called seek pagination) for large tables, infinite scrolling and APIs, because its cost stays the same however deep the page.
1. Offset pagination
SELECT order_id, order_date, total
FROM orders
ORDER BY order_date DESC, order_id DESC
LIMIT 20 OFFSET 40; -- page 3
-- SQL Server: ORDER BY ... OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY
- Simple, and supports "go to page 50".
- The database still reads and discards all skipped rows, so page 5,000 is much slower than page 1.
- Rows inserted or deleted between requests shift the pages, so users can see duplicates or miss rows.
2. Keyset (seek) pagination Remember the last row of the current page and ask for rows after it:
SELECT order_id, order_date, total
FROM orders
WHERE (order_date, order_id) < (DATE '2026-09-30', 81234) -- last row of the previous page
ORDER BY order_date DESC, order_id DESC
LIMIT 20;
- With an index on
(order_date, order_id), each page is a short index range read, whatever the depth. - Stable when rows are added: no duplicates or gaps.
- Include a unique column (here
order_id) in the sort, or rows with equal dates can be skipped or repeated. - Can't jump to an arbitrary page number; it fits "next" and "previous" navigation and cursors in APIs.
- Row-value comparison works in PostgreSQL and MySQL; in SQL Server write it out:
WHERE order_date < @d OR (order_date = @d AND order_id < @id).
3. ROW_NUMBER() filtering
SELECT *
FROM (
SELECT order_id, order_date, total,
ROW_NUMBER() OVER (ORDER BY order_date DESC, order_id DESC) AS rn
FROM orders
) numbered
WHERE rn BETWEEN 41 AND 60;
This was common before databases supported OFFSET. It still numbers every row up to the page, so it costs about the same as OFFSET; it is no faster for deep pages.
4. Supporting techniques
- Index the sort columns; without an index every method sorts the whole result.
- Return a cursor token (the encoded last key) from APIs instead of page numbers.
- Avoid exact total counts on huge tables (
COUNT(*)reads everything); show "more results" or an estimate.
Choosing: offset for small admin screens and page numbers, keyset for large or fast-changing data and anything exposed as an API.
Last updated 16 October 2026
Sources