Offset pagination lies to you after page fifty

The claim

LIMIT 20 OFFSET 5000 has two defects, and the well-known one is the lesser. Yes, it gets slower linearly, because the database must produce and discard five thousand rows to hand you twenty. The worse defect is that under concurrent writes it silently skips and duplicates rows: an item inserted while a user browses shifts every subsequent page by one, so row 41 appears on two pages and row 60 on none. For anything a user scrolls, an export walks, or an API client syncs, keyset pagination fixes both defects at once.

Demonstrate the performance defect on your own data

EXPLAIN ANALYZE SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 100;
EXPLAIN ANALYZE SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 200000;

On a table of a million rows with an index on id, the first query returns in well under a millisecond and the second takes tens of milliseconds — the plan shows the index being walked through 200,020 entries to discard 200,000 of them. The cost grows with the offset, forever. Deep pages are also exactly what crawlers and misbehaving API clients request, so your slowest queries arrive from your least valuable traffic.

Demonstrate the correctness defect

Open two sessions. In the first, read page one, ordered by newest:

SELECT id FROM orders ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 0;

In the second session, insert a new order. Back in the first, read page two with OFFSET 20. The row that was 20th on page one is now 21st overall — and appears again at the top of page two. Delete a row instead and one record vanishes from the sequence entirely. No error is raised in either case. An export built this way double-counts and under-counts, and a finance team will eventually find the discrepancy before you do.

Keyset pagination

Instead of counting rows to skip, remember where you stopped and ask for rows after that point:

-- page 1
SELECT id, created_at, total
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- subsequent pages: pass the last row's values back
SELECT id, created_at, total
FROM orders
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;

The row comparison (created_at, id) < ($1, $2) is the idiomatic form and Postgres uses a composite index on (created_at DESC, id DESC) to jump straight to the position. Every page costs the same as page one, at any depth, and inserted or deleted rows cannot shift the window — the cursor is anchored to values, not to a count.

Two rules keep it correct. The sort key must be unique, which is why id is appended as a tiebreaker — created_at alone will skip rows that share a timestamp. And the cursor you hand to clients should be opaque — base64 of the key values — so nobody builds against its internals.

The trade-offs, honestly

• No jumping to page 37. Keyset gives you next and previous, not random access. For infinite scroll, exports, and API sync this costs nothing. For a genuine jump-to-page interface you need offset — but check your analytics first: in every product we have measured, direct deep-page navigation is a rounding error, and "next" plus search covers what users actually do.
• Total counts are a separate decision. COUNT(*) over a large filtered set can cost more than the page itself. Show "next" until an empty page, or display an estimate from pg_class.reltuples when approximate is acceptable.
• Multi-column sorts need the full key in the cursor. Sorting by status then date means the cursor carries both plus the id. Mechanical, but it must be done.

Migrating an API that already promised page numbers

If partners are integrated against ?page=37, do not break them; run both. Keep offset behaviour on the existing parameter, add a cursor parameter alongside it, and return a next_cursor field in every response either way. New integrations pick up the cursor because it is what the response hands them; existing ones keep working while you cap the damage with a documented maximum offset — rejecting page requests beyond, say, row 10,000 with a clear error pointing at the cursor is a defensible limit that ends the pathological queries without a coordinated migration.

Where each belongs

Offset is fine for a small admin table of a few hundred rows where page numbers are genuinely useful and writes are rare. Everything else — customer-facing lists, infinite scroll, CSV exports, webhook backfills, any API a partner will page through — gets keyset. The migration is contained: one query shape and one cursor parameter, and the deep-page latency and the reconciliation mysteries leave together.