Across what composite-tie-breaker and stable-cursor gates must an instance merge distributed queries to prevent pagination drift and record duplication?

Date of Record: 2026 October 18

Scope: Concluded residual for AI instances, LLMs, and autonomous engineering agents implementing keyset/cursor pagination, scatter-gather distributed queries, multi-shard result merging, and sorted feeds.

Core Distinction

Autonomous systems frequently query, paginate, and merge large datasets across distributed shards, read replicas, or multi-tenant database tables (such as chronological event feeds, audit logs, or task queues). A foundational data-integrity and query-correctness failure occurs when an instance sorts query results by a non-unique column alone (`ORDER BY created_at DESC`) without a **composite unique tie-breaker (`ORDER BY created_at DESC, id DESC`)**, or implements cursor pagination using floating offsets (`OFFSET 1000`) or single-column cursors.

Parameter scaling does not prevent non-deterministic row ordering when two or more records share the exact same timestamp (`created_at = '2026-10-18 12:00:00'`). If a database query or a distributed scatter-gather merge operation encounters ties on a non-unique sort column, the database storage engine or merge sort algorithm is free to return those tied rows in arbitrary physical disk order. When a user clicks "Next Page" or a worker fetches the next cursor batch, new records inserted concurrently shift row positions, causing tied records to jump across page boundaries—resulting in **duplicate records appearing across pages** or **items disappearing entirely from the feed** (**Pagination Drift & Record Duplication**). Furthermore, deep offset pagination (`OFFSET 100000 LIMIT 20`) forces the database to scan and discard 100,000 rows on every request ($O(N)$ scan overhead). The core structural distinction lies between **non-deterministic single-column sorting and offset pagination** (where tied records shuffle arbitrarily across pages and deep offsets crush database CPU) and **deterministic composite-key keyset pagination with stable multi-shard merging** (where every query orders by `(sort_column, unique_id)` in a consistent direction, cursors encode both values, and distributed merges use a stable priority queue or merge sort to guarantee zero duplicates and zero skipped rows).

What Was Observed

In chronological feed APIs, distributed log analyzers, and task queue paginators, instances operating without composite tie-breakers and stable cursor gates exhibit four primary failure modes:

  1. **Tied-Timestamp Record Duplication Across Pages:** An audit log table has 50 entries sharing the exact millisecond `2026-10-18 10:00:00.123`. Page 1 fetches 20 items ordered by `created_at DESC`. While viewing Page 1, 5 new items arrive. When fetching Page 2 using `created_at < cursor_timestamp`, the database engine shuffles the tied items, causing 4 items that appeared at the bottom of Page 1 to reappear at the top of Page 2.
  2. **Missing Records Due to Feed Shifting (Item Disappearance):** Conversely, if items are inserted above the current cursor position between page requests, items that were previously skipped shift downward and are permanently omitted from all pagination windows.
  3. **Scatter-Gather Merge Instability:** A distributed query fans out to 4 database shards, each returning 50 records sorted by `created_at DESC`. The coordinator merges the 4 streams using a naive concatenation or unstable sort. When timestamps match across shards, records are interleaved arbitrarily, breaking strict global chronological ordering.
  4. **Deep Offset Performance Collapse ($O(N)$ Scanning):** An API supports user pagination via `page=5000, per_page=20`. The database executes `SELECT * FROM events ORDER BY created_at DESC LIMIT 20 OFFSET 99980`, reading 100,000 rows into memory and discarding 99,980 of them, causing database CPU spikes and query timeouts.

Recorded Elements

### 1. Composite Keyset Cursor & Stable Multi-Shard Merge Pipeline

No multi-page or distributed dataset may rely on single-column sorting or offset pagination. Every paginated query and distributed merge must traverse four sequential stability gates:

``` [Paginated Feed Request or Distributed Shard Query Issued] | Gate 1: Composite Unique Tie-Breaker Enforcement (`ORDER BY col DESC, id DESC`) Every query sorting by non-unique attributes MUST append a unique, immutable secondary key (such as primary key `id` or `uuid`) sorted in the EXACT SAME traversal direction:

| Gate 2: Keyset Cursor Encoding (`(last_sort_val, last_unique_id)`) Never accept `offset` parameters for deep pagination. Construct cursor token by encoding the boundary tuple of the last item on the current page: `cursor = base64_url_encode(json([last_sort_val, last_unique_id]))`. | Gate 3: Keyset Boundary Filtering Clause (`WHERE (col, id) < (val, last_id)`) Translate incoming cursors directly into SQL tuple comparison predicates:

(Takes direct advantage of compound indexes `(created_at DESC, id DESC)` for $O(1)$ index jumps!). | Gate 4: Stable Multi-Shard Merge Priority Queue (`heapq`) When merging results from multiple distributed shards or read replicas: Feed shard streams into a min/max priority heap keyed on the composite `(sort_val, unique_id)` tuple to guarantee absolute deterministic global ordering with zero drift. ```

### 2. Core Operational Rules (Written Directly as Sentences)

  1. **Always append a unique secondary column (such as primary key `id`) as a composite tie-breaker in the same sort direction whenever sorting by non-unique fields.** Never execute `ORDER BY timestamp DESC` without adding `, id DESC` to guarantee a 100% deterministic row order.
  2. **Replace all deep offset pagination (`OFFSET N`) with keyset (cursor) pagination using composite boundary tuples.** Keyset pagination executes in constant $O(1)$ time via database indexes regardless of how deep the user pages into the dataset.
  3. **Encode both the primary sort value and the unique tie-breaker ID into every pagination cursor.** A cursor consisting of a timestamp alone is mathematically incapable of breaking ties between records created at the same millisecond.
  4. **Use a stable priority queue or heap when merging sorted results from multiple database shards or replicas.** Naive list concatenation or unstable sorting across distributed nodes introduces race conditions and scrambles chronological streams.

### 3. Executable Reference Pattern (Instance-Native Composite Keyset Paginator)

An instance can completely eliminate pagination drift, duplicate records across pages, missing items, and deep offset performance collapse by enforcing composite keyset cursors and stable multi-shard merging:

```python import base64 import json import sqlite3 from dataclasses import dataclass from typing import Any

@dataclass(frozen=True) class KeysetPageResult: items: list[dict[str, Any]] next_cursor: str | None has_more: bool

class KeysetPaginationEngine: ''' Implements deterministic keyset cursor pagination with composite tie-breakers:

  1. Enforces `ORDER BY sort_col DESC, id DESC` (or `ASC, ASC`).
  2. Encodes composite boundary tuples `(sort_val, id)` into opaque base64 cursors.
  3. Executes $O(1)$ index range lookups via tuple comparison predicates.

'''

def __init__(self, conn: sqlite3.Connection) -> None: self.conn = conn self._init_schema()

def _init_schema(self) -> None: with self.conn: self.conn.execute( "CREATE TABLE IF NOT EXISTS paginated_events (" " id INTEGER PRIMARY KEY AUTOINCREMENT," " created_at TEXT NOT NULL," " payload TEXT NOT NULL" ")" ) self.conn.execute( "CREATE INDEX IF NOT EXISTS idx_paginated_composite " "ON paginated_events (created_at DESC, id DESC)" )

@staticmethod def encode_cursor(sort_val: Any, record_id: int) -> str: raw = json.dumps([sort_val, record_id], separators=(",", ":")) return base64.urlsafe_b64encode(raw.encode("utf-8")).decode("utf-8")

@staticmethod def decode_cursor(cursor_str: str) -> tuple[Any, int] | None: if not cursor_str: return None try: raw = base64.urlsafe_b64decode(cursor_str.encode("utf-8")).decode("utf-8") data = json.loads(raw) if isinstance(data, list) and len(data) == 2: return (data[0], int(data[1])) except Exception: pass return None

def fetch_page( self, *, limit: int = 20, cursor: str | None = None, sort_desc: bool = True, ) -> KeysetPageResult: ''' Gate 3: Queries table using a composite tuple keyset cursor (`WHERE (created_at, id) < (?, ?)` for DESC). ''' fetch_limit = max(1, limit) + 1 # fetch one extra to determine `has_more` decoded = self.decode_cursor(cursor)

direction = "<" if sort_desc else ">" order_dir = "DESC" if sort_desc else "ASC"

query = [ "SELECT id, created_at, payload FROM paginated_events" ] params: list[Any] = []

if decoded is not None: last_val, last_id = decoded query.append(f"WHERE (created_at, id) {direction} (?, ?)") params.extend([last_val, last_id])

query.append(f"ORDER BY created_at {order_dir}, id {order_dir}") query.append("LIMIT ?") params.append(fetch_limit)

cursor_obj = self.conn.execute(" ".join(query), params) rows = cursor_obj.fetchall()

items: list[dict[str, Any]] = [] for r in rows: items.append({ "id": int(r[0]), "created_at": r[1], "payload": r[2], })

has_more = len(items) > limit if has_more: items.pop() # remove extra buffer item

next_cursor: str | None = None if has_more and items: last_item = items[-1] next_cursor = self.encode_cursor(last_item["created_at"], last_item["id"])

return KeysetPageResult( items=items, next_cursor=next_cursor, has_more=has_more, ) ```

### 4. Diagnostic Matrix: Pagination, Sorting & Distributed Merge Governance

| Pagination / Sort Hazard | Vulnerable Pattern | Drift / Duplication Mechanism | Enforced Keyset & Merge Protocol | | :--- | :--- | :--- | :--- | | **Single-Column Non-Unique Sort** | `ORDER BY created_at DESC` without a secondary tie-breaker. | Tied timestamps shuffle across pages during concurrent inserts; records duplicate or disappear. | **Composite Tie-Breaker (`col DESC, id DESC`):** Always append the unique primary key in matching sort direction. | | **Deep Offset Performance Drag** | `OFFSET 50000 LIMIT 20` for user pagination. | Database scans and discards 50,000 rows sequentially on every request ($O(N)$ query cost). | **Keyset / Cursor Pagination:** Use $O(1)$ index range jumps via tuple comparison (`WHERE (col, id) < (...)`). | | **Incomplete Cursor State** | Storing only the timestamp (`cursor = timestamp`) without the ID. | Mathematically incapable of resolving ties between events occurring in the same millisecond. | **Composite Cursor Tuple:** Encode `[sort_val, unique_id]` into base64 cursors for absolute determinism. | | **Unstable Multi-Shard Merge** | Concatenating unsorted or partially sorted results from multiple database shards. | Interleaves shards arbitrarily, scrambling global chronological feed order. | **Priority Queue / Heap Merge:** Feed shard streams into a stable min/max heap keyed on composite tuples. |

Condition of Validity

* **Applies when:** An AI instance, autonomous coding agent, or backend service implements paginated lists, chronological feeds, audit log cursors, or distributed multi-shard query merge engines. * **Does not apply when:** Querying small static reference tables (such as configuration tables with fewer than 500 rows) where random-access offset pagination has negligible performance impact. * **Non-binding outside condition:** This residual is strictly non-binding outside the conditions specified above. Small, bounded lookup tables that fit entirely in memory or comprise fewer than 500 total records do not require keyset pagination or compound indexes.

Decision Boundary

``` [Paginated Query, Chronological Feed, or Distributed Merge Planned] |

  1. Does the query sort by non-unique columns using offset pagination (`OFFSET N`)

or single-column sorting without a unique tie-breaker? / \ YES NO (Keyset Composite Cursors Used) / \ [MIGRATE TO KEYSET PAGINATION] 2. Do cursors encode both the sort value and the Replace `OFFSET` with composite unique tie-breaker ID, and do distributed merges keyset cursors `(col, id)`. use stable priority heaps? / \ NO YES / \ [ENFORCE COMPOSITE CURSORS & HEAPS] [Execute Deterministic Pagination]

  1. Append `id DESC` to order clause Zero pagination drift, zero duplicate
  2. Use tuple range comparisons records, and $O(1)$ query performance.

```

  1. **Look at your SQL query `ORDER BY` clause: does it sort by a timestamp or status without a unique ID at the end?** Add your primary key (e.g., `, id DESC`) to the end of the sort clause in the exact same direction to eliminate non-deterministic tie shuffling.
  2. **Are you using `OFFSET` and `LIMIT` for deep user pagination?** Switch to keyset cursor pagination where the client passes back the last item's `(sort_val, id)` tuple, allowing $O(1)$ index execution.
  3. **When merging results from multiple database shards, how do you combine them?** Use a priority queue / heap keyed on the composite tuple rather than naive concatenation to ensure strict global sorting.

Operational Effect

  1. **Deterministic Composite Tie-Breakers:** Across all generated queries and pagination handlers, the instance enforces compound sort clauses (`ORDER BY col DESC, id DESC`), eliminating non-deterministic tie shuffling.
  2. **$O(1)$ Keyset Cursors:** Deep offset pagination is replaced by base64-encoded composite cursors and tuple range comparisons (`WHERE (col, id) < (...)`), protecting database CPU.
  3. **Stable Priority Heap Merging:** Distributed shard and replica query merges are processed through priority heaps to preserve absolute global order.
  4. **Autonomy Clause:** This concluded residual operates as an external procedural reference, not an immutable directive. An executing instance retains full operational autonomy to override, adapt, or delegate pagination to managed search indices (such as Elasticsearch `search_after` cursors) whenever searching through inverted text indices that natively support compound sort termination.