Keyset Pagination Under Concurrent Writes

A query returns twenty rows ordered by creation time. Before the client asks for the next twenty, another transaction inserts a row near the front of that order. The data set has changed, but the client still expects page two to continue from the point page one reached.

That expectation exposes the main difference between offset pagination and keyset pagination. An offset identifies a position in a particular query result. A keyset cursor identifies an ordering boundary. Under concurrent writes, those are not equivalent references.

Neither model creates a frozen snapshot across independent HTTP requests. The distinction is narrower: keyset pagination can preserve forward progress relative to stable ordering values, while offsets can shift as rows are inserted or removed ahead of the current position.

An offset counts the current result

Consider rows sorted by descending creation time:

A
B
C
D
E
F

A page size of three returns A, B, C. The next request uses an offset of three.

Before that request arrives, a new row X is inserted ahead of A:

X
A
B
C
D
E
F

Applying offset three to the new result starts at C, so C appears again:

page 1: A B C
page 2: C D E

The database followed the request exactly. The offset still means “skip three rows,” but the three rows now ahead of that position are different.

Deletion can produce the complementary effect. If B disappears after page one, skipping three rows in the reduced result can move the next page past D, leaving that row unseen by the client.

These outcomes do not require weak transaction isolation. Each individual query can be internally consistent. The discontinuity comes from applying a positional coordinate to two different committed states.

A keyset cursor names the boundary

Keyset pagination replaces the row count with values from the ordering key. If the order is:

ORDER BY created_at DESC, id DESC

the cursor can carry the created_at and id values of the last row returned. The next query requests rows strictly after that tuple in the chosen order.

For descending order, the predicate has the shape:

WHERE created_at < :cursor_time
   OR (created_at = :cursor_time AND id < :cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT :page_size

If a new row is inserted before the cursor boundary, it does not move that boundary. The next query still continues after the last tuple observed on the prior page.

This property depends on using the same ordering definition in both the ORDER BY clause and the cursor predicate. A cursor containing only created_at is insufficient when several rows can share that timestamp.

Total order prevents ambiguous ties

Pagination needs an order that distinguishes every row participating in the traversal.

Creation timestamps often contain ties. Database timestamp precision, batch inserts, imported data, or application-assigned values can place several rows at the same timestamp. If the query orders only by created_at, the database is not required to return tied rows in a stable relative order unless another ordering term establishes it.

Adding a unique identifier as a tie-breaker creates a total order:

(created_at, id)

The cursor then records both components. For two rows with the same creation time, the identifier decides which one comes first and which side of the boundary the other occupies.

The identifier does not have to encode time. It only has to provide a deterministic tie-break within equal values of the preceding sort columns. If the full ordering tuple is not unique, page boundaries remain ambiguous.

Mutable sort keys can cross the cursor

Keyset pagination is most predictable when ordering values do not change during traversal.

Suppose a list is ordered by updated_at DESC, id DESC. A row originally behind the cursor is modified after page one, giving it a newer updated_at. It moves ahead of the cursor. A forward-only traversal that continues after the old boundary may never encounter that row.

The reverse movement can create duplication. A row already returned on page one can receive an older effective sort value through data correction or another update model, move behind the cursor, and appear again on a later page.

The cursor predicate is still functioning correctly. It classifies rows according to their current ordering values. What changed is row membership on each side of the stored boundary.

This makes the choice of sort key part of the consistency contract. An immutable creation key supports a stable notion of “after the row already seen” even while unrelated fields change. A mutable ranking score, status, or update timestamp represents a live ordering whose members can cross the cursor between requests.

A cursor is not a snapshot token

A keyset cursor can prevent positional drift without preserving the entire data set observed at the first request.

Rows inserted after the cursor boundary can appear on later pages if their ordering values place them there. Rows deleted before the next request disappear. Mutable ordering values can move rows across the boundary. The traversal therefore reflects multiple database states unless the application adds a separate snapshot mechanism.

For some feeds, that is the intended model. The client wants efficient forward movement through a changing collection and accepts that the collection remains live.

Other tasks require a fixed population. An export, audit operation, or reproducible batch may need every page to refer to one logical snapshot. That can require an explicit upper bound, a persisted selection, a database snapshot held through a suitable mechanism, or another application-specific definition of membership.

Keyset pagination and snapshot consistency solve different problems. One defines a continuation boundary; the other defines which version of the data set the traversal belongs to.

Cursor encoding should preserve query meaning

An API often serializes cursor values into an opaque string. Opacity keeps clients from depending on the physical representation, but encoding does not change the semantics that must be preserved.

A cursor can include the ordering tuple and, when needed, parameters that bind it to the query shape. If a client changes a filter between requests while reusing a cursor from the old filter, the boundary may no longer describe a meaningful continuation.

For example, a cursor created under:

status = active
order = created_at DESC, id DESC

does not automatically define a continuation for:

status = archived
order = created_at DESC, id DESC

An API can reject such reuse by signing or validating cursor metadata, or it can define cursors as meaningful only when the caller repeats the original query parameters. The exact representation is an interface choice; the important property is that the continuation predicate matches the ordering and filtering context that produced the cursor.

Index structure affects execution, not semantics

A matching index can make a keyset query seek near the cursor boundary instead of scanning and discarding a large prefix. That is a common operational advantage over large offsets, but it depends on the database, index definition, filter predicates, and query plan.

The semantic distinction remains even without that performance benefit. Offset pagination asks for a count from the start of the current result. Keyset pagination asks for rows beyond a value boundary.

A composite index aligned with filters and ordering can support the latter efficiently. An index that omits relevant leading predicates or uses an incompatible ordering may not. Query plans should be evaluated for the actual database and workload rather than inferred solely from the presence of a cursor.

The boundary defines the contract

Pagination is often treated as response formatting: choose a page size, attach a cursor, return a next link. Concurrent mutation shows that the deeper issue is the identity of the continuation point.

Offsets make that point positional. Positions are recalculated whenever the ordered result changes ahead of them. Keyset cursors make it relational: continue after this ordering tuple. That boundary survives inserts and deletes ahead of it when the sort values used for already observed rows remain stable.

The model still has limits. It does not freeze the collection, stop rows with mutable sort keys from moving, or make an incomplete ordering deterministic. Those properties need separate constraints.

A sound pagination contract states the order, gives that order a unique tie-break, defines cursor scope, and distinguishes live traversal from snapshot traversal. Once those pieces are explicit, the cursor stops being an arbitrary token and becomes a precise statement about where the next query begins.