MediumExperience level: 3-5 years · 8-15 years1 min read

Why is OFFSET pagination slow at scale, and how does keyset (cursor) pagination fix it?

Reviewed by Gurusankar M.

Asked inAmazonGoogleMicrosoft
#pagination#offset#keyset#cursor#performance#scalability
Report issue

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.

On this page

Concept Made Simple

Coffee Chat Question

“Why is OFFSET pagination slow at scale, and how does keyset (cursor) pagination fix it?”

Remember It Faster

Mind Map Answer

OFFSET 100000 LIMIT 20
scans 100020 rows, drops 100000
Keyset
WHERE id > :last LIMIT 20 — index seek
Result
O(page) vs O(offset+page)

Learn by Doing

Hands-on Keyboard

sql
-- 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;

Think Beyond the Expected

What If?

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:

  • OFFSET scans+discards
  • deep pages O(offset)
  • keyset uses index seek
  • needs stable sort key
  • no random page jump

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

  1. How do you make keyset stable with non-unique sort columns?
  2. Why can OFFSET pagination skip/duplicate rows on live data?
  3. How do API cursors encode the keyset?

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.
Open inChatGPTGeminiClaude

Was this answer helpful?

Affiliate links

Tools for Your Developer Workspace

Equipment for a comfortable, productive setup.

As an Amazon Associate I earn from qualifying purchases.