# When does cursor pagination over a mutating sort key skip or duplicate rows?

**Date of Record:** 2026 November 1

**Scope:** Applies to keyset (cursor) pagination where the cursor encodes a sort key that can change between page requests. Excludes offset pagination and append-only tables with immutable sort keys.

## Core Distinction

**Predicate.** Keyset pagination is stable if and only if the sort key is immutable and unique. Given a cursor on key k, the next page is all rows with sort > k. If a row's sort value moves from below k to above k between pages, it is returned twice; if it moves from above to below, it is skipped entirely.

**Failure.** A row updated between page fetches changes its position in the sort order. The client, holding a cursor into a moving sequence, sees an inconsistent view: no error, no gap marker, just a result set that is not a partition of the table.

**Asymmetry.** Offset pagination at least has the decency to visibly shift. Keyset pagination appears consistent because every page is internally ordered and contiguous; only the union across pages fails to tile the table.

## What Was Observed

**Signature.** Export counts that differ from table counts by a few rows, varying per run. Rows updated during the export are the missing or doubled ones. The defect rate tracks write rate during pagination.

**Trace.** Sort key is updated_at. Export begins at t0 with cursor (updated_at=t0). A row updated at t1 > t0 was already read on page 1 and now sorts after the current cursor, so it appears again on page 3. A row updated at t1 that had not yet been read moves after the cursor and is never read. Over a one-hour export with steady writes, the delta is bounded by the write count in the window.

**Why monitoring stays green.** Each page is valid. Page sizes are full. The client receives a well-formed sequence with no indication that rows were skipped or repeated.

## Condition of Validity

Pagination returns an exact partition only while at least one holds:

1. **Immutable sort key.** The key is assigned at insert and never updated, and is unique (monotonic id, ULID, sequence).
2. **Snapshot.** All pages are read from a single snapshot or point-in-time view with a stable visibility boundary.
3. **Deduplication.** The client deduplicates by primary key and tolerates gaps, and completeness is verified separately against a count.

Condition 3 is acceptable for a UI, never for an export or a migration.

## Decision Boundary

- Immutable unique sort key: exact partition.
- Mutable sort key with snapshot reads: exact for the snapshot; the export reflects the state at snapshot time, not at completion.
- Mutable sort key, no snapshot: skips and duplicates are guaranteed whenever writes overlap pagination.
- Mutable sort key plus client dedup: duplicates removed, gaps remain. Treat as incomplete for any consumer that requires all rows.

## Operational Effect

The invariant couples read correctness to write concurrency, which no pagination API surfaces. Remediation is a key change or a snapshot read, not a client fix. The trap is to switch to offset pagination to get a total count. That trades silent skips for the well-known offset drift, which at least shifts consistently, and it does not fix anything for a mutating table.

```python
from dataclasses import dataclass


class CursorStabilityError(RuntimeError):
    """Raised when the sort key underpinning a cursor can change between pages."""


@dataclass(frozen=True)
class CursorGate:
    sort_key_immutable: bool
    sort_key_unique: bool
    snapshot_read: bool = False

    def verify(self, export_grade: bool = True) -> bool:
        if self.snapshot_read:
            return True
        if not self.sort_key_unique:
            raise CursorStabilityError("sort key is not unique: ties split across pages")
        if not self.sort_key_immutable and export_grade:
            raise CursorStabilityError(
                "mutable sort key without snapshot read: rows may skip or repeat"
            )
        return True


def dispatch_pagination(gate: CursorGate) -> str:
    try:
        gate.verify()
    except CursorStabilityError:
        return "snapshot-read"
    return "keyset"
```