Why Offset Pagination Fails in Production APIs

A client of a database-backed API can request a page by specifying how many rows to return and how many matching rows to skip first. This method is called offset pagination. For example, a query with a limit and offset skips the requested number of matching rows, then returns the next set.

Offset pagination is easy to understand and lets clients jump to a numbered page. As the database stores more rows, it must pass over more rows to reach deep pages. When a request adds or deletes rows, later rows move to different positions. As a result, deep-page requests take longer, and a sequence of pages can repeat or miss items. Understanding these problems helps you decide whether offset pagination or cursor pagination better fits the API’s needs.

Deep offsets make the database pass earlier rows

LIMIT controls how many rows the database returns. OFFSET tells it how many matching rows to skip before returning results. A request such as LIMIT 20 OFFSET 100000 still returns only 20 rows, but the database generally has to account for the rows before them.

An index is a data structure that helps the database find and order rows. When an index supports the query’s filters and sort order, the database can follow the index entries instead of sorting all matching rows. It still has to pass through earlier entries to reach the offset. As a rough model, a page of size K at offset N requires work proportional to N + K. The database’s chosen query steps affect the cost. So do the query filters and joins. Data layout and indexes also matter. So this estimate does not apply to every database or query.

Without a suitable index, the database may also need to scan or sort more rows. MySQL’s documentation explains when an index supplies the requested order and when the database must use a separate sorting step, called a filesort, to process matching rows. PostgreSQL likewise notes that rows skipped by OFFSET still have to be computed by the server in its LIMIT and OFFSET documentation.

This means an API response can stay small while its database query gets slower and consumes more resources at greater depths. An index helps when it matches the query, but it does not turn a large offset into a direct jump to a known row. To judge the impact on an API, inspect the database’s chosen query steps and measure requests for deep pages using the filters and amount of data seen in production.

One PostgreSQL 17.11 benchmark illustrates the pattern without establishing a universal threshold. On a single machine, with a million-row table and an index on (created_at, id), the benchmark measured a 20-row page at a median of 5.845 ms at page 1,000 and 243.1 ms at page 50,000. Those results describe that benchmark’s setup, not a forecast for other databases or workloads. The benchmark details include its conditions and code.

Writes move offset boundaries

An offset identifies a position in the current result set. It does not remember which row the client last received. When rows enter or leave the ordered collection between requests, later positions shift.

Suppose an API returns this ordered collection:

10, 20, 30, 40, 50, 60

With a page size of three, the first page contains 10, 20, 30. The next request uses offset three and returns 40, 50, 60.

Now suppose a row with value 15 is inserted before the client requests the second page:

10, 15, 20, 30, 40, 50, 60

Offset three now starts at 30, so the second page repeats an item from the first. If a row before the boundary is deleted instead, later rows move toward the beginning, and reusing the old offset can skip an item the client has not seen.

Each request can return a valid page for the data at the time of that request, while the sequence of pages fails to represent one stable traversal. The exact behavior across requests depends on the database’s transaction isolation. It also depends on which committed changes each query can see, as determined by the database’s rules, and on how the API handles transactions. For example, PostgreSQL documents that separate statements using its default Read Committed isolation level can see different committed data in its transaction isolation documentation.

A unique sort order does not prevent this shifting. It makes row order repeatable for a given dataset, but inserts and deletes can still change which rows occupy each offset.

A stable order fixes ties, not shifting positions

Pagination needs an explicit order. Without ORDER BY, a database does not promise a repeatable row sequence, so the same offset can refer to different rows across executions. Even when a query sorts by a field, ties can leave the order incomplete. For example, several rows can share the same timestamp.

Add a unique tie-breaker to the sort order, such as an ID:

ORDER BY created_at, id

The timestamp defines the main order, and the ID places rows with matching timestamps in a consistent sequence. PostgreSQL recommends an order that constrains results to a unique ordering when using LIMIT and OFFSET; MongoDB’s documentation similarly recommends including a unique field when sorting values that can tie.

This change solves ambiguity among tied rows. Earlier inserts and deletes can still move offset boundaries. Treat deterministic ordering and stability across writes as separate requirements.

Cursor pagination follows a sort key

Cursor, or keyset, pagination asks for rows after the sort values of the last row the client received. A cursor is a continuation token that carries the position for the next request. For an ascending order on (created_at, id), a query can use the last row’s values as its boundary:

SELECT ...
FROM items
WHERE (created_at, id) > (:last_created_at, :last_id)
ORDER BY created_at, id
LIMIT 20;

The comparison depends on the sort direction and what the database supports. The query needs a consistent order with a unique tie-breaker, plus an index that supports that order and lets the database start at the saved values. When these conditions hold, the database can go straight to the saved sort values instead of passing over more and more earlier rows. API pagination guidance from Zalando describes this approach as continuing from values in the last item.

Inserts or deletes before the saved boundary do not shift the next-page position as they do with an offset. Cursor pagination still does not freeze the collection: new rows after the boundary can appear on later pages, and changes to sort-key values can move rows across it. A cursor that depends on looking up the last row also needs to account for that row being deleted; a token that stores the boundary values avoids that particular dependency.

Cursor navigation fits sequential next-page or previous-page traversal of a large collection. It generally does not support jumping directly to an arbitrary page number. Offset pagination can remain a reasonable choice for shallow or bounded results, relatively static collections, or clients that genuinely need numbered-page jumps. Choose based on the API’s navigation needs, then test the query shape and data behavior that the choice creates.

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