Pagination is harder than it looks

LIMIT 20 OFFSET 40 is the first thing everyone writes and it is wrong in two distinct ways. Both are invisible on a development dataset and both bite in production.

problem one: it gets slower the deeper you go#

OFFSET 100000 does not skip to row 100,000. The database fetches and discards 100,000 rows first. Page one is instant; page five thousand is a table scan.

This is why "the last page of results times out" is such a common bug report, and why some APIs simply cap how deep you can page.

problem two: it is wrong when the data changes#

Between the user's request for page 1 and page 2, a row is inserted at the top. Every subsequent row shifts down by one. The last item of page 1 is now the first item of page 2 — the user sees it twice, and something else is skipped entirely.

For a feed sorted by recency, where new items arrive constantly, this happens on almost every request. Users experience it as "the list is glitchy," and it is essentially never diagnosed, because it is invisible unless you are looking for it.

the fix: keyset pagination#

Instead of counting rows to skip, remember where you stopped:

-- page 1
SELECT id, created_at, title FROM posts
WHERE feed_id = $1
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- page 2: everything strictly after the last row seen
SELECT id, created_at, title FROM posts
WHERE feed_id = $1
  AND (created_at, id) < ($2, $3)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Constant time at any depth — the index seeks directly to the position. And stable under inserts: new rows appear at the top and do not shift anything the user has already seen.

The tuple comparison is the part people get wrong. (created_at, id) < (...) is a row comparison, not two independent conditions. Writing it as created_at < $2 AND id < $3 is a different and incorrect query. The tie-breaker column is required — without it, rows sharing a timestamp are skipped or repeated.

The index must match the sort exactly, including direction:

CREATE INDEX ON posts (feed_id, created_at DESC, id DESC);

the API shape#

Do not expose the raw column values as the cursor. Encode them opaquely:

{
  "data": [...],
  "next_cursor": "eyJjIjoiMjAyNi0xMC0wOVQxMjozNCIsImkiOjQ4MjEzfQ",
  "has_more": true
}

An opaque cursor means you can change the underlying sort and pagination scheme without breaking clients, and clients cannot construct one by hand and depend on its structure.

Include has_more rather than making clients infer the end from a short page — that inference is wrong when the last page happens to be exactly full.

what you give up#

Jumping to page N. Keyset pagination is next and previous only. There is no "page 47."

For most applications this is fine and arguably better — infinite scroll and "load more" are the dominant patterns and they only need forward. For an admin table where users genuinely want page numbers, either keep offset pagination and accept the cost on a smaller dataset, or provide filtering that reduces the result set instead of paging through it.

Total counts. COUNT(*) on a large filtered set is expensive regardless of pagination scheme. Options: show an approximate count, show "1,000+", or drop it. Most users do not need the exact number and the query to produce it is often more expensive than the page itself.

the other things people get wrong#

Unstable sort. Sorting by a non-unique column with no tie-breaker produces a different order between queries, and rows disappear or duplicate. Always append a unique column to the sort.

No maximum page size. ?limit=1000000 should be rejected, not attempted.

Different sorts, same cursor. If the client can change the sort order, the cursor from the old order is meaningless. Encode the sort in the cursor and reject a mismatch rather than returning nonsense.

Deleted cursors. If the row a cursor points at is deleted, a strict < comparison still works correctly — which is a good reason to encode the values rather than a row reference.