Keyset Pagination
What you'll learn: How to paginate efficiently through large datasets using indexed column values as stable, performant cursors.
The Core Idea
Keyset pagination (also called "seek method" pagination) uses values from indexed columns—like id, created_at, or composite keys—as markers to retrieve the next page. Instead of skipping rows with an offset, you query for records greater than (or less than) the last seen value.
Example query pattern:
SELECT * FROM posts
WHERE id > 12345
ORDER BY id
LIMIT 20;
Here, 12345 is the keyset—the id of the last item from the previous page. The database uses the index to jump directly to the next record, avoiding the full table scan or expensive offset calculations that slow down offset-based pagination at scale.
Why It Wins at Scale
- Consistent performance: Whether you're on page 1 or page 10,000, the database performs an index seek—always fast
- Stability: New inserts don't shift your pages; the keyset anchors your position in the dataset
- Index-friendly: Leverages existing indexes on the ordering column(s), making queries efficient even with millions of rows
Tradeoffs
- No random page access: You can't jump directly to page 47; you must traverse sequentially
- Requires stable, unique ordering: The keyset column must be indexed and provide a deterministic sort order
- More complex for multi-column sorts: Composite keysets (e.g.,
created_at + id) require careful handling of boundary conditions
Key Takeaway:
Keyset pagination uses indexed column values as cursors to deliver consistently fast, stable pagination across massive datasets—ideal when sequential traversal and performance matter more than random page access.