Pagination: Offset vs. Keyset

This page is a vendor-neutral overview of why deep offset-based pagination degrades and how keyset (seek method) pagination avoids that cost across SQL and NoSQL stores. Unlike the SQL, MongoDB and Couchbase reference sections on this site, no single reference book or specification underpins it: the content was generated with the assistance of AI from general knowledge, and every concrete claim should be verified against each engine’s own current documentation before being relied on in production.

This section’s bibliography lists the reference material consulted while preparing these pages.

Every store on this site offers two different ways to page through a result set: skip a number of rows/documents/ hits to reach a page (offset pagination), or carry forward a pointer to the last row seen and ask for the next ones after it (keyset, also called seek-method or cursor-based, pagination). They look interchangeable for page one, and diverge sharply by page one thousand. This page explains why, describes the general keyset pattern once, and links out to how each technology on this site implements it.

Why Large Offsets Are Slow

OFFSET in SQL, skip() in MongoDB, start in Solr and from in Elasticsearch all share the same underlying cost model: the engine cannot jump straight to row/document/hit N of a result set, because nothing indexes "the Nth match" directly. Instead it has to walk the ordered result set from the beginning, locate and materialize every row up to the offset, and then discard all of them before it can start returning the page the caller actually asked for. The work spent producing and throwing away the skipped rows is pure waste, and it is not optional — the engine cannot know a row belongs before the offset without first evaluating and counting it.

That means the cost of a page grows roughly linearly with the offset itself, independent of how small the page size is: page 2 is cheap, page 2,000 does two thousand times the discarded work of page 1, and on a search engine like Elasticsearch, deep enough offsets are refused outright (the default index.max_result_window caps from
size at 10,000) rather than left to degrade silently. This is the shared root cause behind every one of the store-specific "avoid deep pagination" warnings in the per-technology reference pages linked from Comparison by Data Store — it is not a MongoDB quirk, or a SQL quirk, or an Elasticsearch quirk, it is a property of offset-based paging itself, regardless of which store implements it.

The Keyset (Seek Method) Pattern

Keyset pagination — also called the seek method, since it seeks forward from a known position rather than counting from the start — sidesteps the discarded work entirely by turning "skip N" into "resume after the last key I saw." The pattern is the same shape in every store that supports it:

  • Sort by a stable, indexed key that is unique, or made unique with a tiebreaker (e.g. ORDER BY created_at, id when created_at alone can repeat).

  • On the first page, query with no lower bound and take the first N results, ordered by that key.

  • Record the key value of the last row returned.

  • On the next page, filter with WHERE key > :lastSeenKey (or the store’s equivalent range/cursor construct) instead of skipping rows, and take the next N again.

A generic SQL illustration of the idea:

SELECT id, name FROM products WHERE id > :lastId ORDER BY id LIMIT 20;

Because the WHERE/range predicate on an indexed key is a direct index seek, the cost of fetching any page is independent of how deep into the result set it is — page 2,000 costs the same as page 2. Every store-specific implementation of this idea — MongoDB’s range-query alternative to skip(), Solr’s cursorMark, Elasticsearch’s search_after, GraphQL’s Relay cursor connections, and Spring Data’s Window<T>/ScrollPosition.keyset() — is this same pattern adapted to that store’s query surface; see Comparison by Data Store for exactly how each one expresses it.

Trade-offs of Keyset Pagination

Keyset pagination is not a strict upgrade over offset pagination — it trades away things offset pagination gives for free, and those trade-offs decide which one fits a given screen or API:

  • No direct jump to page N. A keyset cursor only knows how to move to "the page after this one" (or, with a reversed comparison, the page before it); there is no way to compute "page 47" without walking there page by page. Offset pagination is the only option when a UI needs numbered page links or a "jump to page" control.

  • Requires a stable sort key. The ordering column(s) must be unique — or made unique with a tiebreaker column — or rows can be skipped or repeated across pages when values tie. This usually means a composite key (e.g. (created_at, id)) when the natural sort column is not unique on its own.

  • Total counts become a separate concern. Offset pagination’s page/size model pairs naturally with "page N of M," backed by a COUNT()-style query run alongside the page. Keyset pagination has no such query built in: a total count, if the UI needs one, has to be computed separately (an exact COUNT(), or an approximate count from store statistics), and kept in mind as its own cost.

In practice, deep, high-traffic, "next page" style pagination (API result sets, infinite scroll, background exports) is exactly where keyset pagination earns its keep, while shallow, numbered pagination over a small, bounded result set is exactly where offset pagination’s simplicity is still the better trade.

Comparison by Data Store

Store Offset mechanism Keyset/cursor alternative

SQL

OFFSET / FETCH FIRST …​ ROWS ONLY

Seek query ordered by an indexed key

MongoDB

skip()

Range query on an indexed field

Couchbase

LIMIT / OFFSET

SQL++ seek query

Solr

start / rows

cursorMark

Elasticsearch

from / size

search_after

GraphQL

Offset-style arguments

Relay cursor connections

Spring Data

Pageable / Page<T>

Window<T> / ScrollPosition.keyset()

Bibliography

MongoDB

Solr

Elasticsearch

Spring Data