Page 1 Was Fast. Page 400 Timed Out.

Pagination is the endpoint nobody reviews carefully, because the version everyone writes first is correct. It returns the right rows in the right order, the tests pass, and page one is fast.

It stays fast for as long as nobody goes deep, which in a product with a feed, a chat history or a follower list is not very long.

OFFSET is not a seek

The mental model that causes this is that LIMIT 20 OFFSET 8000 asks the database for rows 8,001 to 8,020, in the way you would index into an array. It does not. It asks the database to produce rows in order and discard the first 8,000.

An index on the sort column does not save you, and this is the part that surprises people who have already done the obviously correct thing and added the index. The index makes the ordering free. It does not make the skipping free, because a B-tree gives you sorted traversal, not random access by ordinal position. Nothing in the structure knows where the eight-thousandth row is without walking to it.

So the cost of a page is proportional to how deep the page is. Page one reads 20 rows. Page 400 reads 8,000 to return 20 of them. The query has not changed and the plan has not changed. Only the amount of discarded work has, and it grows without bound as the table does.

The second problem is subtler and worse. Offsets address positions in a result set, not rows. If something is inserted at the top of the feed between your request for page one and your request for page two, everything shifts down by one, and the row that was last on page one is now first on page two. Your reader sees it twice. Delete something instead and a row is skipped entirely. On a feed with active writes, offset pagination loses and duplicates rows as a matter of routine, and it does so quietly.

Keyset pagination asks a different question

The fix is to stop asking “give me the next page” and start asking “give me the rows after this specific row”.

SELECT ... FROM posts
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20

Now the database seeks into the index at the cursor and reads forward twenty times. Page 400 costs exactly what page one costs. And because the cursor names a row rather than a position, concurrent inserts and deletes elsewhere in the table cannot shift it.

The three parts have to agree

This is where implementations go wrong, and the failure is silent rather than slow, which is worse.

Sorting by created_at alone is not enough, because timestamps collide. Four posts created in the same second have no defined order between them. If page one ends in the middle of that tie and page two asks for everything with created_at < 09:00:00, the tied rows you have not seen yet are excluded. They are not late. They are gone, and nothing reports an error.

The sort key has to be unique, which in practice means appending a tiebreaker: the primary key. Then (created_at, id) is a total order, the cursor identifies exactly one row, and there is no ambiguity to fall into.

Three things must then match each other exactly:

The ORDER BY must list both columns, with directions. The cursor must carry both values, and the comparison must be a row-value comparison rather than created_at < $1 OR (created_at = $1 AND id < $2) reassembled by hand. The tuple form is easier to get right and easier for the planner to match against an index.

The index must be composite, in the same column order, with the same directions. An index on (created_at DESC, id DESC) serves that query with a single seek. An index on created_at alone leaves the engine sorting within ties. An index on (id, created_at) is a different index and will not be used for this at all.

Get two of the three right and you have a query that is correct in testing, where every timestamp is unique because the fixtures were inserted one at a time.

What you give up, and the trap in getting it back

Keyset pagination cannot jump to page 400. You can only go forwards and backwards from where you are. For a feed, a chat log, or an activity list, that is what the interface offers anyway. For an admin table with numbered pages, it genuinely does not fit, and offset is the right answer there because the table is small and the depth is bounded.

You also lose the total count, and this is where people reintroduce the problem they just removed. SELECT COUNT(*) over a filtered set has to visit every matching row. On the table that was too slow to paginate, the count is the most expensive query on the page, and it runs on every page.

If the number is decoration, drop it or approximate it from statistics. If it is genuinely load-bearing, maintain it as a counter rather than deriving it on read.

What I would tell someone building this

Decide whether your product needs page numbers or a next page. They are different features with different costs, and almost every infinite-scroll interface is quietly paying for the wrong one.

Make the sort key unique. A tiebreaker column is not pedantry. Without it, a cursor cannot name a row, and the rows lost at tie boundaries never show up in an error log.

Check the index direction, not just the columns. A composite index in the wrong order or the wrong direction reads as “indexed” in a schema review and does nothing for the query.

Never let a count run on every page. The count is usually the slowest query in the endpoint and the least valuable thing on the screen.