Skip to content

CONCEPT Cited by 1 source

Cursor pagination

Cursor pagination (also known as keyset pagination or seek-method pagination) is a pagination mechanism that uses the value of a sort-key column from the last seen row as the boundary for the next page, instead of using LIMIT / OFFSET:

-- Page 1 (no cursor yet):
SELECT * FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 25;

-- Page 2 (cursor = last row's (created_at, id) from page 1):
SELECT * FROM posts
WHERE (created_at, id) < ($last_created_at, $last_id)
ORDER BY created_at DESC, id DESC
LIMIT 25;

(Source: sources/2026-04-21-planetscale-introducing-fastpage-faster-offset-pagination-for-rails-apps — discussed as the "swap to cursor based pagination" escape hatch for deep offset pagination.)

Why it's O(1) at any depth

Given a secondary index on the sort key, WHERE created_at < $last is a seek operation — the B+tree descent lands directly on the boundary position, and the LIMIT 25 is satisfied by reading 25 adjacent leaf entries. The database does not walk past and discard the first M rows; the predicate itself carries the "where to start" information.

Axis Offset pagination Cursor pagination
Cost to fetch page N O(M + N) O(N)
Cost constant Grows with depth Constant
Index walk entries touched M + N N
Clustered-index hydrations M + N N

The cost is the same regardless of whether it's page 2 or page 200,000 — the engine never touches the rows it's skipping because there's nothing to skip: the predicate already filtered them out before they entered the candidate set.

The UX trade-off

Cursor pagination's strict dominance in performance is paid for with a UX restriction:

You can only go next / previous. You cannot "jump to page 47" because there is no cursor for page 47 unless the user has already seen page 46. The cursor is the last-seen row on the previous page — it doesn't exist until the client has observed it.

This is why cursor pagination is common in:

  • Infinite-scroll feeds (Twitter, Instagram, Facebook) — no page concept, just "load more."
  • API paginators that return next_cursor in the response payload (GraphQL Relay connection spec, Stripe API, GitHub REST API).
  • Backend-to-backend iteration over large tables (ETL, reindexers) — never needs random-page UX.

And why offset pagination persists despite its cost in:

  • Admin dashboards where users want to jump to the last page.
  • Content catalogs with finite known size (e.g. blog archive pages 1-20).
  • Tabular UIs with page-number navigators.

For this middle case — needs offset UX but doesn't want OFFSET cost — deferred join is the compromise.

Stability requires a tiebreaker

Cursor pagination on a non-unique sort key is unstable without a tiebreaker:

-- Wrong: cursor on created_at alone
WHERE created_at < $last_created_at
ORDER BY created_at DESC
LIMIT 25;
-- Rows with created_at = $last_created_at but different ids
-- can be seen twice (in both pages) or not at all.

The canonical fix is a lexicographic cursor on a composite key that includes the primary key as the tiebreaker:

-- Correct: cursor on (created_at, id)
WHERE (created_at, id) < ($last_created_at, $last_id)
ORDER BY created_at DESC, id DESC
LIMIT 25;

This depends on the (created_at, id) comparison semantics being the database's native tuple comparison, which requires ROW() syntax in some engines or, in MySQL, the literal tuple comparison shown above.

Composite index alignment

For best performance the index should match the cursor column order:

CREATE INDEX posts_created_at_id ON posts(created_at, id);

With this index, the seek + read-25 operation is an index-range-scan on adjacent leaf entries — the cheapest operation a B+tree can serve.

Without it, the engine must fall back to scanning a wider range and filtering, which erodes the O(1) property.

Seen in

Last updated · 378 distilled / 1,213 read