Español

Swap Offset Pagination for Keyset Pagination

On a large, changing table, offset pagination is a bug waiting to happen. LIMIT 20 OFFSET 100 looks harmless in a code review. It isn't. On a big, mutable table it does two things nobody wants: it gets slower every page, and it silently skips and repeats rows while people are still inserting and deleting underneath you. Keyset pagination fixes both — and it's the pattern reviewers expect once your table stops being small.

Why offset drifts

OFFSET N tells the database: fetch the rows in order, then throw the first N away. Two consequences fall out of that one sentence.

• It scans what it discards. To answer OFFSET 100000, the engine still reads and orders 100,000 rows before it can return the next 20. The work grows linearly with the offset, so page 5,000 is far slower than page 1 for the exact same page size.
• It drifts under writes. Offset counts positions, not rows. Insert a row that sorts before your current page between two requests and every later row shifts down by one — so the first row of your next page is one you already showed. Delete a row and the opposite happens: a row slides up past the boundary and never appears. On an active table, users see duplicates and gaps that no amount of retrying will fix.

Page by key, not by position

Keyset pagination (also called seek or cursor pagination) never counts positions. It remembers the sort key of the last row on the page and asks for rows after that key:

-- Page 1
SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Next page: pass the last row's (created_at, id) back in
SELECT id, title, created_at
FROM articles
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

The WHERE clause anchors to a value, not a count, so inserts and deletes elsewhere in the table can't shift your boundary. And with an index on (created_at, id), the database seeks straight to the anchor instead of walking past everything before it — page 5,000 costs the same as page 1.

The one rule that makes or breaks it

Keyset needs an explicit, unique, stable sort order. Never rely on implicit ordering — a query with no ORDER BY returns rows in whatever physical order the engine likes, and that order can change under you.

The whole guarantee rests on the cursor pointing at exactly one row. Any non-unique sort column needs the primary key appended so the (sort_key, id) tuple is unique.

Power user notes

• Row-value comparison, not chained ANDs. WHERE (created_at, id) < (:c, :id) is the correct, index-friendly form. Hand-expanding it into created_at < :c OR (created_at = :c AND id < :id) is equivalent but easy to get wrong — most modern databases support the tuple syntax directly.
• The tradeoff is real. Keyset can't jump to an arbitrary page number — there's no "go to page 500," only next/previous. That's a fit for infinite scroll and APIs, less so for a numbered pager. If you truly need page numbers on a huge table, that's a product decision, not a default.
• The index must match the sort. A composite index on the exact (created_at, id) order is what turns the seek into a single index descent. Without it you keep the correctness win but lose the speed.
• Encode the cursor. Ship the last key as an opaque token (base64 of the tuple) rather than raw column values, so clients treat it as a handle and you can change the internals later.

Resources

• No Offset — Use The Index, Luke — why OFFSET is slow and drifts, with the seek-method SQL.
• Keyset Cursors, Not Offsets, for Postgres Pagination — Sequin — the tie-breaker and row-value comparison in Postgres.
• Offset and Keyset Pagination with Spring Data JPA — Thorben Janssen — the same pattern applied in an ORM.