Why is OFFSET pagination slow at scale, and how does keyset (cursor) pagination fix it?
Reviewed by Gurusankar M.
⚡ Short Answer
OFFSET N must scan and discard N rows before returning the page, so deep pages get linearly slower (page 10,000 scans 200k rows). Keyset pagination uses WHERE (sort_key) > last_seen + LIMIT, seeking directly via an index — constant time regardless of depth.
☕Coffee Chat Question
Concept Made Simple
“Why is OFFSET pagination slow at scale, and how does keyset (cursor) pagination fix it?”
🧠Mind Map Answer
Remember It Faster
⌨️Hands-on Keyboard
Learn by Doing
-- slow deep page
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 100000;
-- keyset: pass the last id from the previous page
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;🔥What If?
Think Beyond the Expected
What's the trade-off of keyset pagination vs OFFSET?
Keyset can't jump to an arbitrary page number and needs a stable, indexed sort key (often a tiebreaker like id). But it's vastly faster for infinite scroll / 'next page' and avoids missing/duplicate rows when data shifts between page loads.
😂Real World
Infinite-scroll feeds and large admin tables use keyset/cursor pagination (it's what most APIs' 'next cursor' is) precisely because OFFSET collapses on deep pages.
🎯Interviewer's Expectation
Keywords they're listening for:
⚠️Common Mistakes
- ✗OFFSET for deep pagination on big tables
- ✗Keyset without a unique tiebreaker
- ✗Not indexing the sort key
✅Best Practices
- ✓Use keyset/cursor pagination for large datasets
- ✓Sort by an indexed, unique (or tie-broken) key
- ✓Expose an opaque cursor in APIs
🔁Follow-up Questions
- 1How do you make keyset stable with non-unique sort columns?
- 2Why can OFFSET pagination skip/duplicate rows on live data?
- 3How do API cursors encode the keyset?
🧩Related Technologies
Continue Learning with AI
Take this question deeper with your favourite AI assistant. Pick a depth, copy the prompt, or open it directly — AI is your learning companion, not a shortcut.
Plain-language foundations
I'm preparing for a software engineering interview and want to understand this from scratch, as a beginner. Topic: Optimization (SQL) Interview question: "Why is OFFSET pagination slow at scale, and how does keyset (cursor) pagination fix it?" Please: 1. Explain the core idea in simple, plain language, using an everyday analogy. 2. Define any technical terms you use. 3. Walk through one small, concrete example. 4. Finish with a single sentence I can easily remember. Keep the tone friendly and assume I'm new to this topic.
Was this answer helpful?
⭐ Featured Products
Support our platform by exploring our recommended products.
As an Amazon affiliate, purchases through these links may earn us a small commission — at no extra cost to you. It helps keep Full Stack Interview Guru free.