A customer exported their transaction history and got 4,127 rows. The database said 4,203. Nobody could reproduce it, because the export had paginated through a table that was being written to at the time.
OFFSET is a count, not a position
SELECT * FROM transactions WHERE account_id = $1
ORDER BY created_at DESC
LIMIT 100 OFFSET 2000;
OFFSET 2000 means "skip the first 2000 rows of a result I am computing right now". If three rows were inserted at the top between page 20 and page 21, every subsequent page shifts by three and the reader never sees three rows. Delete three instead and three rows appear twice.
The cost is the other half of the problem: OFFSET 200000 still computes 200,000 rows and throws them away. On a busy table, page 2,000 takes seconds while page 1 takes milliseconds — a latency curve your monitoring will attribute to "the database".
Keyset pagination, done properly
The fix is to paginate on a value, not a count:
SELECT id, created_at, amount_cents
FROM transactions
WHERE account_id = $1
AND (created_at, id) < ($2, $3)
ORDER BY created_at DESC, id DESC
LIMIT 100;
The (created_at, id) row comparison is the important part, and it is where most attempts go wrong.
A cursor on created_at alone skips ties. If forty transactions share a timestamp — a batch job, a backfill, a now() default that resolves per transaction — then WHERE created_at < $2 skips all forty, or <= returns them again on the next page. Any cursor that is not unique is a page that either loses rows or repeats them.
The ORDER BY must match the tuple exactly, in the same direction. ORDER BY created_at DESC, id DESC with (created_at, id) < (...) is consistent. Mixing DESC on one and ASC on the other produces a filter that does not describe the sort, and Postgres will happily return rows in an order nobody asked for.
The index has to cover the tuple in that order. (account_id, created_at DESC, id DESC) makes the query a range scan with no sort step. Without it, keyset pagination is still correct but you are sorting the whole partition on every page.
Why this is a data-model bug
Both variants are the same mistake: the application assumes the query has defined an order when it has only defined a partial one. With OFFSET, the assumption is that the set does not change between pages. With a non-unique cursor, the assumption is that equal keys are distinguishable. Neither is true, and neither failure is visible in a test that pages through a static fixture.
The test that catches it pages while writing:
def test_pagination_does_not_skip_or_duplicate(db, client):
seen = []
cursor = None
for _ in range(50):
page = client.get("/transactions", params={"cursor": cursor}).json()
if not page["items"]:
break
insert_a_new_transaction(db) # the thing OFFSET cannot survive
seen.extend(i["id"] for i in page["items"])
cursor = page["next_cursor"]
assert len(seen) == len(set(seen)), "a row was returned twice"
assert set(seen) == {r.id for r in db.query("SELECT id FROM transactions")}
The second assertion is the one that fails on OFFSET: rows inserted above the cursor never appear, so the sets differ.
Practical notes
Return an opaque cursor (base64 of the tuple), not a page number. Clients treat numbers as indexes and will compute page 3 themselves, which puts the pagination contract back in their hands.
Decide what happens when the sort key is mutable. Keyset pagination assumes created_at never changes; a row edited to the top of the list can be seen twice. If the ordering column can change, order by (created_at, id) from an append-only table, or accept that the cursor describes a snapshot of that column.
The export that started this came back with 4,203 rows after the change, and the query time was flat across all 43 pages instead of climbing from 8ms to 900ms. Same database, same index budget. The only thing that moved was the arithmetic.
I write about production failures in Postgres, queues, and distributed systems.
Top comments (0)