PostgreSQL

Deep OFFSET pagination gets slower on every page

Q: Can an index make OFFSET fast?

No. An index makes the ordering free, but the skipped rows must still be produced and counted, so cost remains linear in the offset. That is a property of what `OFFSET` means, not of how the table is indexed.

Q: What do I lose by switching to keyset pagination?

Random access to arbitrary page numbers, and easy backwards jumps. You can only move relative to a cursor. Most product surfaces — feeds, infinite scroll, exports — never needed page numbers; a report that genuinely does is the case where a bounded offset remains acceptable.

Q: Why do rows get duplicated across pages with OFFSET?

Because offsets are positional. A row inserted before your current position shifts everything down by one, so the next page re-serves a row you already saw; a deletion shifts up and skips one. Keyset cursors name a position in the data, so they are immune.

Q: Is the total count really that expensive?

Often it is the dominant cost. An exact `COUNT(*)` with a filter must visit every matching row, so it scales with the result set rather than the page. Estimating from `reltuples` or capping the count usually removes more latency than fixing the page query.

Written and reviewed by Sahil Srivastav

PostgreSQLPaginationQuery performance

EXPLAIN ANALYZE SELECT * FROM events ORDER BY created_at DESC LIMIT 20 OFFSET 200000;

 Limit  (cost=284913.02..284941.51 rows=20)
         (actual time=3841.774..3841.802 rows=20 loops=1)
   ->  Gather Merge  (actual time=1122.318..3790.441 rows=200020 loops=1)
 Planning Time: 0.214 ms
 Execution Time: 3842.019 ms

What this error actually means

There is no error here, which is why this one survives for years. `OFFSET n` does not tell PostgreSQL to jump to row n. It tells PostgreSQL to produce rows in order, count off the first n, throw them away, and then return the ones you asked for. Page 10,000 therefore costs roughly 10,000 pages of work.

Read the `EXPLAIN ANALYZE` above closely: the `Limit` node returns 20 rows, but the node beneath it produced 200,020. That gap is the entire problem, and it grows linearly with page depth. The first page is instant, the hundredth is noticeable, and the ten-thousandth times out — from the same query, with the same plan, on the same index.

There is a second, subtler defect. Between two page requests, rows can be inserted or deleted, which shifts every offset. Users see duplicated rows across pages or miss rows entirely, and it is impossible to reproduce on demand. Offset pagination is not merely slow at depth; it is incorrect on a table that changes.

Causes, most common first

1OFFSET is inherently linear. The rows before the offset must be generated to be counted, even with a perfect index. No index can make `OFFSET` constant-time, because the database cannot know how many rows satisfy the filter without producing them.
2Sorting a full result set before limiting. If the `ORDER BY` is not served by an index, PostgreSQL sorts everything matching the filter before discarding the offset. Cost is then proportional to the whole table rather than to page depth.
3Total count computed on every page. The `COUNT(*)` that powers "page 1 of 4,312" is often more expensive than the page itself, and it is recomputed per request. Frequently the real cause of slow pagination when the page query looks fine.
4Non-deterministic sort key. Ordering by a non-unique column, such as `created_at` with duplicate timestamps, leaves row order within ties unspecified. Pages then overlap or skip rows even without concurrent writes.
5Clients walking every page. Exports, sync jobs, and crawlers page through the whole table. Aggregate cost is quadratic in row count, and it is usually the largest single query load on a mature table.

When you see it

Page 1 is fast, page 500 times out, with no error until `statement_timeout` fires
Crawlers and scrapers walking every page generate most of the load
Export and reporting jobs that paginate through everything get slower as data grows
Users report seeing the same row on two consecutive pages, or a row vanishing
`pg_stat_statements` shows a high `rows / calls` ratio for a query with a small `LIMIT`

How to diagnose it

Step 1

Compare rows produced against rows returned

Run `EXPLAIN ANALYZE` on a deep page. The actual row count on the node under `Limit` is what you are paying for; the difference between it and your page size is pure waste.

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM events ORDER BY created_at DESC LIMIT 20 OFFSET 200000;

Step 2

Find the callers using deep offsets

Rank by total time and look at `rows / calls`. A paginated endpoint whose average rows per call vastly exceeds its page size is doing offset work.

SELECT calls, mean_exec_time, rows / calls AS avg_rows, left(query, 80)
FROM pg_stat_statements WHERE query ILIKE '%OFFSET%'
ORDER BY total_exec_time DESC LIMIT 10;

Step 3

Time the count query separately

Run the page query and the total-count query independently. It is common for the count to dominate, in which case the pagination method is not the problem at all.

Step 4

Verify the sort key is unique

Check for duplicate values in the ordering column. Ties mean non-deterministic ordering, which is a correctness bug in any pagination scheme including keyset.

SELECT created_at, count(*) FROM events GROUP BY 1 HAVING count(*) > 1 LIMIT 5;

The fix

Switch to keyset pagination — also called cursor or seek pagination. Instead of "skip 200,000 rows", the query says "give me rows after this one", which an index can satisfy directly. Cost is constant regardless of depth, and it is stable under concurrent inserts because the cursor names a position in the data rather than a count.

Always make the sort key unique by appending a tiebreaker, normally the primary key. Order by `(created_at DESC, id DESC)` and compare the tuple `(created_at, id) < ($1, $2)`. PostgreSQL supports row-wise comparison directly, and it can use a composite index on exactly those columns. Without the tiebreaker, rows sharing a timestamp are silently skipped or repeated at page boundaries.

Build the composite index to match the ordering, including direction: `CREATE INDEX ON events (created_at DESC, id DESC)`. Verify with `EXPLAIN` that the plan is an index scan with no sort node.

Drop exact total counts. Replace "page 1 of 4,312" with infinite scroll or a "load more" control, or show an estimate from `pg_class.reltuples`, or cap the count (`SELECT count(*) FROM (SELECT 1 FROM events LIMIT 10000) t`) and display "10,000+". An exact count on a large table is rarely worth what it costs.

For jobs that must walk everything, keyset-paginate by primary key — it is the cheapest possible full scan and it is resumable after a crash, which offset pagination is not.

-- Linear in page depth, and unstable under concurrent inserts
SELECT * FROM events
 ORDER BY created_at DESC
 LIMIT 20 OFFSET 200000;

-- Constant cost at any depth, stable, resumable
SELECT * FROM events
 WHERE (created_at, id) < ($1, $2)   -- last row of the previous page
 ORDER BY created_at DESC, id DESC
 LIMIT 20;

CREATE INDEX events_created_at_id_desc_idx
    ON events (created_at DESC, id DESC);

How to stop it coming back

Make keyset pagination the default in your API shape — an opaque cursor token rather than a page number
Never expose a page-number API on a table that can grow without bound; the contract itself forces the slow implementation
Cap or estimate total counts rather than computing them exactly per request
Assert in tests that a page request touches O(page size) rows regardless of depth
Always include a unique tiebreaker in the sort key, in every pagination scheme

Practise this failure in a real repository

Gronex ships this as a runnable repository: a listing endpoint that degrades with page depth and drops rows at page boundaries under concurrent inserts. The tests assert both bounded work per page and that no row is duplicated or skipped while data changes.

FAQ

Can an index make OFFSET fast?