keyset pagination
Paging by remembering the last key the reader saw and asking for rows after it, which does not drift.
In more detail
The next page is WHERE id > last_seen_id ORDER BY id LIMIT n. New rows cannot shift it, and an index on the key lets the database jump straight to the right place however deep the page.
Example
SELECT id, name FROM products WHERE id > 3 ORDER BY id LIMIT 3;Related terms
- offset pagination — Paging by saying how many items to skip, such as ?limit=2&offset=4.
- ORDER BY — The clause that sorts a query's answer by one or more columns, each ascending or descending.
- index — A separate, sorted structure a database keeps alongside a table so it can find matching rows without reading them all.
- cursor pagination — Paging by remembering the position of the last item seen and asking for items after it, such as ?after=2&limit=2.
Taught in
- Ordering And Limiting Results — PostgreSQL, Indexing & Relational Data Modeling