Part 26: Offset-Based Pagination vs Keyset / Cursor Pagination

Pagination is essential in any application. Let's analyze why traditional offset pagination breaks at scale and how keyset pagination fixes it.

1. The Offset Problem

When you write OFFSET 1000000 LIMIT 10: The database must scan and discard 1,000,000 rows before returning the 10 rows you asked for! As pages increase, query latency degrades exponentially.

2. The Solution: Keyset (Cursor-Based) Pagination

Instead of counting offsets, filter by the ID of the last seen item:

-- Page 1:
SELECT id, title, created_at 
FROM articles 
ORDER BY id DESC 
LIMIT 10;

-- Page 2 (Pass last seen ID from Page 1, e.g. 9840):
SELECT id, title, created_at 
FROM articles 
WHERE id < 9840 
ORDER BY id DESC 
LIMIT 10;

Complexity drops from O(N) to O(1) indexed B-Tree seek time!

Key Takeaways

• Avoid high OFFSET values on massive tables.
• Use Keyset / Cursor pagination (WHERE id < :last_seen_id) for infinite scrolling feeds and large datasets.

In Part 27, we analyze Performance Pitfalls of High-Offset Queries!