Pagination at Scale
You'll learn why the common OFFSET-based pagination strategy fails at large scale and how cursor-based pagination provides a faster, more efficient alternative.
The OFFSET Problem
When paginating results (showing 10, 20, or 100 items per page), many developers reach for OFFSET:
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 400;
This fetches page 21 (skipping the first 400 rows). The hidden cost: the database must scan through all 400 rows, discarding them, just to reach your page. On page 1,000 of a million-row table, you're forcing the database to process and discard 999,000 rows every single time.
Think of it like reading a physical book: OFFSET makes you flip through every previous page just to find page 500, even though you've already read them.
Cursor-Based Pagination to the Rescue
Instead of counting rows, cursor pagination uses an indexed column value as a bookmark:
-- First page
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20;
-- Next page uses the last created_at value as a cursor
SELECT * FROM posts
WHERE created_at < '2024-01-15 10:30:00'
ORDER BY created_at DESC
LIMIT 20;
With a proper index on created_at, the database jumps directly to the bookmark position—no scanning thousands of rows. It's like having page markers in your book: you open directly to the saved spot.
The tradeoff: you can't jump to arbitrary page numbers (no "page 47" link), but for infinite scroll or "next/previous" navigation, it's dramatically faster.
Key Takeaway: OFFSET pagination becomes exponentially slower on large datasets because it scans and discards rows; cursor-based pagination uses indexed column values to jump directly to the next batch, maintaining constant-time performance regardless of depth.