Keyset pagination
OFFSET reads and discards every earlier row. Page 5,000 visits 249,950 rows to return 50. Keyset pagination starts after the last key, so it reads only the 50 rows that it returns.
See it on the board
The same traffic runs on two boards. The simulator measured both when this page was built.
| Measure | Goal | OFFSET pagination | Keyset pagination |
|---|---|---|---|
| p99 latency | under 100 ms | 10.1 s Fail | 25 ms Pass |
| Monthly cost | 8 USD/mo | 8 USD/mo | |
| Error rate | 99.162% | 0% |
Key numbers
| Number | Value |
|---|---|
| Default page size | 50 rows |
| Reads per second, one database vCPU | 15,000 reads/s |
| Disk operations per second | 50,000 IOPS |
Index · 4 pages read per query · disk writes per insert: 2
How it works
Pages of results look easy: skip the rows of the earlier pages and take the next 50. The SQL form is OFFSET and LIMIT. The database does not jump to the row. It must produce each skipped row and then discard it. The cost grows with the page number.
In the mini board page 5,000 has 50 rows after 249,950 earlier rows. Each request visits about 250,000 rows to return 50, and p99 passes the 100 ms limit.
Keyset pagination uses the last row of the previous page. The next request asks for rows whose key is greater than that key, in order, and takes 50. With an index on the key, the database jumps to the right place and reads only 50 rows. The cost is the same for page 5 and for page 5,000.
Keyset has limits. The sort key must be unique, or the pages can skip or repeat rows, so add a tie-breaker such as the row ID. It gives next and previous, not jump to page 400. And it needs an index that matches the sort order.
For feeds, logs and APIs that clients walk page by page, keyset is the better choice.
In the levels you switch the pagination style and check the time for a deep page.