Choosing Between Cursor and Offset Pagination

When a database-backed page or API returns many matching records in batches, pagination determines how it finds each batch. Offset pagination asks the database to skip a set number of rows and return the next batch, for example, “skip a requested number of rows and return a batch.” Cursor pagination gives the database a marker for the last position in the sort order, such as the previous item’s date and ID, so it can continue from there.

Choose offset pagination when people need page numbers or jumps to specific pages and measured query costs are acceptable. Choose cursor pagination when people mostly move forward or backward through a large or frequently changing result set, and the database can efficiently find rows using a stable, indexed sort order. The choice depends on how people browse and how the actual query performs, not on a fixed row-count cutoff.

What each method asks the database to do

With offset pagination, the client asks for a position and a page size. The database sorts the matching rows, skips the requested number of rows, and returns the next batch. Numbered pages map directly to offsets: with a fixed page size, page 10 has a known offset. The database still has to count through the skipped rows, so requests for later pages can take more work. PostgreSQL’s documentation describes this cost but does not set a general point at which offsets become too slow.

With cursor pagination, the client sends a marker for where it stopped. The database uses the marker to find rows after or before that position in the sort order. The cursor can hold the last-seen ID or several sort values. APIs often put the marker in an opaque token, which clients pass back without reading its contents. The query continues from a sort position instead of skipping a number of rows.

Cursor pagination performs well only when the database can search efficiently from the cursor’s position. The database needs an index that supports the sort columns and any extra column used to put rows with equal sort values in a consistent order. That extra column is the tie-breaker. GitLab’s keyset guidance also recommends checking the continuation query, the request for the page after the first one, because it may perform differently.

Match pagination to navigation and workload

These rules do not guarantee performance. A query against a small table can still be slow if its filter is costly. A larger table can perform well with a suitable query and index. Measure the queries people actually run instead of switching at a guessed row count.

How people navigate often determines which method fits. Offset pagination supports requests such as “go to page 40” because the client can ask for a row position. Cursor pagination follows the result sequence and does not inherently support jumps to arbitrary pages. The Django REST framework pagination documentation describes this difference between offset-style navigation and cursor navigation. If people need both sequential browsing and occasional jumps, use cursor navigation for the common path and offset pagination for jumps only when the added complexity is justified.

Make the sort order deterministic

Both methods need an explicit, predictable sort order. If several rows have the same sort value, the database needs an extra value to place them consistently. For example, sorting by creation date alone leaves the order of rows with the same date unclear. Adding a unique ID gives the database a clear order. PostgreSQL warns that LIMIT and OFFSET do not select a predictable subset unless the query constrains the order. Its pagination documentation also notes that different limits and offsets can produce inconsistent subsets when the order is not unique.

Cursor pagination needs the full sort order to resume correctly. The cursor must include enough information to identify its position, including the tie-breaker value when the main sort value is not unique. Use sort values that do not change. If a row’s sort value changes while people browse, the row can move across the cursor boundary and appear in an unexpected place. The Django REST framework guidance recommends an unchanging ordering value for cursor pagination.

Before choosing cursor pagination, write down the full sort order and confirm that the database has an index that supports it. A sort order that uses several columns may need a composite index, which is an index built from more than one column. Test the query that fetches later pages with the real filters and sort order, not just a simplified query on a primary key.

Account for inserts and deletes between requests

Offset pagination tracks a position in the current results rather than a particular row. When another request inserts or deletes rows before that position, the later page shifts, so someone browsing may see the same row twice or miss one. This matters most when the data changes often during browsing.

A cursor continues from an earlier sort position, which reduces this kind of shift when the sort values stay the same. But the cursor does not freeze the results. A new row that sorts before the cursor may not appear during the current traversal. An update to a sort value can move a row across the cursor boundary. If a user or client needs the same snapshot across several requests, cursor pagination alone does not guarantee it. The database and API must define how they handle consistency.

Measure the queries people will run

Compare the first-page request with a typical request for a later offset page. Then compare both with a cursor request for the next page. Use data that resembles production, along with similar filters and sort orders. Include realistic writes when concurrent inserts or deletes matter. These tests show whether offset requests become costly at the depths people reach and whether the cursor condition and index make later-page requests efficient.

Table size alone does not give you a universal threshold. The documentation explains why skipped rows can cost more and why keyset queries need suitable sort orders and indexes, but it gives no cutoff or performance ratio that applies across databases. Set a threshold only after measuring the query patterns and page depths your product needs. Also decide whether the interface needs an exact total count. Offset pagination does not make that count inexpensive by itself.

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