Pagination at scale: why OFFSET dies and keyset cursors survive

The infinite-scroll feed is smooth for the first ten pages, then the support tickets arrive: “the app freezes when I scroll a lot.” The endpoint is ?page=10000&size=20. In staging it returned in 8ms; in prod, against a 50-million-row table, that same query takes 8.2 seconds and sometimes times out. Nobody changed the code. The query plan was always going to do this — it reads two hundred thousand rows, throws away all but the last twenty, and the deeper a user scrolls the slower it gets. The bug was in the pagination strategy, present from day one, invisible until the table grew.

Why OFFSET gets slower the deeper you go

LIMIT 20 OFFSET 200000 reads like “skip to row 200000,” but the database has no way to skip. It must produce rows in sorted order, count off the first 200,000, discard them, and only then start returning your 20. The work is proportional to the offset, not the page size. Page 1 is instant; page 10,000 is two hundred thousand rows of wasted I/O on every request — and that cost repeats for the next page, and the next.

-- Offset pagination: the deeper the page, the more rows scanned and thrown away
SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 200000;   -- DB reads 200020 rows, returns 20

The numbers are brutal and consistent across benchmarks. On a multi-million-row table, offset page 1,000 lands around 890ms while page 10,000 climbs to roughly 8.2 seconds — an order of magnitude slower for the same payload, purely because there are more rows to skip. Keyset pagination over the same data stays flat: 8ms, 9ms, 11ms, 12ms, page after page, because it never counts past rows at all. Reported speedups run 17x on million-row sets and up to 177x at the deep pages where offset falls apart.

Result drift: the bug OFFSET hides even on small tables

Even when offset is fast enough, it is wrong under concurrent writes. Page boundaries are computed by position in a live, shifting result set. Insert a row near the top between page 1 and page 2 and every later row shifts down by one: the item that was the last on page 1 reappears as the first on page 2 (a duplicate the user sees twice). Delete a row instead and one item is skipped entirely — the user never sees it. This drift is not a load problem; it happens on a tiny table the moment two requests straddle a write, which on any active feed is constantly.

Keyset (cursor) pagination: seek, don’t skip

Keyset pagination replaces “skip N rows” with “give me the rows after this specific row.” You sort by a stable, unique, indexed key and carry the last row’s key values forward as the cursor. The next query becomes a range seek the index can satisfy directly, with no counting.

-- Keyset pagination: seek straight past the last row seen, O(log n) regardless of depth
SELECT id, title, created_at
FROM articles
WHERE (created_at, id) < ('2026-05-01 09:00:00', 918273)  -- last row of previous page
ORDER BY created_at DESC, id DESC
LIMIT 20;

Two non-negotiables make this work. First, the sort key must be unique and stable — created_at alone is not unique (two rows can share a timestamp, causing a row to be dropped or duplicated at the page seam), so you append a tiebreaker like the primary key: order by (created_at, id) and compare the tuple as a whole. Second, you need a composite index in the exact column order and direction of your ORDER BY:

CREATE INDEX idx_articles_created_id ON articles (created_at DESC, id DESC);

Without that index the row-comparison WHERE still works but the planner falls back to a sort+scan and you lose the flat latency. With it, every page is a single index range scan — the database jumps to the cursor position and reads exactly 20 rows.

The cursor is opaque, and that is a contract decision

Never expose raw (created_at, id) to clients. Encode the keyset into an opaque string — Base64-encoded JSON of the sort values is the common pattern — and return it as nextCursor. The client treats it as a black box and sends it back verbatim. This buys you the freedom to change the sort key, add fields, or switch the underlying store later without breaking the client contract; it also prevents clients from hand-crafting cursors that scan into expensive territory. GraphQL’s Relay Connection spec formalizes exactly this: edges carry per-row cursor values, and pageInfo carries hasNextPage / endCursor. For bidirectional paging you support both after/first and before/last, flipping the comparison operator (< becomes >) and the ORDER BY direction, then reversing the returned slice so the client sees a stable order.

The hidden cost: total counts

Even with keyset, the “Showing 1–20 of 4,812,339” footer is its own performance trap. In Postgres, COUNT(*) cannot be answered from metadata because MVCC means every transaction sees a different set of visible rows — the engine must walk the rows to count the ones visible to you. On a large table that is a full scan that gets slower as the table grows, often dwarfing the page query it accompanies. The senior moves: drop the exact total (most products don’t need it), use a fast estimate from pg_class.reltuples (kept current by ANALYZE, normally within ~10% of the true count), or fetch LIMIT n+1 and report only “there is a next page” — which is all infinite scroll ever actually needs.