Offset vs. Cursor Pagination – Definition and Differences
Compare counting and marker-based pagination on stability, depth performance, random access, and which to choose for a given consumer.
Offset pagination counts rows from the beginning; cursor pagination resumes from a marker. That difference determines whether a traversal is correct when the underlying data changes.
1. Key Differences
| Dimension | Offset | Cursor |
|---|---|---|
| Position expressed as | A count to skip | An opaque marker |
| Stable under insertion or deletion | No | Yes |
| Deep-page performance | Degrades | Constant |
| Random access | Yes | No |
| Total page count | Available | Usually not |
| Implementation | Trivial | Requires a stable sort with a tiebreaker |
2. The Drift Problem
Offset paging assumes the set is frozen. It is not: with a record inserted before your position, every later offset shifts by one, and the client silently skips a row. A deletion causes a repeat.
Neither produces an error. The traversal completes and the result is quietly wrong, which is why the problem persists so long in production systems.
3. Why Deep Offsets Are Slow
A database asked to skip 100,000 rows must traverse them before returning anything. Cost grows with depth, so the last pages of a large set are far slower than the first.
A cursor is an indexed seek to a position, so page one and page one thousand cost the same.
4. What Cursors Give Up
- Jumping to an arbitrary page — you must walk there.
- Total page counts, since positions are not indices.
- Bookmarkable page URLs, because cursors can expire.
- Backwards navigation, unless explicitly supported.
Those matter for interfaces with numbered page controls, which is exactly where offsets persist.
5. Choosing
| Consumer | Choose |
|---|---|
| A user interface with page numbers | Offset, accepting drift |
| An API traversed programmatically | Cursor |
| A complete export of a large set | Cursor |
| A small set that fits in a few pages | Either |
| Data changing frequently during traversal | Cursor, without question |
6. Implementing Cursors Correctly
The sort key must be unique or paired with a tiebreaker. Sorting by a timestamp alone breaks at page boundaries whenever two records share a timestamp — one is skipped or repeated.
Including the primary key as a secondary sort makes the position unambiguous, which is the difference between a correct cursor implementation and a subtly broken one.
7. Consuming Either Safely
Regardless of scheme: follow the server's own next indicator, stop on an explicit end signal rather than a short page, deduplicate on write, cap the loop, and compare the final count against expectations.
Those five habits produce correct traversals under both schemes and detect the failures of each.
8. Summary
Offsets are simple and permit random access, at the cost of drift under concurrent change and poor deep-page performance. Cursors are stable and efficient at any depth, and forfeit random access and page counts.
Use offsets for paged interfaces and cursors for programmatic traversal — and remember that a correct cursor needs a unique sort key or a tiebreaker.