Cursor pagination under concurrent writes

Follow row identities through a changing list

A page number names a position in a result set. A cursor names a boundary in its ordering. When rows change between requests, those references can lead to different next pages. A cursor can avoid positional shifts while still failing to provide a stable snapshot.

Our SQLite experiment starts with six rows, reads three and then applies one controlled mutation before continuing to the end. Each walk records every returned ID. We compare offset pagination with a composite cursor, and include a timestamp-only cursor to expose tied-key mistakes.

"Missing originals" counts initial rows that still exist after the mutation but never appear in the completed walk. Deleted rows are excluded from that denominator. New rows are inspected separately because a cursor is not expected to include every row inserted ahead of its boundary.

The mutable-key case is the important exception. Composite pagination eventually returns an already-seen row again after its key moves behind the cursor. Stable positioning depends on the ordering values, not just on the presence of a cursor parameter.

Give every row a unique position

The normal fixture uses IDs 1 through 6 with sort keys 10 through 60. Queries order by sort_key DESC, id DESC, so the first page contains IDs 6, 5 and 4. The next cursor records both values from row 4: sort key 40 and ID 4.

PostgreSQL's LIMIT documentation warns that a predictable subset requires an ordering that constrains rows to a unique order. Microsoft's pagination guidance likewise recommends a fully unique ordering and explains how date-plus-ID ordering resolves timestamp ties. PostgreSQL LIMIT and OFFSET, EF Core pagination guidance.

For descending order, the next composite page selects rows whose sort key is less than 40, plus rows whose key equals 40 and whose ID is less than 4. Both the filter and ordering must use the same fields and directions.

SELECT id, sort_key FROM items
WHERE sort_key < :last_key
   OR (sort_key = :last_key AND id < :last_id)
ORDER BY sort_key DESC, id DESC
LIMIT 3;

This is the actual comparison structure used by the model, expressed with named parameters for readability. An ascending list would require the opposite inequalities. Mixed directions require deriving each comparison from the declared order rather than changing every operator mechanically.

Insertions and deletions shift an offset

Insert a new row 7 with key 70 after the first page. The offset reader skips three rows in the new ordering: 7, 6 and 5. Its second page therefore starts with row 4, which the user has already seen. The composite reader continues below the saved boundary of row 4 and avoids that duplicate.

Now delete row 6 instead. The offset reader skips rows 5, 4 and 3 in the changed list, so row 3 is never returned. The composite reader still asks for rows below key 40 and ID 4. It returns 3, 2 and 1.

These outcomes explain why keyset pagination is useful for sequential navigation through a changing feed. The Microsoft guidance discusses the same insertion and deletion problem for offset pagination. The model supplies concrete returned IDs rather than estimating how frequently a production workload would encounter it.

Offset remains useful when the interface requires arbitrary page jumps and accepts its semantics. The database still has to process skipped rows, as PostgreSQL documents, but this experiment contains no timing or query-plan measurements. It cannot quantify the performance difference for your indexes and data distribution.

A timestamp alone can skip a tied row

The tie fixture assigns key 30 to IDs 6, 5, 4 and 3. The first page returns 6, 5 and 4. A cursor that stores only timestamp 30 and requests strictly smaller timestamps jumps straight to rows 2 and 1. Row 3 is lost from that walk.

Using <= would not solve the problem: it would make already-returned rows with key 30 eligible again. The cursor needs an additional unique tie-breaker. Our composite predicate includes ID, so it returns row 3 before continuing to the smaller timestamps.

The complete result JSON includes the timestamp-only method for every case. In the tied fixture, its missing-original list is exactly [3]. The main table compares offset with the correct composite cursor so that the two different defects remain distinguishable.

Choose a tie-breaker whose uniqueness holds within the actual query scope. If an ID is unique only inside one tenant, the request must retain that tenant constraint. A cursor from one scope should not become a way to query another scope accidentally.

Mutable ordering can repeat an already-seen row

In the fifth case, row 6 appears on the first page and then its sort key changes from 60 to 5. The composite reader continues through 3, 2 and 1, then finds row 6 below its current boundary. Its full sequence is 6, 5, 4, 3, 2, 1, 6.

The cursor predicate worked as written. The row acquired a new place in the ordering. If the product sorts by a mutable field such as last activity, an item can cross a previously visited boundary during navigation.

Client-side deduplication can hide a repeated card, but it cannot establish that every eligible item was seen or that all values came from one moment. Define what the interface promises: a live feed, a best-effort traversal or a consistent export. These are different reader expectations.

For a live feed, refreshing from the head may be the intended way to discover new activity. For a consistent export, investigate a database snapshot or an application-level versioned dataset with a clear lifetime. The download does not implement either mechanism, and a cursor token alone cannot substitute for one.

New rows can appear on only one side of the boundary

The insertion-before-cursor case adds row 7 at key 70. Composite pagination never sees it during that walk because the saved boundary is already below it. In the insertion-after-cursor case, row 7 has key 35 and appears on the next page.

Thus "no missing originals" does not mean "a snapshot of every current row." The two insertion schedules both have zero missing initial rows, yet one includes the new row and the other does not. Keep that distinction visible in analytics exports and synchronization APIs.

A creation-time upper bound can exclude later creations only if that timestamp and its assignment rules support the intended contract. It does not by itself prevent edits, deletions or changes to other filter fields. Document the boundary's meaning instead of calling any timestamp filter a snapshot.

Replaying a request also deserves attention. A repeated cursor may return different values after updates even if the IDs remain the same. Our API idempotency examples discuss stable recorded outcomes for writes; paginated reads need their own explicit freshness and replay contract.

Bind the cursor to the query contract

Treat a cursor as structured continuation state. It can carry ordering values, a format version and information that binds it to the active filter. The server must still validate the request and apply authorization independently.

Encoding the fields makes a token convenient to transport. It does not make them trustworthy or confidential. If tampering matters, use an integrity mechanism appropriate to the service and reject tokens that do not match the requested ordering or scope.

Reverse navigation needs a corresponding predicate and presentation order. A total count may also change between requests. These interface details are not reasons to abandon cursor pagination, but they should be designed alongside the database query.

Run the finite walks against your own assumptions

Download the Python and SQLite model, all method results as CSV and page-by-page JSON. Save the source as experiment.py and run it with Python 3.

python3 experiment.py

The script uses the standard library, executes each walk twice and asserts the duplicate and missing-row counts. All mutations are scheduled between complete queries. It does not run concurrent threads or test transaction isolation behavior under overlapping database statements.

To adapt it, keep the returned-ID assertions and replace the fixture with your actual ordering rules. Add null values, equal timestamps, scope changes and a mutable field that crosses the cursor. Then test the database isolation and token-validation behavior separately. The observed row sequence is the evidence that connects a pagination contract to what the user actually sees.

Sources

Documentation checked September 5, 2026.

1. PostgreSQL: LIMIT and OFFSET
2. Microsoft EF Core: pagination