Decision guide
Offset vs Cursor vs Keyset Pagination
How should a collection endpoint paginate?
Side by side
| Criterion | Offset / limit | Opaque cursor | Explicit keyset |
|---|---|---|---|
| Deep-page performance | Degrades with offset | Constant | Constant |
| Stable under inserts/deletes | No (skips/duplicates) | Yes | Yes |
| Jump to arbitrary page | Yes | No | No |
| Total count | Natural (but costly) | Optional | Optional |
| Client complexity | Lowest | Low | Medium |
| Implementation flexibility | Low | High (opaque) | Low (exposed keys) |
| Index requirement | Sort column | Composite sort + id | Composite sort + id |
When to choose each
Offset / limit
?page=3&per_page=50 — skip N rows, take M.
Choose when
- Small or admin datasets
- Users must jump to page N
- Data rarely changes while browsing
Avoid when
- Large tables (deep pages are slow)
- Feeds that change constantly
Opaque cursor
Server returns next_cursor encoding the position; clients pass it back.
Choose when
- Public APIs and feeds
- Infinite scroll
- You want freedom to change the implementation
Avoid when
- Users need random access to page numbers
Explicit keyset
Client passes the last seen sort values (?after_created=…&after_id=…).
Choose when
- Internal exports and sync jobs
- Clients that already track sort keys
Avoid when
- Public APIs (leaks implementation, harder to evolve)
Verdict
Default to opaque cursors for anything user-facing or large; keep offset for small admin tables where page numbers matter. Always cap page size and include a unique tiebreaker in the sort.
Learn more
- REST APIs in Depth
- API Design
- Databases & Transactions
- Paginate with cursors in FastAPI (FastAPI)
- Implement cursor pagination with PostgreSQL in Node.js (Node.js)
- Paginate large tables with cursors in PHP (PHP)