OFFSET vs keyset pagination
OFFSET reads the rows it skips, so deep pages cost more. Keyset pagination starts after the last key it saw and reads only the next page. For page 5,000 at 500 requests/s, OFFSET reads 249,950 rows first and p99 passes 10 s. Keyset gives p99 24.5 ms.
Index · 4 pages read per query · disk writes per insert: 2
Case 1
Page 5,000 has 50 rows after 249,950 earlier rows. 500 requests/s ask for deep pages. p99 must stay below 100 ms.
| Measure | Goal | OFFSET pages | Keyset pages |
|---|---|---|---|
| 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% |