How Cursor Pagination Works and When to Use It Instead of Offset Pagination

For a database-backed API that splits a large list into pages, the pagination method affects query cost and which records users see as the data changes. Offset pagination identifies a page by its position, using a row offset and page size. Cursor pagination identifies the next page by a known sort value.

Use a cursor when clients move through a large or frequently changing list and need predictable query performance. Use an offset when the list is small or relatively static, or when users need to jump directly to a numbered page. A cursor is a value that marks where the next request should continue; the database uses it with a stable sort order to find the next set of records.

How the database fetches each page

With offset pagination, the database sorts matching records, skips the requested number, and returns the page size. For later pages, the database must skip more records before returning results. Larger offsets make deep pages slower on large datasets. Sentry’s production discussion describes why this becomes a problem for large queries.

Cursor pagination stores the sort values from the last item on a page. The next request asks for records beyond those values. With an index that supports the filter and sort, the database can seek to the cursor boundary instead of skipping earlier matches.

For example, suppose an API sorts posts by created_at from newest to oldest. Timestamps can repeat, so the query also sorts by unique id:

SELECT id, created_at, title
FROM posts
WHERE created_at < :last_created_at
   OR (created_at = :last_created_at AND id < :last_id)
ORDER BY created_at DESC, id DESC
LIMIT :page_size_plus_one;

The cursor contains both sort values from the last returned post. The second condition handles posts with the same timestamp: it uses id to establish which post comes next. Without that tie-breaker, the database could skip or repeat records that share a timestamp. Pagination examples using composite sort keys show why the cursor must represent the full ordering.

The database and API must use the same sort order on every request. An index with columns that match the query’s filters and sort order helps the database find the next page efficiently. Check the query plan to confirm the index supports the query. SQL dialects also differ in how they express comparisons across multiple columns, so adapt the predicate to your database.

A common way to detect another page is to request one more record than the client asked for. If the database returns that extra record, the server returns only the requested number and includes a next cursor. If it returns no extra record, the server knows the list has ended. Cursor pagination examples describe this limit + 1 approach.

What happens when records change

Offset pagination uses a row’s current position, so inserts or deletions between requests can shift page boundaries. For example, if a new record appears before the next offset, a client can see a record twice. If a record disappears, the client can miss one as the remaining rows shift.

A cursor uses sort values as a boundary, so an insertion or deletion before that boundary does not shift the next page in the same way. That makes cursor pagination more reliable for scrolling through a list that changes while a client reads it. Speakeasy’s API pagination guidance discusses the consistency problems that position-based pages can create.

A cursor does not freeze the dataset. If a record’s sort value changes and it moves across the cursor boundary, the client can still see a repeat or miss it. If a client needs a precise snapshot across multiple requests, the API must provide a separate snapshot or transaction mechanism; cursor pagination alone does not guarantee one.

When to choose a cursor

Choose cursor pagination when clients read deep pages from a large dataset. It also suits infinite scrolling and browsing records as new ones arrive. The database avoids the growing skip cost of large offsets, provided the query has an appropriate index. The cursor also gives the API a stable continuation point instead of relying on a page number.

Many APIs return an opaque cursor token. The client sends it back without interpreting its contents, while the server can encode sort values and other details needed to continue. Base64 encoding alone does not hide or protect the values, so sign or encrypt the token if its contents or integrity need protection. An opaque token lets the API change its pagination details without requiring clients to construct cursor values themselves, as Speakeasy’s pagination guidance notes.

Cursor pagination makes page-number navigation awkward: clients must fetch earlier pages in sequence to reach a later point. The API must keep the sort deterministic. It must include every sort key in the cursor and handle changes to those keys. If users need to jump to a numbered page, or the dataset is small and stable, offset pagination is simpler and more convenient. There is no universal row-count threshold for switching; measure query performance and consider how often records change.

When offset pagination still fits

Offset pagination works well when a dataset is small enough that skipping rows stays inexpensive, when users need numbered pages, or when the data rarely changes. It is straightforward to implement and supports direct requests for a particular page. For a large or rapidly growing dataset, check how query latency changes as offsets increase before relying on it for deep navigation.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com