SQL Pagination Explained: OFFSET vs Keyset, With a Million-Row Test

An admin screen lists orders twenty at a time. It is fast on page 1. Someone clicks “last page” on a table with a million rows, and the database grinds. Meanwhile, a mobile feed shows the same post twice because new posts arrived between scrolls. Both problems come from the most common way to paginate: LIMIT and OFFSET.

We built a SQLite 3.50 table with one million posts and an index on (created_at, id), then timed both techniques, averaging repeated runs. All numbers below are from that test on one machine; absolute times on your database will differ, but the pattern is the same.

What is pagination?

Pagination splits a large result into smaller pages, so an app loads twenty rows instead of a million. It keeps responses fast, reduces memory use and network traffic, and gives users something they can actually read. Every list screen, search result and API collection needs it.

LIMIT and OFFSET

-- page 3, 20 rows per page
SELECT id, created_at, title
FROM posts
ORDER BY created_at, id
LIMIT 20 OFFSET 40;

The offset is (page - 1) * page_size. It is easy to understand, supports jumping straight to any page number, and every database supports it. SQL Server and Oracle use the standard form OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY, which means the same thing.

Always order by something unique

Without ORDER BY, SQL does not promise any order, so page 2 might repeat rows from page 1. Ordering by a column that is not unique, such as created_at alone, has a quieter version of the same problem: rows that share a timestamp can swap places between queries. Add a unique tie-breaker, usually the primary key, so the order is fully determined. Our sample data happened to have no shared timestamps, but real tables almost always do. This is the same principle behind the ordering rules in window functions.

Why deep pages get slow

OFFSET does not jump to a position. The database must produce and throw away every skipped row. Page 50,000 means reading 999,980 rows to return 20. The execution plan showed this clearly:

OFFSET: SCAN posts USING COVERING INDEX ix_created
KEYSET: SEARCH posts USING COVERING INDEX ix_created (created_at>?)

“SCAN” means walking through the index from the start; “SEARCH” means jumping directly to a position. Reading the plan, as described in our query optimization guide, tells you which one you have.

Keyset pagination

Keyset pagination, also called seek or cursor pagination, remembers where the previous page ended and asks for rows after that point:

-- first page
SELECT id, created_at, title FROM posts
ORDER BY created_at, id
LIMIT 20;

-- next page: pass the last row's (created_at, id)
SELECT id, created_at, title FROM posts
WHERE (created_at, id) > (?, ?)
ORDER BY created_at, id
LIMIT 20;

The row-value comparison (created_at, id) > (?, ?) means “later timestamp, or same timestamp with a higher ID”. PostgreSQL, MySQL 8, and SQLite support it. Where it is not available, write it out: created_at > ? OR (created_at = ? AND id > ?). With an index on the same columns in the same order, the database jumps straight to the right spot, no matter how deep the page.

Our million-row results

First, selecting only indexed columns, so the index alone could answer the query:

Then with SELECT *, including a 200-character body column that is not in the index, which is closer to a real list screen:

OFFSET time grew roughly in proportion to the offset, while keyset stayed flat. Both methods returned the same first row, ID 999,961, for the deepest page, so the results were identical; only the cost differed. Thirty-eight milliseconds sounds small, but it is over a thousand times slower than the first page, it repeats for every request, and on bigger tables and busier servers it grows into seconds.

Drift: duplicates and gaps

OFFSET has a correctness problem too. We read page 1 with three rows, inserted a new row that sorts first, then read page 2:

page 1:                  [1, 2, 3]
-- a new row is inserted at the top
page 2 (OFFSET 3):       [3, 4, 5]

Row 3 appeared twice, because everything shifted down by one. Deleting a row does the opposite and silently skips one. In feeds and exports, this means duplicate items or missed records. Keyset pagination does not have this problem: page 2 asks for rows after row 3, so new rows earlier in the order cannot shift it.

Trade-offs

A middle ground for OFFSET is the deferred join: first page through IDs using a covering index, then fetch full rows only for those IDs. It reduces the cost of skipped rows, as our first table showed, but does not remove it.

Designing a paginated API

Most large public APIs use cursor-based pagination. A typical response looks like this:

{
  "data": [ ... 20 items ... ],
  "next_cursor": "eyJjIjoxNzAwMDYwMDAwLCJpIjo5OTk5ODB9",
  "has_more": true
}

The cursor is the last row’s sort values, encoded, often as Base64 JSON, so clients treat it as an opaque token. Clients pass it back as ?cursor=... to get the next page. Keep a maximum page size, validate cursors, and consider signing them so clients cannot craft their own. Our REST API best practices cover more of the response design.

Total counts

“Page 3 of 50,000” needs a total count, and SELECT COUNT(*) on a large, filtered table can cost more than the page itself. Options include showing “more results” instead of an exact total, caching counts, or using an estimate from database statistics. Infinite-scroll interfaces usually skip totals entirely.

Testing pagination

• Request every page and check that the union equals the full result, with no duplicates.
• Test the last page, an empty table, and a page size larger than the table.
• Insert and delete rows between page requests, and check what clients see.
• Use rows that share the same sort value to prove the tie-breaker works.
• Time a deep page on production-sized data, not just page 1.

Boundaries such as the last page and empty results are classic places for bugs, as our guide to boundary value analysis explains.

Which to use

• Small tables or admin screens with page numbers: OFFSET is fine.
• Large tables, feeds, infinite scroll, exports and public APIs: keyset.
• Both needs: keyset for “next” and “previous”, with limited page-number jumps or search to narrow results.

Final thoughts

LIMIT and OFFSET are perfect until tables grow and data changes underneath the user. Keyset pagination costs a little more design, a unique sort key, a matching index and a cursor, but it stays fast at any depth and never shows the same row twice. Measure a deep page on realistic data before deciding which one your feature needs.

Frequently asked questions

Keep learning