Showing a long list in pages
A table has a million rows and the screen shows ten. So the app asks for ten at a time: page 1, page 2, page 3. This is pagination, and there are two ways to ask for "the next ten".
One of them gets slower with every page.
OFFSET: count from the start
The familiar way is to say how many rows to skip.
SELECT * FROM posts ORDER BY id LIMIT 10 OFFSET 0; -- page 1
SELECT * FROM posts ORDER BY id LIMIT 10 OFFSET 10; -- page 2
SELECT * FROM posts ORDER BY id LIMIT 10 OFFSET 1000; -- page 101It is easy to write and it lets a user jump straight to any page number. But think about what "skip 1,000 rows" asks of the database. It has no way to know where row 1,001 starts. It must read the first 1,000 rows, throw them away, and then return the next 10.
Keyset: continue from where you stopped
The other way is to remember the last row you showed and ask for what comes after it.
SELECT * FROM posts ORDER BY id LIMIT 10; -- page 1: ends at id 10
SELECT * FROM posts WHERE id > 10 ORDER BY id LIMIT 10; -- the next 10
SELECT * FROM posts WHERE id > 20 ORDER BY id LIMIT 10; -- and the nextWith an index on id, the database walks straight to the first row after the given id and reads ten rows. Nothing is skipped and nothing is thrown away. This is called keyset pagination, or cursor pagination.
Watch the gap open
LIMIT 10 OFFSET 00 rows read
WHERE id > (last id seen) LIMIT 100 rows read
Pink: rows read and thrown away. Green: rows returned to you.
Start. A list of a million rows, shown 10 per page. Both methods are asked for the same pages, further and further in.
- Page
- –
- OFFSET reads
- 0
- Keyset reads
- 0
On page 1 they are identical, and that is the trap. Nobody tests page 100,000. Then a search crawler, or an export job, walks through every page, each one slower than the last, and the database spends its time reading rows that are immediately discarded.
Example
Walking the whole table
To export a million rows in pages of 10 using OFFSET, the database reads 10 rows for page 1, 20 for page 2, 30 for page 3, and so on. Added up over 100,000 pages, that is about 50 billion rows read to deliver one million.
With keyset pagination it reads one million.
The second problem with OFFSET: shifting rows
OFFSET counts positions, and positions change when data changes.
You load page 1: the 10 newest posts
Positions 1 to 10.
Someone publishes a new post
Everything moves down one place. The post you saw at position 10 is now at position 11.
You load page 2: positions 11 to 20
The first post on it is one you have already seen.
Deletions do the opposite and cause a row to be skipped. Keyset pagination is immune, because "after id 10" means the same thing whatever was added or removed.
What keyset costs
Good at
- Every page costs the same, however deep
- No duplicates or gaps when rows are added or removed while paging
Where it bites
- No jumping to page 500: only next and previous
- Needs a sort order that is unique, backed by an index
- Slightly more complex when sorting by a column that can repeat
Sorting by a column with repeats, such as a date, needs a tie-breaker so the position is exact. Sort by both and compare both:
SELECT * FROM posts
WHERE (created_at, id) < ('2026-03-01 10:00', 8141)
ORDER BY created_at DESC, id DESC
LIMIT 10;Which one to use
| Situation | Choice |
|---|---|
| "Load more" buttons, infinite scroll, feeds, APIs | Keyset |
| Exports and background jobs that walk a whole table | Keyset |
| A small list where users need page numbers | OFFSET is fine |
| Page numbers on a large list | OFFSET, with a limit on how deep people can go |
Key takeaways
- OFFSET makes the database read and discard every skipped row, so deep pages get slow.
- Page 1 is fast either way, which hides the problem until real traffic arrives.
- Keyset pagination asks for rows after the last one seen and reads only the page.
- It also avoids duplicates and gaps when rows change between requests.
- The price is that you can only move to the next or previous page.