SQL Reference ยท Transactions and Performance
SQL pagination strategies
Concept guide to SQL pagination: LIMIT/OFFSET paging, why deep pages get slow, and keyset (seek) pagination with a stable ORDER BY and tie-breaker.
Pagination means returning a big result a page at a time. There are two main techniques. Offset pagination (LIMIT n OFFSET m) is easy and lets users jump to page 40, but the database must still produce and throw away the first m rows, so deep pages get slower. Keyset pagination (also called the seek method) remembers the last row of the previous page and asks for rows after it, which an index can do in constant time per page.
Whichever you use, pagination is only meaningful with a deterministic ORDER BY. Without one, the database may return rows in any order and pages can overlap or skip rows.
-- Offset: page 3 with 10 rows per page SELECT id, name FROM products ORDER BY id LIMIT 10 OFFSET 20; -- Keyset: the 10 rows after the last id seen SELECT id, name FROM products WHERE id > 30 ORDER BY id LIMIT 10;
Offset pagination
Press Run (or Ctrl + Enter) to see the result here.
For page p with size rows, use OFFSET (p - 1) * size. Its weaknesses:
- Cost grows with the offset.
OFFSET 100000reads and discards 100,000 rows first. - Unstable under change. If a row is inserted or deleted on an earlier page while the user is reading, the next page repeats or skips a row.
- A tie in the sort key (many products with the same price) makes the order ambiguous. Add a unique tie-breaker, usually the primary key:
ORDER BY price, id.
Keyset pagination
Instead of counting rows to skip, filter on the sort key of the last row you showed. With a single unique key it is a simple comparison.
Press Run (or Ctrl + Enter) to see the result here.
When you sort by a column that can repeat, compare the pair (sort key, id). The standard way is a row-value comparison, which SQLite supports since 3.15:
Press Run (or Ctrl + Enter) to see the result here.
Where row values are not available, spell it out:
WHERE price > 29.99 OR (price = 29.99 AND id > 7)
An index on (price, id) makes this a direct seek into the index followed by reading 5 entries, no matter how deep the page is.
Press Run (or Ctrl + Enter) to see the result here.
Going backwards and counting
- Previous page: reverse the comparison and the sort, then reverse the 5 rows in the application:
WHERE id < 6 ORDER BY id DESC LIMIT 5. - Total pages:
COUNT(*)over a big table is itself expensive. Show "next / previous" only, estimate, or cache the count. - "Is there another page?" Ask for
size + 1rows; if you get the extra row, there is a next page. Show onlysizeof them.
Comparing the techniques
| Offset | Keyset | |
|---|---|---|
| Jump to page N | Yes | No (only next/previous) |
| Cost of deep pages | Grows with offset | Constant with a matching index |
| Stable when rows change | No | Yes |
| Complexity | Trivial | Needs a unique, indexed sort order |
| Sorting by many columns | Easy | Harder; every key joins the comparison |
| NULLs in the sort key | Normal ordering | Awkward; avoid or use COALESCE |
Dialect differences
-- PostgreSQL, MySQL, SQLite SELECT id FROM products ORDER BY id LIMIT 10 OFFSET 20; -- SQL standard, PostgreSQL, Oracle 12c+, SQL Server 2012+ (needs ORDER BY) SELECT id FROM products ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY; -- SQL Server older style SELECT TOP (10) id FROM products WHERE id > 20 ORDER BY id;
Row-value comparison (a, b) > (x, y) works in PostgreSQL, MySQL and SQLite; MySQL may not use an index for it as effectively, so the expanded OR form is safer there. SQL Server and Oracle do not support it; use the expanded form.
Usage notes
- Always include a unique column at the end of
ORDER BY. - Do not trust page numbers in URLs for long feeds. Pass an opaque cursor (the last key values) instead.
- Match the index to the sort. The index column order and direction should equal the
ORDER BY. - Deep offsets are rarely useful. Few users go to page 5,000; limit the maximum page or switch to search.
- Filters stay in the query. Apply the same
WHEREfilters on every page and put the cursor condition on top.