Pagination Consistency Under Concurrent Writes

Concurrent inserts, updates, and deletes can cause duplicate or missing records when clients traverse multiple pages. Stable ordering plus a cursor-based strategy can make traversal deterministic relative to a chosen consistency boundary.

Offset pagination uses a position such as `offset=20` to identify the next page, but that position can shift when rows are inserted or deleted before it. A client may therefore see the same record twice or skip a record entirely.

A stable `ORDER BY` is essential. If the primary sort field is not unique, add a unique tie-breaker such as `id`, for example `ORDER BY created_at DESC, id DESC`. Without this, records sharing the same timestamp can move between pages even without concurrent writes.

Cursor pagination replaces a numeric offset with a value representing the last item seen. For `created_at DESC, id DESC`, the next query can request rows where `(created_at, id)` is strictly after the previous cursor in that ordering. This avoids depending on how many rows currently precede the client.

Cursor pagination does not automatically provide a transactionally consistent snapshot. If records are inserted, updated, or deleted while the client is paging, the exact behavior depends on the cursor predicate, ordering columns, isolation level, and whether the application captures a fixed snapshot or boundary.

For APIs requiring a stable multi-page view, common approaches include snapshot or transaction-based reads, or encoding a fixed upper-bound value in the cursor. The design should also define what happens when records are deleted or when sort keys change during traversal.

Key points

• Use a deterministic ordering with a unique tie-breaker.
• Offset pagination can produce duplicates or gaps when earlier rows change.
• Cursor pagination tracks a logical position instead of a row count.
• A cursor does not by itself guarantee snapshot consistency.
• For strict consistency, combine pagination with an explicit consistency boundary or snapshot strategy.

Interview questions

Why can offset pagination return duplicate or missing records when rows are inserted concurrently?

Offset pagination calculates the next page from the current position in the result set. If a new row is inserted before that position, the rows after it shift, so the next request can revisit a record from the previous page. A deletion can shift rows in the opposite direction and cause a record to be skipped. The problem comes from treating a changing result set as though its row positions were fixed.

How would you design a cursor for `created_at DESC` pagination?

I would use `created_at` together with a unique tie-breaker such as `id`. The query would order by `created_at DESC, id DESC`, and the cursor would contain the last row's two values. The next query would use a strict lexicographic predicate matching that ordering, such as `(created_at, id) < (:createdAt, :id)`. This prevents ties on timestamps from making the continuation point ambiguous.

Does cursor pagination guarantee that a client sees a consistent snapshot?

No. Cursor pagination provides a stable way to continue through an ordering, but it does not automatically freeze the database state. New rows, deletions, or changes to ordering fields can still affect what later pages contain. If the API requires a fixed view, the system needs an explicit consistency mechanism such as a database snapshot, suitable transaction semantics, or a fixed boundary captured in the cursor.

What happens if `created_at` or another cursor field is updated while the client is paging?

The record can move to a different position in the ordered result, which means it may be encountered later, earlier, or no longer satisfy the cursor predicate. Whether this creates a duplicate or omission depends on the direction of the update and the exact predicate. For strong traversal semantics, I would prefer an immutable ordering key or define a snapshot or fixed boundary. The API contract should explicitly state how mutable sort fields are handled.

How would you test an API for pagination anomalies?

I would create enough ordered records to span several pages, then interleave page requests with inserts, deletes, and updates. The test would track every returned identifier and check for duplicates and expected omissions under the documented consistency model. I would also test equal sort-key values and boundary cases where a write occurs immediately before or after the cursor. For database-backed systems, I would run these tests concurrently rather than only simulating sequential requests.