Pagination at Scale: OFFSET vs Keyset

Scenario

Example app: a public API lists an account's transactions (GET /transactions?page=N&per_page=50). A large customer's integration walks through all 2 million transactions every night to sync them.

What you see in production

Early pages take 5 ms; page 30,000 takes 4 seconds. The nightly sync takes hours, hammers the primary, and the customer reports duplicated and missing transactions: new transactions arriving during the sync shift every page by a few rows.

The naive code

SELECT * FROM transactions
WHERE account_id = $1
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 1499950;          -- page 30,000

page = Paginator(Transaction.objects.filter(account=acct).order_by("-created_at", "-id"), 50).page(n)

Transaction::where('account_id', $id)->orderByDesc('created_at')->orderByDesc('id')->paginate(50);

Reproduce it

Count the rows the database must read for each page, and what happens when a new row arrives between page requests:

rows = list(range(1000, 0, -1))          # ids, newest first

def offset_page(page, size=3):
    start = (page - 1) * size
    return rows[start:start + size], start + size        # rows read = offset + limit

def keyset_page(after=None, size=3):
    idx = 0 if after is None else rows.index(after) + 1  # an index seek in a real database
    return rows[idx:idx + size], size

print("rows read for page 300: OFFSET", offset_page(300)[1], "vs keyset", keyset_page(rows[896])[1])

p1, _ = offset_page(1)
rows.insert(0, 1001)                     # a new transaction arrives during the sync
p2, _ = offset_page(2)
print("OFFSET pages:", p1, p2, "-> id", p1[-1], "appears twice")

rows.remove(1001)
k1, _ = keyset_page()
rows.insert(0, 1001)
k2, _ = keyset_page(after=k1[-1])
print("keyset pages:", k1, k2, "-> no duplicates, no gaps")

rows read for page 300: OFFSET 900 vs keyset 3
OFFSET pages: [1000, 999, 998] [998, 997, 996] -> id 998 appears twice
keyset pages: [1000, 999, 998] [997, 996, 995] -> no duplicates, no gaps

Root cause

OFFSET n doesn't skip rows for free: the database reads and discards n rows on every request, so cost grows linearly with page number. And offsets are positions, not identities — inserts and deletes between requests shift the window, producing duplicates and gaps.

The fix

Keyset (seek) pagination: remember the last row's sort key and ask for rows after it, using an index on the same columns:

CREATE INDEX transactions_account_created_id ON transactions (account_id, created_at DESC, id DESC);

SELECT * FROM transactions
WHERE account_id = $1
  AND (created_at, id) < ($last_created_at, $last_id)     -- row comparison (Postgres, MySQL 8)
ORDER BY created_at DESC, id DESC
LIMIT 50;

qs = Transaction.objects.filter(account=acct).order_by("-created_at", "-id")
if cursor:
    created, last_id = decode_cursor(cursor)
    qs = qs.filter(Q(created_at__lt=created) | Q(created_at=created, id__lt=last_id))
page = list(qs[:50])
next_cursor = encode_cursor(page[-1].created_at, page[-1].id) if len(page) == 50 else None

// Spring Data 3.1+: Window / ScrollPosition implement keyset scrolling
Window<Transaction> page = repo.findFirst50ByAccountIdOrderByCreatedAtDescIdDesc(accountId,
        ScrollPosition.keyset());

$page = Transaction::where('account_id', $id)
    ->orderByDesc('created_at')->orderByDesc('id')
    ->cursorPaginate(50);                       // returns an opaque next_cursor

const page = await prisma.transaction.findMany({
  where: { accountId }, orderBy: [{ createdAt: "desc" }, { id: "desc" }], take: 50,
  ...(cursor ? { cursor: { id: cursor }, skip: 1 } : {}),
});

Return an opaque cursor (base64 of the last sort key) instead of page numbers.

Trade-offs

• No "jump to page 731" — fine for feeds, APIs and infinite scroll; admin tables that need page numbers can cap depth (e.g. 100 pages) and offer filters/search.
• The sort key must be unique (add id as a tiebreaker) or rows with equal timestamps get skipped.
• Total counts (count(*)) are expensive on large tables; show "more results" instead of an exact number, or an estimate.

Detect it in production

• Latency by page number (or by OFFSET value) in API logs — a rising slope is the OFFSET signature.
• Customers reporting duplicates/missing items during exports.
• pg_stat_statements entries with huge rows relative to calls × LIMIT.

Key points

• OFFSET reads and discards rows, and shifts when data changes.
• Keyset pagination seeks to the last seen key with an index: constant cost, stable pages.
• Expose opaque cursors and use a unique sort key.