Why Pagination Is Harder Than It Looks
Pagination is the feature everyone assumes is trivial. You add LIMIT and OFFSET, wire up next and previous buttons, and move on. Then the dataset grows, users report that page 900 takes seconds to load, and someone notices records occasionally appear twice or get skipped. Pagination turns out to be a genuinely hard problem the moment data is large or changing, and the naive approach quietly fails on both counts.
How offset pagination works - and why it slows down
Offset pagination asks the database to skip a number of rows and return the next batch. The catch is that the database usually has to scan and discard every skipped row to find where the page starts. Page 1 skips nothing; page 1000 with a page size of 20 forces the database to walk past 20,000 rows before returning a single one. The cost grows linearly with the page number.
-- Page 1: fast, skips nothing.
SELECT * FROM orders ORDER BY created_at LIMIT 20 OFFSET 0;
-- Page 1000: the database must scan past 20,000 rows
-- just to discard them before returning 20.
SELECT * FROM orders ORDER BY created_at LIMIT 20 OFFSET 20000;The consistency problem
Performance is only half the trouble. Offset pagination assumes the underlying data does not change between page loads. If a new row is inserted while a user browses, every subsequent row shifts by one. A record that was on the boundary can appear on two consecutive pages, and another can be skipped entirely. On a busy, frequently updated table this is not a rare edge case - it is normal behavior.
Keyset pagination: remember where you were
Keyset pagination - also called cursor or seek pagination - fixes both problems by not counting rows at all. Instead of asking for 'the next 20 after skipping N', it asks for 'the next 20 after this specific value'. The client remembers the last row it saw, and the next query continues from there using an indexed comparison.
-- First page.
SELECT * FROM orders
ORDER BY created_at, id
LIMIT 20;
-- Next page: continue after the last row seen.
SELECT * FROM orders
WHERE (created_at, id) > (:last_created_at, :last_id)
ORDER BY created_at, id
LIMIT 20;Why keyset stays fast
Because the WHERE clause compares against indexed columns, the database can jump straight to the starting point instead of scanning discarded rows. Every page costs roughly the same, regardless of how deep you are. This depends entirely on having the right index on the ordering columns - without it, keyset pagination is no better than offset.
The trade-offs
| Aspect | Offset | Keyset |
|---|---|---|
| Deep page performance | Degrades with page number | Constant |
| Jump to arbitrary page | Trivial (page N) | Not directly supported |
| Stable under inserts | No - rows shift | Yes - anchored to a value |
| Total page count | Easy to compute | Awkward or avoided |
Offset pagination is not always wrong. For small datasets, or admin screens where users genuinely need to jump to page 50, its simplicity and random access are worth the cost. Keyset shines for large, append-heavy datasets and for infinite-scroll interfaces that only ever move forward.
Practical guidance
- Use offset pagination for small or bounded result sets where users may jump to arbitrary pages.
- Switch to keyset pagination for large datasets, deep pages, or feeds that scroll forward.
- Always order by a set of columns that includes a unique tiebreaker, so the cursor is unambiguous.
- Ensure the ordering columns are indexed, or neither approach will perform well at scale.
Conclusion
Pagination is hard because the obvious solution optimizes for the easy case and quietly breaks at scale. Offset pagination is simple and supports jumping to any page, but it slows down linearly and produces duplicates and gaps on changing data. Keyset pagination trades random access for constant performance and consistency. Knowing which trade-off your use case needs - and indexing accordingly - is what separates a list that works in a demo from one that works in production.