Pagination and Large Result Sets
You'll learn efficient techniques for paginating large datasets without killing database performance.
The Problem with Simple Pagination
When displaying large result sets (think thousands or millions of rows), you can't return everything at once. The naive approach uses LIMIT and OFFSET:
SELECT * FROM products
ORDER BY created_at
LIMIT 20 OFFSET 200;
This works for small offsets, but becomes disastrously slow as OFFSET grows. Why? The database must scan and skip all those offset rows on every request. At OFFSET 1000000, it reads a million rows just to throw them away.
Keyset Pagination (Cursor-Based)
Keyset pagination uses the last seen value as a filter instead of counting rows:
-- First page
SELECT * FROM products
ORDER BY created_at, id
LIMIT 20;
-- Next page (using last row's values: created_at='2024-01-15', id=450)
SELECT * FROM products
WHERE (created_at, id) > ('2024-01-15', 450)
ORDER BY created_at, id
LIMIT 20;
This approach leverages indexes to seek directly to the starting point—no skipping required. Performance stays constant whether you're on page 2 or page 20,000.
Why It Works
Think of OFFSET like counting pages in a phone book by flipping through them one-by-one. Keyset pagination is like opening directly to "Smith" because the book is alphabetically indexed—you jump straight there.
Key requirements:
- Include a unique column (like
id) in the sort for tie-breaking - Index must cover your ordering columns
- Trade-off: can't jump to arbitrary page numbers (only next/previous)
Key Takeaway: For large datasets, avoid OFFSET entirely and use keyset pagination with indexed columns to maintain consistent, fast performance regardless of page depth.