Sorting, LIMIT/OFFSET, and Keyset Pagination
ORDER BY defines result ordering; without it, row order is not guaranteed. LIMIT caps the number of rows, and OFFSET skips rows. Offset pagination is easy to understand but can become slow for deep pages and may shift when concurrent inserts or deletes occur.
Keyset pagination uses a stable ordering key from the last item on the previous page. For example, when ordering by created_at DESC, id DESC, the next page can filter rows lexicographically before the last seen pair. A unique tie-breaker is important to avoid duplicates or omissions when sort values repeat.
Basic pagination
SELECT id, name, created_at
FROM articles
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 40;💡 This returns a page of 20 rows after skipping 40. For deep or frequently changing datasets, keyset pagination can be more efficient and stable.
Key Takeaways
- Always define ORDER BY for deterministic pagination.
- OFFSET can become costly for deep pages.
- Keyset pagination relies on a stable sort and cursor values.
Knowledge Check
What is a key requirement for reliable pagination when sort values can repeat?