OFFSET Pagination Breaks at Scale — Here's What to Use Instead

Pagination usually starts with the obvious approach, and the obvious approach usually works, for a while:

SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 40;

"Skip the first 40 rows, give me the next 20." Simple, readable, and it's genuinely fine for page 2, page 5, even page 50 on a modest table. It stops being fine as the table grows and the offset climbs, and the reason why is invisible from the SQL itself — nothing here looks wrong.

Why OFFSET gets slower as it grows

OFFSET doesn't teleport the database to row 40,000. It can't — the underlying storage doesn't have a concept of "the 40,000th row" it can jump straight to. What actually happens is the database generates the first 40,020 rows in sort order and then throws away the first 40,000 of them, returning only the last 20. Every page further into the results means computing and discarding a larger and larger prefix that never gets used.

Page 2 with OFFSET 20 discards 20 rows — cheap, unnoticeable. Page 2,000 with OFFSET 40000 discards 40,000 rows to hand you 20. The query isn't doing 100x more useful work for page 2,000 than page 2 — it's doing the same tiny amount of useful work, plus an enormous and constantly growing amount of throwaway work that scales linearly with how deep into the results you've paged. That's the entire mechanism, and it's why "pagination got slow" reports almost always turn out to correlate with "users who paginate deep into result sets," not with total table size alone.

The second problem: OFFSET drifts when data changes

Performance is the loud problem. This one is quieter and worse, because it produces wrong results with no error at all. OFFSET counts rows by position in the current sort order, and that position isn't stable if rows are being inserted or deleted between page loads:

Someone is paginating through products sorted by creation date. They fetch page 1 (OFFSET 0). Before they load page 2, a new product gets inserted at the front of that sort order. When they fetch page 2 (OFFSET 20), position 20 has shifted — one row that would have appeared on page 1 now lands on page 2 instead, appearing twice across the two pages, while whatever fell off the end of page 1 as a result is never shown at all. Nothing errored. Nothing logged a warning. A row just silently duplicated or vanished, because OFFSET was never counting anything stable in the first place — it was counting "position right now," and "right now" kept changing underneath the query.

The fix: keyset pagination (a.k.a. cursor-based pagination)

Instead of asking the database to skip N rows, ask it to continue from a specific point you already know — the last row's own sort key, not its position:

-- First page
SELECT * FROM products ORDER BY id LIMIT 20;

-- Next page: remember the last id from the previous page (say, 20), then:
SELECT * FROM products WHERE id > 20 ORDER BY id LIMIT 20;

This is a completely different question to the database: not "skip some rows," but "give me rows after this specific point." With an index on id — which a primary key already has, for free — that WHERE id > 20 lookup is a direct index seek. It costs the same whether it's page 2 or page 2,000, because there's nothing to discard; the database jumps straight to the right spot in the index and reads forward. The performance cliff disappears entirely, at any depth.

It also fixes the drift problem, because "after id 20" means the same thing regardless of what got inserted or deleted elsewhere in the meantime — it's anchored to a specific row's identity, not to a row count that can shift.

The one real tradeoff: keyset pagination can't jump to an arbitrary page number — there's no way to ask for "page 47" directly, only "the next page after this cursor." For infinite-scroll feeds, "load more" buttons, and API pagination, this is rarely a real constraint since nobody actually types "take me to page 47" in those interfaces anyway. For a numbered page-jump UI — an admin table with page number links, for instance — OFFSET may still be the pragmatic choice, and it's worth explicitly weighing that against the depth your users actually page to in practice, not the theoretical worst case.

Ordering by a non-unique column needs a tiebreaker

Keyset pagination requires the sort key to be unique, or rows with identical values in that column can be skipped or repeated across pages — the exact bug it was supposed to fix. Sorting by created_at alone is a common case where this bites, since two rows can share a timestamp:

SELECT * FROM products
WHERE (created_at, id) > ('2026-01-15 10:00:00', 8420)
ORDER BY created_at, id
LIMIT 20;

Adding the primary key as a tiebreaker after the non-unique sort column makes the combined (created_at, id) pair unique, which is what makes the cursor comparison unambiguous again.

The one-sentence version

OFFSET pagination costs more with every page you go deeper, because it computes and discards everything before the offset on every single request, and it can silently skip or duplicate rows when the underlying data changes between pages. Keyset pagination replaces "skip N rows" with "continue after this specific row," which is a direct, constant-cost index lookup regardless of depth — worth the switch the moment your users page past the first handful of screens.

Query performance stops being abstract once you're staring at an actual execution plan — SQL Quest has hands-on lessons on exactly this. Free, runs in your browser.