Cursors That Keep Their Word: Stable Pagination Under Concurrent Writes

Pagination is usually treated as a presentation detail. In a concurrent system, it is a contract. A client asks for page two and expects the next set of records, not a repeat of page one and not a silent gap. OFFSET and LIMIT look like they provide that contract, but they actually promise something weaker: 'skip N rows in whatever order the database returns them.' When rows are inserted or deleted, N no longer points where it did. The API has not changed, but its promise has quietly broken.

Where OFFSET loses its place

Consider a documents table ordered by creation time descending. Page one asks for the first 20 rows. Before page two arrives, a new document is inserted at the top. Page two asks for rows 21 through 40 of the current ordering. Every row has shifted down by one, so the last row of page one appears again at the top of page two. A deletion has the opposite effect: a row that should have appeared on page two is skipped entirely.

This is not an edge case reserved for heavy traffic. It happens in ordinary editorial workflows, background imports, and retry storms. It also happens even when the underlying data is not changing, if the query lacks a deterministic order. Two rows with the same timestamp can swap places between requests, producing duplicates and omissions without a single write.

Derive a stable order

A cursor is a continuation token, not a page number. To make one meaningful, the result set needs a total order that is stable across requests. A unique, immutable column is ideal. If the business order is by created_at, then created_at alone is not enough, because timestamps can tie. Add a unique tie-breaker such as id and order by both columns: ORDER BY created_at DESC, id DESC.

With that order, the cursor can carry the last seen key: the created_at value and the id of the final row on the page. The next query asks for rows strictly after that key in the same ordering. This is keyset pagination. It does not skip by position; it navigates by value. If a row is inserted ahead of the cursor, it will not appear in later pages, which is correct for a continuation. If a row is inserted behind the cursor, it can appear naturally on a later page. Deletions do not shift the window.

The cursor should be opaque to clients. Encode a small payload, base64url it, and include a version field. Clients treat it as a token, not as a query fragment they can construct. That preserves your ability to change the internal key later.

An endpoint with tie-breakers

A compact Python endpoint makes the contract concrete. The cursor payload carries the sort key and the tie-breaker. The SQL predicate uses row-value comparison so the database can use a composite index.

import base64, json

def encode_cursor(payload):
    raw = json.dumps(payload, separators=(',', ':')).encode()
    return base64.urlsafe_b64encode(raw).decode().rstrip('=')

def decode_cursor(token):
    pad = '=' * (-len(token) % 4)
    return json.loads(base64.urlsafe_b64decode(token + pad))

@app.get('/documents')
def documents(cursor=None, limit=50):
    limit = max(1, min(limit, 100))
    where, args = '', []
    if cursor:
        c = decode_cursor(cursor)
        where = 'WHERE (created_at, id) < (%s, %s)'
        args = [c['created_at'], c['id']]
    rows = db.execute(
        f'SELECT id, created_at, title FROM documents {where} '
        'ORDER BY created_at DESC, id DESC LIMIT %s',
        args + [limit]
    ).fetchall()
    next_cursor = None
    if len(rows) == limit:
        last = rows[-1]
        next_cursor = encode_cursor({
            'v': 1,
            'created_at': last['created_at'].isoformat(),
            'id': last['id']
        })
    return {'items': rows, 'next_cursor': next_cursor}

The composite index matters: (created_at DESC, id DESC). Without it, the row-value predicate may still be correct but expensive. With it, the database can seek directly to the cursor position instead of scanning and discarding rows. The LIMIT stays bounded, and the API returns a cursor only when the page is full; an empty or short page signals the end.

Testing concurrent writes

Pagination tests that only use a static fixture will miss the failure mode. Add concurrent-write cases around the page boundary. Insert a row newer than the first page and confirm that a client following cursors does not see a duplicate of the old boundary row. Insert a row older than the cursor and confirm that it can appear on a later page without disrupting the continuation. Delete the row whose key was used as the cursor; the predicate should still work because it compares values, not offsets. If the sort key is mutable, add a test that changes it and document the consequence: a cursor is a position in an ordering, so changing a row's ordering key can move it out of the traversal.

For mutable business ordering, a cursor alone cannot promise a consistent snapshot. Use an immutable version column, a materialized ordering key, or a snapshot identifier. The contract should state which one applies.

Migrating from OFFSET

Teams rarely get to rewrite every client at once. Support both modes during a transition: accept offset for existing callers and cursor for new ones, but always return next_cursor in the response. Document that offset is deprecated and set a removal date. Add the composite index before shifting traffic, then monitor duplicate and gap reports from clients. Finally, remove offset support and update the API reference with the cursor contract: opaque token, stable ordering, and a clear statement about what concurrent inserts mean.

The result is not merely faster deep pages. It is an API that keeps its word when the data changes underneath it.