Defining Stable Ordering Rules for Cursor Pagination

When an API returns a large collection in pages, a client uses a cursor to ask for records after or before a position it has already received. The cursor identifies that position only if the server orders records by a precise rule. If several records have the same sort values, or a record moves as the collection changes, the client can receive duplicates or miss records.

The server can give every record a distinct position by sorting on a combination of values that is unique, such as a creation timestamp followed by a unique record ID. The cursor must carry enough information to identify that position. The API contract must also explain what happens when records change between requests. A unique order makes each position clear, but it does not guarantee that every page sees the same snapshot of the data.

Add a tie-breaker to the sort

A sort such as created_at ASC does not fully order records when two records have the same timestamp. A database can return tied records in either order unless the query specifies another sort key. PostgreSQL documents that later sort expressions determine the order among rows tied on earlier expressions, while a query without a specified order does not promise a particular row sequence (PostgreSQL’s ORDER BY documentation).

Add a unique tie-breaker to make each record’s position distinct. For example:

ORDER BY created_at ASC, id ASC

id breaks ties in created_at when each ID is unique within the result set. The cursor needs both values: the timestamp alone cannot distinguish between two records with the same timestamp.

The next-page query must follow the same combined ordering. For the ascending example, it selects records whose timestamp is later than the cursor’s timestamp, plus records with the same timestamp and a larger ID:

WHERE created_at > :last_created_at
   OR (created_at = :last_created_at AND id > :last_id)

The server should use matching sort and continuation rules. If the cursor stores only created_at, a strict comparison can skip tied records, while a non-strict comparison can return the last record again. Microsoft’s guidance on stable sorting in EF Core describes how non-unique ordering can lead to missing or duplicate results during pagination, and recommends adding a unique ordering key.

The tie-breaker does not have to appear as a client-selectable sort field. An API can present records as newest first while using a unique ID as a final internal key. The cursor still needs to identify the full position, even when the API keeps that tie-breaker out of the visible sort controls.

A unique order does not freeze the collection

For each set of records and query parameters, a unique sort tuple gives every record an unambiguous position. It does not ensure that successive requests see the same version of a changing collection.

With keyset pagination, the server continues from the last-seen sort values. This avoids some shifts that occur with offset pagination, which skips a count of rows, when records are inserted or deleted before that offset. But if a record’s sort value changes between requests, it can move across the cursor position. Depending on the direction of the change and the continuation rule, the client can see the record again or miss it.

The ordering rule determines where each record belongs; snapshot behavior determines whether the records stay fixed as the client moves through the pages. A cursor and a unique tie-breaker do not create a snapshot. If the API promises a consistent snapshot, it needs to explain how it provides one. Otherwise, it should describe the collection as live and explain what additions, deletions, and changes to sort fields mean for pagination.

Document the ordering clients can rely on

The API contract should specify the full order, its direction, and tie-breakers. For example, saying “newest first” is incomplete if records with the same creation time have no specified relative order. The contract should also state how the server handles null sort values when those values are allowed, because their placement affects which records follow a cursor.

The contract should explain that a cursor represents the full sort position and whether clients may reuse it after changing filters, sort parameters, or access scope. It should also describe how forward and backward requests relate to the same order. The Relay Cursor Connections Specification requires a consistent edge order across pages and says backward pagination should preserve that order. A database-backed API can make continuation clearer by defining a unique tie-breaker as well.

Document what clients should expect while records change. Say whether pages read a live collection or a stable snapshot, and describe how updates to sort fields affect traversal. If the API offers only best-effort traversal of live data, clients that require deduplicated results can track record IDs they have already processed.

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