Pagination: Offset vs Cursor-Based
Pagination strategy significantly impacts performance, consistency, and user experience. Offset-based is simple but degrades with scale and mutable data. Cursor-based (keyset pagination) provides stability for live feeds and infinite scroll but makes arbitrary page jumps harder.
Offset pagination is like using page numbers — easy to jump around but frustrating if someone inserts or removes pages while you’re reading. Cursor pagination is like using a bookmark — you always continue exactly where you left off, even if the book changes.
1Offset-Based Pagination
Uses page number + limit. Simple to implement and great for admin panels or static lists where users expect 'Go to page 5'.
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 400;
2Cursor-Based Pagination
Uses a token (usually encoded last item's sort key) for stable traversal. Ideal for timelines, feeds, and infinite scroll where data mutates frequently.
SELECT * FROM posts WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 20;
3Key Trade-offs & When to Choose
Offset is simpler but can show duplicates/skips in live data and becomes slow with large offsets. Cursor is more stable and performant at scale but harder to implement 'jump to page N'.
| Property | Offset Pagination | Cursor Pagination |
|---|---|---|
| Ux | Good for numbered pages | Best for infinite scroll |
| Stability | Poor with inserts/deletes | Excellent |
| Complexity | Low | Medium |
| Performance | Degrades on deep pages | Consistent at scale |
Offset Pagination
Ux
Good for numbered pages
Stability
Poor with inserts/deletes
Complexity
Low
Performance
Degrades on deep pages
Cursor Pagination
Ux
Best for infinite scroll
Stability
Excellent
Complexity
Medium
Performance
Consistent at scale
Common questions
- ›“When would you choose cursor-based over offset pagination?”
- ›“What problems can occur with offset pagination on large datasets?”
- ›“How do you implement stable pagination for a live feed?”
- ›“How do you support both infinite scroll and page navigation?”
What interviewers look for
- Deep understanding of stability vs simplicity trade-offs
- Knowledge of keyset pagination and sort key selection
- Real-world considerations (mutable data, performance at scale)
- Balanced recommendation based on use case
Short answer (60 sec)
Use offset pagination for admin tables and numbered pages. Use cursor-based for infinite scroll and live feeds where data changes frequently. Cursor provides better consistency and performance at scale.
Detailed answer (senior level)
Offset pagination is simple but suffers from duplicates/skips in mutable datasets and slow performance on deep offsets. Cursor (keyset) pagination uses a stable pointer (e.g., (created_at, id)) and is ideal for timelines and infinite scroll. Combine both when needed: cursor for main feed, offset for admin views.
- Using offset for infinite scroll on mutable feeds
- Not using a composite sort key in cursor pagination
- Ignoring performance impact of large OFFSET values
- Returning unstable ordering without explicit sort
- Making cursor tokens too complex or non-opaque
- ✓Offset = simple but fragile with scale and mutations
- ✓Cursor = stable and performant for live data and infinite scroll
- ✓Choose based on UX: numbered pages vs continuous feed
- ✓Use composite sort keys (time + id) for cursor stability
- ✓Consider hybrid approaches when both patterns are needed
- ✓Always measure real-world performance and consistency
- ✓Pagination strategy is a core architecture decision