Skip to content
Tutorials Center
My learning

Topics

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.

SQL
-- 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

SQL Page 1 and page 2 with LIMIT and OFFSET ยท database: shop

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 100000 reads 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.

SQL Next page after id 5 ยท database: shop

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:

SQL Keyset on price with an id tie-breaker ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Where row values are not available, spell it out:

SQL
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.

SQL The plan confirms an index seek ยท database: shop

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 + 1 rows; if you get the extra row, there is a next page. Show only size of 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

SQL
-- 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 WHERE filters on every page and put the cursor condition on top.