Why OFFSET pagination slows down

How OFFSET pages work

The usual way to page through a list:

-- page 1
SELECT id, created_at, payload FROM events
WHERE org_id = 7
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 0;

-- page 5,000
… LIMIT 20 OFFSET 99960;

OFFSET 99960 reads as “skip 99,960 rows”. But rows have no position until they are sorted, and the database has no way to find the 99,961st row in an order without producing the 99,960 before it. Skipped rows are still computed; they are just not sent.

What it costs

We built an events table with one million rows across ten organizations, 100,000 per organization, with an index on (org_id, created_at, id). Then we fetched pages for organization 7 at increasing depth, measuring each query’s second run with EXPLAIN (ANALYZE, BUFFERS):

The work grows in step with the page number. Page 4,999 does roughly 2,000 times the work of page 1 to return the same 20 rows. PostgreSQL 18.3 on an Apple Silicon laptop, with everything in memory; on a cold cache or a busier server, the deep pages are worse.

What the deep page actually did

 Limit (actual rows=20.00 loops=1)
   Buffers: shared hit=11865, temp read=724 written=727
   ->  Sort (actual rows=99980.00 loops=1)
         Sort Key: created_at DESC, id DESC
         Sort Method: external merge  Disk: 5792kB
         ->  Bitmap Heap Scan on events (actual rows=100000.00 loops=1)
               Recheck Cond: (org_id = 7)
               ->  Bitmap Index Scan on events_org_created_id (actual rows=100000.00 loops=1)
                     Index Cond: (org_id = 7)

Read it from the bottom. With 99,980 rows needed anyway, the planner decided it was cheaper to fetch all 100,000 of the organization’s rows and sort them than to walk the index in order. The sort did not fit in memory, so it wrote 5.8 MB to disk (external merge). Then Limit kept 20 and threw the rest away. Every user who opens that page repeats all of it.

Check yourself

1. Why can’t PostgreSQL jump straight to the rows at OFFSET 99960?

   Show answer

   Rows have no fixed position; their order exists only once they are sorted. To know which rows come after the first 99,960, it must produce those 99,960 in order first.

   I could explain this without looking

2. In the measurements, how did the work grow with the page number?

   Show answer

   Roughly in proportion: about 2,000 rows and 240 pages at page 100, 20,000 rows and 2,375 pages at page 1,000, and almost 100,000 rows and 11,859 pages at page 4,999.

   I could explain this without looking

3. Why did the deep page sort on disk instead of reading the index in order?

   Show answer

   It needed nearly all of the organization’s 100,000 rows. The planner estimated that fetching them all and sorting was cheaper than walking the index row by row; the sort was too big for memory, so it spilled to disk.

   I could explain this without looking