Keyset or Cursor-Based Pagination
Keyset or Cursor-Based Pagination#
When you want to show only part of a large result set, you use pages of results. This is called pagination.
There are two primary kinds of pagination:
- Limit/offset pagination
- Cursor-based or keyset pagination
Deterministic Ordering#
There should be a single, stable order. If some fields have the same value, the database may return rows in a different order each time. Users may see the same result on different pages if the order is not deterministic.
A primary key guarantees determinism.
Limit/Offset Drawbacks#
The problems with using limit/offset are:
- The higher the offset, the more records the RDBMS has to move through and discard, which slows things down.
- The data can shift underneath you if it changes while you are paginating.
However, limit/offset is easy to implement and stateless, and you can go directly to any page.
Cursor/Keyset-Based Pagination#
This also uses LIMIT, but tracks the last record seen.
The cursor can be called anything: token, key, next, or previous.
Original query:
SELECT * FROM people
ORDER BY id
LIMIT 10
For example, your cursor is id=10. Often, this is base64 encoded and sent to the client as part of the response.
base64.b64encode('(id=10)'.encode('utf-8'))
b'KGlkPTEwKQ=='
# sent to client as part of response
{"next_page": "KGlkPTEwKQ=="}
The client must then send the next-page key to the application, which decodes it and uses it in the next query:
SELECT * FROM people
WHERE id > 10
ORDER BY id
LIMIT 10
Note: The cursor/key must contain every field the dataset is being sorted by.
For example:
SELECT * FROM people
ORDER BY first_name, id
LIMIT 10
Would create a key:
(first_name=Aaron & id=25995)
base64.b64encode('(first_name=Aaron & id=25995)'.encode('utf-8'))
b'KGZpcnN0X25hbWU9QWFyb24gJiBpZD0yNTk5NSk='
The next query would be:
SELECT * FROM people
WHERE
(first_name = 'Aaron' AND id > 25995)
OR (first_name > 'Aaron')
ORDER BY first_name, id
LIMIT 10
Drawbacks of Cursor-Based Pagination#
- The more fields you sort by, the more complex the query becomes.
- You cannot directly address a page or skip to a specific page. You can only go forwards and backwards.
However, cursor-based pagination skips and discards a lot less data, so it is more performant. It is also more resilient to shifting records. It is a good fit for infinite scrolling.