It's a familiar scenario: your "Recent Activity" feed works perfectly with 100 items. But when you have 10 million items, clicking "Page 5000" causes the database to time out.
Why? Because of how standard pagination (OFFSET) works.
In this guide, we'll explore why deep pagination kills performance and how to fix it using the Seek Method (also known as Keyset Pagination).
The Problem with OFFSET
Most developers learn pagination like this:
-- Get Page 2 (Items 11-20)
SELECT * FROM orders
ORDER BY date DESC
LIMIT 10 OFFSET 10;
This seems fine. But what happens when you need Page 100,000?
-- Get Page 100,000
SELECT * FROM orders
ORDER BY date DESC
LIMIT 10 OFFSET 1000000;
The Database's Hidden Work:
To fulfill OFFSET 1,000,000, the database must:
- Fetch 1,000,000 rows.
- Sort them.
- Throw them away.
- Return the next 10.
It gets slower and slower the deeper you go. This is O(N) complexity for pagination.
The Solution: Seek Method (Keyset Pagination)
The Seek Method doesn't count rows. It simply asks: "Give me the 10 rows after the last one I saw."
If the last order on Page 1 had id=500, Page 2 asks:
SELECT * FROM orders
WHERE id < 500 -- Assuming we sort DESC
ORDER BY id DESC
LIMIT 10;
Why it's fast:
The database jumps directly to id=500 using the index. It doesn't read or sort the previous 499 rows. This is O(1) complexity—constant time, regardless of how deep you paginate!
Interactive Comparison
Let's verify this conceptual difference. While our dataset is small, the logic holds for millions of rows.
1. OFFSET Method (The Slow Way)
2. Seek Method (The Fast Way)
Faster, but requires knowing the last ID from the previous page.
Trade-offs
| Feature | OFFSET | Seek Method |
|---|---|---|
| Performance | Slows down as pages increase | Constant speed |
| Jumping | Can jump to "Page 500" easily | Cannot jump (must scroll linearly) |
| Consistency | Rows can shift if new data is added | More stable results (anchored to ID) |
| Implementation | Trivial | Complex (need to track last ID) |
Related Interview Question
This concept is so important it's a dedicated interview question!
- Slow Query Detection (Databricks) - While this question focuses on finding slow queries, understanding why queries are slow (like inefficient scans) is key to solving performance problems.
Conclusion
If you're building an "Infinite Scroll" feed (like Instagram or Twitter), always use the Seek Method. It scales infinitely.
If you are building a small admin dashboard with < 1,000 items, OFFSET is perfectly fine and easier to code.
Rule of thumb: Optimization is about picking the right tool for the scale.