Pagination Needs a Consistency Contract

A client fetches the newest three posts, waits a moment, then asks for the next three. Meanwhile another post is inserted at the front of the feed. An offset-based query now counts from a different starting point, so the second page repeats an item from the first. The queries are valid SQL; the API's traversal promise is underspecified.

Keyset pagination improves this situation by continuing after a value rather than skipping a number of current rows. It also changes what the cursor means. Before choosing the query, decide whether the endpoint offers a live feed, a stable export, or a search result whose ranking can change. Those products need different consistency guarantees.

Order by a unique tuple

A timestamp alone is rarely a complete ordering key. Several rows can share the same timestamp, so add a unique tie-breaker such as an integer identifier. For descending order by creation time and identifier, the next page selects tuples strictly smaller than the last tuple previously returned.

Keep the cursor fields non-null or define null ordering and comparison semantics explicitly. The sort direction, comparison operator, and index must agree. A query that orders one way but encodes the cursor another way can look correct on random development data and fail only when several records share a boundary timestamp.

This local SQLite example demonstrates both the insertion improvement and its limit. The integer timestamps are fixture values. All operations run sequentially; the mutations between requests represent changes another transaction could commit while a client reads a feed.

Reproduce an insertion and a moving row

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE posts (id INTEGER PRIMARY KEY, created_at INTEGER NOT NULL)")
db.executemany("INSERT INTO posts VALUES (?, ?)", [(i, i * 10) for i in range(1, 7)])
db.execute("CREATE INDEX feed_order ON posts (created_at DESC, id DESC)")


def page(cursor=None):
    if cursor is None:
        return db.execute("""SELECT created_at, id FROM posts
            ORDER BY created_at DESC, id DESC LIMIT 3""").fetchall()
    return db.execute("""SELECT created_at, id FROM posts
        WHERE (created_at, id) < (?, ?)
        ORDER BY created_at DESC, id DESC LIMIT 3""", cursor).fetchall()


first = page()
assert [row[1] for row in first] == [6, 5, 4]
cursor = first[-1]
db.execute("INSERT INTO posts VALUES (7, 70)")
offset = db.execute("""SELECT id FROM posts
    ORDER BY created_at DESC, id DESC LIMIT 3 OFFSET 3""").fetchall()
assert [row[0] for row in offset] == [4, 3, 2]  # repeated id 4
assert [row[1] for row in page(cursor)] == [3, 2, 1]

db.execute("UPDATE posts SET created_at = 80 WHERE id = 3")
assert [row[1] for row in page(cursor)] == [2, 1]  # moved row is missed
db.execute("INSERT INTO posts VALUES (8, 20)")
assert page(cursor) == [(20, 8), (20, 2), (10, 1)]  # tie is ordered by id

After the insertion, the offset query repeats identifier four because the current rows have shifted. The keyset query continues below the previously observed boundary and returns three, two, and one. It does not need to count past the new item at the front.

Then the example moves identifier three above that boundary by changing its sort value. A later keyset request misses it. The final assertion checks that two rows with the same timestamp still have a deterministic order. These are separate properties: a unique tuple removes tie ambiguity, while immutable sort values reduce movement during traversal.

A cursor does not create a snapshot

A live feed can reasonably exclude items inserted above the cursor and tolerate items disappearing after deletion. Describe that behavior to clients. If sort fields can change, explain that the traversal may miss or revisit an item. Client-side deduplication can hide repeated identifiers but cannot recover unseen rows that moved behind an already visited boundary.

For a complete export, use a stronger mechanism. A database snapshot can provide a consistent view while it remains valid, but holding a transaction open across a slow client's requests can retain old versions and consume resources. A background export job that writes an immutable artifact is often easier to bound and retry than a long-lived interactive snapshot.

Another option is an explicitly versioned dataset with a retained version identifier in every cursor. That requires the storage model to preserve the records visible in that version. A high-water mark on creation time alone can exclude newer inserts; it cannot reconstruct rows that were later updated or deleted. The cursor cannot promise history that the database no longer stores.

Bind the cursor to the query

Include a cursor format version, the last sort tuple, and the relevant query identity. Filters, tenant scope, sort direction, and any snapshot or index version must remain consistent across pages. Reusing a cursor from one tenant's query in another tenant's query should fail validation, not subtly produce an incomplete page.

An opaque encoded cursor can evolve without exposing internal database details. Signing it can prevent the client from altering its contents, but a signature is not authorization. Recheck the current caller's permissions on every request. Expired sessions, revoked access, and changed policies remain meaningful even when a cursor was issued earlier.

Validate the page size independently of the cursor and enforce a server-side maximum. Handle malformed, expired, and unsupported cursor versions with a documented error that tells clients whether to restart. Avoid accepting an arbitrary SQL fragment or sort expression from cursor data.

Make cost and product behavior visible

With a suitable index, keyset traversal can seek near the boundary instead of repeatedly scanning and discarding a growing offset. The actual plan still depends on filters, joins, selectivity, and the database. Inspect the execution plan for realistic query shapes; the fixture above proves semantics, not a production performance claim.

Exact total counts can be expensive and can disagree with a live sequence of pages. Decide whether the product needs a count, an estimate, or simply a next-page indicator. To determine whether another page exists, fetch one extra row and use the last returned row, not the extra row, as the next cursor boundary.

Test ties, inserts before and after the boundary, updates to sort fields, deletion, permission changes, and cursor expiry. A good pagination contract lets callers distinguish an ordinary moving view from a stable dataset. Once that distinction is explicit, the query and the user interface can make the same promise.

Sources and further reading

• SQLite row-value comparisons and scrolling windows
• PostgreSQL SELECT and LIMIT ordering
• PostgreSQL transaction isolation