keysetpagination

Keyset pagination, also known as cursor-based or seek pagination, is a method of paginating query results by using a reference key from the last item on the current page to retrieve the next set of items. Instead of counting rows from the beginning, subsequent pages are fetched by querying for records with keys greater than (or less than) the last seen key, combined with a stable sort order and a limit on the number of results.

The technique relies on a deterministic sort order, typically defined by a unique key such as an id, or by a composite key (for example, created_at and id). The pagination query uses a comparison on the sort key and a limit to fetch the next page. Common patterns include selecting where id > last_id or where (created_at, id) > (last_created, last_id) with an appropriate ORDER BY clause.

Keyset pagination offers performance advantages over offset-based pagination. Since it does not skip over rows by counting, the database can jump directly to the position of the last seen key, reducing work on large datasets. It also tends to be more stable under concurrent inserts, as new rows added after the last retrieved key are not re-scanned.

Limitations include the inability to jump directly to arbitrary pages without first traversing pages, and the need for a stable, ideally unique sort key. If keys are not unique, additional tie-breakers are required to ensure deterministic results. Deletions have predictable effects, since previously retrieved pages are not affected, but new pages depend on the last key from the previous page.

Common use cases include large lists in web interfaces, such as logs, feeds, or records with a natural numeric or timestamp key.