Designing Stable Pagination for Changing Datasets

When a service returns a large collection through an API, it sends the results in multiple responses. Each response contains only part of the collection. If someone inserts, deletes, or reorders records between requests, a later response can repeat records the client has seen or skip records it has not. The pagination contract states what clients should expect when the collection changes.

A cursor tells the server where to continue. A keyset cursor records the last record’s position in a defined sort order. A snapshot cursor also identifies one fixed view of the collection. A cursor alone does not make every page show the same data. To reduce repeated or missing records, define an order that always puts records in the same sequence, and state whether each request reads the current collection or continues through a fixed view.

Decide what a traversal promises

Decide what view of the data clients should get before choosing a cursor format. A live traversal reads the collection as it exists when the client requests each page. Inserts, deletes, and updates can therefore change what later pages contain. A watermark-bounded traversal sets a cutoff, such as a captured creation sequence, and scans only records at or before it. The cutoff excludes later inserts, while existing records can still change or be deleted.

A snapshot traversal keeps every page tied to the same fixed view of the collection. Use one for work such as an export or audit when the client must list records as they existed at a particular time. OData’s server-driven paging rules and Google’s AIP-158 pagination guidance explain how clients should follow continuation links or tokens. Those instructions alone do not guarantee that all pages use the same snapshot.

A service can combine these choices. For example, it can use keyset pagination within a snapshot, or stop a keyset scan at a watermark. The API should state where the next page begins and which view of the data the client is reading.

How records get skipped or repeated

Suppose records are ordered as A, B, C, D, E, and the first page returns A, B. With offset pagination, the client asks the server to skip the first two records in the current collection before returning the next page.

If someone inserts a new record X before A, the order becomes X, A, B, C, D, E, so skipping two records starts at B and the client sees B twice. If someone deletes A instead, the order becomes B, C, D, E. Skipping two starts at D, so the client misses C.

A sort field that can change creates similar problems. If someone changes a record’s score or status, the record can move across the page boundary. A record the client already received can move later and appear again. A record the client has not received can move earlier and be skipped.

The sort order must also specify how to order records that tie. Sorting only by timestamp leaves records with the same timestamp unordered. If the database returns those records in a different order for the next request, the page boundary can shift even though no record changed. Add a unique tie-breaker, such as a record ID, so the order stays the same.

Choose how the next page begins

Offset pagination asks the server to skip a set number of records and return the next page. It lets clients request a page by number and works predictably while the collection stays fixed. Inserts and deletes before that position shift which records the server returns. Requests for pages far into the collection can also be less efficient. Microsoft’s ASP.NET Core OData paging documentation describes $skiptoken as an alternative to $skip for server-driven paging.

Keyset pagination asks the server for records after the last values the client received for the fields used to sort. For records sorted in ascending order by creation time and record ID, the next-page condition is conceptually:

WHERE (created_at, record_id) > (:last_created_at, :last_record_id)
ORDER BY created_at, record_id
LIMIT :page_size

The unique ID orders records that have the same timestamp. Inserts or deletes before the cursor do not change its position based on how many rows came before it. This avoids the shifting position that affects offset pagination. Microsoft’s Data API Builder documentation describes this benefit for its keyset implementation.

Unless the service adds a snapshot or another boundary that fixes the view, keyset pagination returns pages from a changing collection. A new record after the cursor can appear on a later page, but one inserted before it will not; a record deleted before the client reaches it will be missing. If someone changes a record’s sort key, the record can cross the cursor boundary and be skipped or repeated. For reliable page-by-page traversal, use a sort key that stays the same, preferably one that cannot be changed.

Keep the cursor tied to the query

A cursor is valid only for the sort order and filters used to create it. If the client changes the sort order and reuses the cursor, “after this record” can point to a different position. The same can happen if the client changes a filter or switches collections before reusing the cursor. Tie the cursor to the query settings that define the results, and reject continuation requests that use different settings.

Make continuation tokens opaque, meaning clients follow them without inspecting or changing their contents. OData requires clients to treat next-link URLs as opaque, and AIP-158 gives similar guidance for page tokens. Hiding the token’s contents keeps implementation details private, but it does not give pages a shared snapshot or replace authorization checks on each request.

Tell clients how to recognize the final page. A short page or even an empty page does not always mean the collection is exhausted. AIP-158 uses the next-page token to signal whether another page exists. DynamoDB also directs clients to continue while LastEvaluatedKey is present, and says an empty key marks the final page. Clients should follow the API’s continuation signal instead of deciding that pagination is done based on the number of records returned.

Add a snapshot when the traversal must be repeatable

If a service promises a fixed view, it must use the same snapshot for every request in the traversal. The service can use a database snapshot. It can also use a search index view from a particular point in time. A view tied to a specific version is another option. Refreshes between search_after requests can change the results, so Elasticsearch's pagination guidance describes using a point-in-time view to keep the traversal consistent.

Snapshots use resources. A service may need to keep older data versions or store state on the server. If a snapshot expires, the client may have to restart the traversal. Elasticsearch notes that open point-in-time searches keep older index segments available, which uses resources such as disk space and file handles. The service should say when cursors expire and return a clear error if a client sends an expired cursor.

But a multi-request API does not automatically use one transaction for all requests. PostgreSQL 14 documents that Read Committed takes a new snapshot for each command, while Repeatable Read uses one snapshot for successive reads within a transaction. To provide that behavior across HTTP requests, the API must explicitly arrange a shared snapshot or an equivalent mechanism.

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