Databases
Offset vs keyset pagination
Short answer
Use keyset pagination for large or changing datasets and production feeds where stable latency matters. Use offset pagination for small administrative lists that need numbered pages and can tolerate skipped or repeated rows as data changes.
Written and reviewed by Sahil Srivastav
What each one actually is
Offset pagination asks for a page number or row offset, commonly with `LIMIT` and `OFFSET`. It is easy to expose and lets a UI jump to a page, but the database may scan and discard many rows.
Keyset pagination uses the last row’s ordered key as a cursor, such as `WHERE (created_at, id) < (...)`. It seeks to the next page and remains efficient deep into a result set.
Both require a deterministic order. Include a unique tie-breaker and define whether inserts or deletes during traversal should appear in the feed.
Side by side
| Offset pagination | Keyset pagination | |
|---|---|---|
| Deep-page cost | Often grows with offset | Stays close to page size with an index |
| Page jumping | Direct page numbers | Sequential cursors; arbitrary jumps are hard |
| Concurrent inserts | Can shift rows between pages | Cursor boundary avoids most shifts |
| Cursor shape | Integer or page parameter | Opaque encoded sort key |
| Index need | Order index still helps, but rows are skipped | Index must match predicate and order |
| Count total | Natural but `COUNT` can be expensive | Separate count or no total |
| API usability | Simple for basic clients | Requires cursor storage and handling |
| Best fit | Small, stable tables and admin UIs | Feeds, exports, and large mutable tables |
Choose Offset pagination when
- Users need numbered pages or arbitrary page jumps
- The result set is small enough that deep offsets are cheap
- Rows change slowly and minor page drift is acceptable
- Client simplicity matters more than maximum-scale latency
Choose Keyset pagination when
- The table is large or users traverse many pages
- New rows arrive while clients paginate
- Latency must remain predictable at depth
- The API can expose an opaque next cursor and a stable sort
The trade-off in detail
Offset pagination’s hidden cost is not only the scan. Inserts before the current offset shift the window, so clients can see duplicates or miss rows. A snapshot can fix that but increases transaction and resource lifetime.
Keyset pagination is only fast when the cursor columns and filter have a matching index. A cursor on `created_at` alone is ambiguous when timestamps tie; include a unique key and use the same direction in predicate and order.
Opaque cursors prevent clients from constructing invalid positions, but they do not make a cursor immortal. Include a version and expiry policy when schema or sort rules can change.
Things that are commonly said and are wrong
- “Cursor pagination always prevents duplicates.” It prevents offset shifts, but updates to sort keys and deletes can still change visibility.
- “OFFSET is slow everywhere.” Small offsets with a useful index are often fine; measure the actual query and data size.
- “A cursor must expose database IDs.” Encode and sign the sort tuple so clients cannot alter tenancy or filter boundaries.
FAQ
Should a public API use cursor pagination?
Usually for large mutable collections. Return an opaque cursor, a stable ordering, and a `hasMore` or next-link signal rather than requiring clients to understand database keys.
How do I implement keyset pagination?
Order by a stable tuple such as `(created_at DESC, id DESC)`, use the last tuple in the next predicate, and add an index with the same leading columns and filters.
Can I show total pages with keysets?
You can run a separate approximate or exact count, but keep it out of the page query’s critical path. Many feeds are better served by next-page availability than a costly total.