Pagination, at page 1 and at page 20,000
OFFSET is not a seek. It reads every row it skips, so page 20,000 costs twenty thousand pages more than page 1 — and the last page is the slowest thing your app does.
12 min · runs against the events dataset
LIMIT 20 OFFSET 0 is cheap. LIMIT 20 OFFSET 400000 reads four hundred thousand rows, throws all of them away, and returns twenty. The database has no way to jump: the only way to know which row is the four-hundred-thousandth is to count past the ones before it.
Page one. Almost nothing.
plan
| node | est | actual | loops | pages | share |
|---|---|---|---|---|---|
| Limit | 20 | 20 | 1 | 0 | |
| → Index Only Scan using events_pkey on events | 8K | 20400x under | 1 | 0 |
The same query, later in the list. Compare the rows examined.
plan
| node | est | actual | loops | pages | share |
|---|---|---|---|---|---|
| Limit | 20 | 20 | 1 | 0 | |
| → Index Only Scan using events_pkey on events | 8K | 7K | 1 | 0 |
Keyset pagination
Instead of counting from the start, remember where you stopped: WHERE id > :last_seen ORDER BY id LIMIT 20. With an index on id that is a seek to a position and twenty rows, and it costs the same on page 20,000 as on page 1.
Deep in the table, and as cheap as the first page.
plan
| node | est | actual | loops | pages | share |
|---|---|---|---|---|---|
| Limit | 1 | 0 | 1 | 0 | |
| → Index Only Scan using events_pkey on eventsIndex Cond: (id > 140000) | 1 | 0 | 1 | 3 |
The cost is that you lose “jump to page 500”, because there is no page 500 — there is only “after this row”. That is usually the right trade for an infinite scroll or an API cursor, and the wrong one for a table with numbered pages that nobody past page three ever visits.
Did it land?
- The twentieth page, cheaply
Return the next twenty event ids after id 4000, in ascending order. Do it the way that costs the same on page twenty thousand as on page one.