The Classic Approach Everyone Uses
When building pagination, most of us reach for the familiar combo:
SELECT * FROM logs LIMIT 100 OFFSET 1000000
Simple. Clean. Works perfectly in development.
Then production hits with millions of records, and suddenly your API takes 30 seconds to respond.
What's Actually Happening
Here's the uncomfortable truth: OFFSET doesn't magically jump to row 1,000,000.
It scans every single row from 1 to 1,000,000, then throws them away. Your database is doing a million units of work just to discard the results.
The deeper you paginate, the slower it gets. Page 1 is fast. Page 10,000 is a disaster.
The Fix: Cursor-Based Pagination
Instead of telling the database how many rows to skip, tell it where to start:
-- First page (no cost)
SELECT * FROM logs LIMIT 100
-- Next pages (efficient)
SELECT * FROM logs WHERE id > 100 LIMIT 100
With WHERE id > 100, the database uses the index to jump directly to row 101. No scanning. No wasted work.
This is exactly why DynamoDB requires a lastEvaluatedKey for pagination. It's not a limitation—it's a performance guarantee.
The Trade-Off
Cursor pagination isn't perfect:
- No "jump to page 50" feature
- Can't show total page count without extra queries
- UI typically shows only "Previous" and "Next" buttons
For large-scale systems, this trade-off is worth it. For smaller datasets, OFFSET remains convenient and acceptable.
When to Use What
Use OFFSET when:
- Dataset under 100K rows
- Users need random page access
- Performance isn't critical
Use cursor pagination when:
- Dataset exceeds 100K rows
- Building infinite scroll or "load more"
- API response time matters