Why is OFFSET pagination slow?

Because skipped rows are not free. The documentation states it directly: the rows an offset skips still have to be computed inside the server, and then discarded. So the first page costs almost nothing, the hundredth costs a hundred pages of work, and the cost grows with how deep any individual reader goes. Nothing about that is visible in a test where nobody clicks past page three.

Remembering where you stopped instead

The replacement is to carry the position forward. Order by a key, return the last row’s key with the page, and make the next request ask for rows after that key rather than for a numbered offset. With an index on the ordering key, every page costs the same as the first, forever, because the server jumps straight to the starting point rather than counting up to it.

Two requirements make it work. The ordering key has to be unique, or made unique by appending a tiebreaker such as the primary key, otherwise rows on the boundary are skipped or repeated. And the index has to match the sort order exactly, including direction and any secondary column, or the server sorts anyway and the benefit disappears.

What you give up is jumping to page four hundred directly, which is a feature almost nobody uses and which the offset version was serving badly in any case.

The other problem with deep pages

Even leaving performance aside, a paged result without a total order is not consistent between requests. The documentation is explicit that different offsets give unpredictable subsets unless the ordering constrains the rows into a unique order, and this is not a bug but a consequence of how the language is defined. Rows inserted between two requests shift the window, so a reader paging through a busy table sees some rows twice and misses others. Position-based paging is immune to the shifting for rows before the cursor, which is a correctness argument rather than a speed one.

The page count usually shipped alongside has its own cost, and it is the one in why count is slow. Where the interface allows it, an infinite list with no total is both faster and more honest.

If a keyset query is still slow, the index is the thing to check: the planner may be sorting because no index matches the order, which is the ordinary form of choosing a sequential scan, and why Postgres is not using my index covers the two reasons. Before adding one, unused and missing indexes covers whether it earns the write cost.