LIMIT 50 OFFSET 500000. Page 10001 of an events table, from a paginator someone wrote in an afternoon. Postgres has no shortcut to row 500000. It walks the index through half a million entries, fetches them, throws them away, and returns fifty. Page one is fast. Page ten thousand is slow, and every page after it is slower. Run EXPLAIN ANALYZE on both: the plans are identical, the numbers are not.
Keyset pagination stops counting and starts seeking. The client sends the position of the last row it saw instead of a page number:
SELECT id, created_at, payload
FROM events
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 50;
With an index on (created_at, id) this is one descent into the index and fifty rows out. Page one and page ten thousand cost the same.
The ordering must be total. That is why id is there: created_at alone has duplicates, and a duplicate on a page boundary means a lost row or a repeated one. The pair from the last row is the cursor. Base64 it into the next-page link, nothing more is needed. A side effect I did not expect to like: inserts no longer shift the pages. With OFFSET a new row pushes everything down and page four shows you a row from page three. Keyset does not notice.
The limitation is known. No jump to page 7000, only next and previous. Before calling this a problem, grep the access logs of the paginator you have. On the project where I did this nobody went past page nine in a month. People do not browse to page 7000. Robots do, and robots I am happy to slow down.