API Pagination — Cursor-Based vs Offset-Based
Fast recall
Quick revision
Core answer
Whether you know cursor-based pagination solves real production problems
Not knowing that OFFSET 10000 scans 10000 rows and is slow
Mention that Stripe, GitHub, and Twitter all use cursor-based pagination for their high-volume APIs
Interview-ready guide
Interview answer
Pagination is a ubiquitous feature with a hidden performance trap. Offset-based pagination works fine on small datasets and breaks catastrophically on large ones.
Why offset breaks at scale
SELECT * FROM orders ORDER BY created_at OFFSET 50000 LIMIT 20— the database sorts and discards 50,000 rows to return 20- Performance degrades linearly with page depth; page 2,500 is 2,500× slower than page 1
- Facebook, Twitter, Instagram all migrated away from offset for their feeds
Cursor pagination
WHERE (created_at, id) < (:lastCreatedAt, :lastId) ORDER BY created_at DESC, id DESC LIMIT 20- One index seek; constant time regardless of page depth
- Trade-off: no random access — you cannot jump to page 47; must traverse sequentially
- Cursor must be stable and unique — use a composite
(created_at, id)cursor; timestamp alone breaks on ties
Spring Data choices
Page— runs two SQL queries: data query +COUNT(*); use when the UI shows total page countSlice— fetcheslimit + 1rows to detecthasNext; no COUNT query; use for 'load more' UIs
The decision rule: offset for admin UIs with small data and random page access; cursor for feeds, timelines, and any pagination over more than ~10,000 rows.
API Pagination — Cursor-Based vs Offset-Based
Direct Answer
offset-based (?page=2&size=20) is simple but breaks on concurrent inserts and is slow for large offsets. cursor-based pagination uses an opaque cursor (last seen ID/timestamp) that is stable and performant regardless of dataset size.
Common Mistakes & Pitfalls
Not knowing that OFFSET 10000 scans 10000 rows and is slow
Check your understanding
Answer these without looking back at the article.
- How do you encode a cursor to make it opaque to clients?
- How does Spring Data support cursor-based pagination with Slice?
- How do you handle total count with cursor-based pagination?
Related interview prep categories
1Want a guided curriculum order?
These modules are also sequenced in our flagship backend interview track — open it when you prefer an ordered path instead of browsing by topic.