Building APIs: A Comparison Between Cursor and Offset Pagination?

I have been mostly relying on offsets for implementing paging. It is more straightforward in implementation but I find cursor based pagination more intuitive in usage; plus it has better support for real-time data but on the negative side you are forced to use infinite scroll which is not always the best solution.

I was curious if anybody has any experience with these two approaches in their projects? And what are some other advantages and disadvantages on this? [1]

[1] I wrote this piece a while back but was thinking of updating it further: https://betterprogramming.pub/building-apis-a-comparison-between-cursor-and-offset-pagination-88261e3885f8

I've used cursor pagination on daemons doing large batch processing. It worked fine.

If you're using a cursor, you should be iterating the entire (or at least most of) the result set over the course of the connection. I wouldn't think they're appropriate to API's, because maintaining the cursor state between requests sounds painful.

Offset is the only way to paginate arbitrarily sorted data sets, unless you plan on going hard on indexing. The performance falls off as the offset grows. You'll also have issues with apparent 'duplicates' between pages.

Keyset pagination is preferred if the data is usually sorted in one way. Most infinite scrolling falls into this category. Each row has a unique 'counter', which is (usually) monotonic. To skip pages, filter rows whose counter is later than the last row in the current page. This is fast and has no duplicates. However, your sorting options are constrained, as you must have an index for each sort option.

Keyset and offset can be combined to eliminate the duplication defect of offset alone. The performance issue will still be there. This becomes an engineering decision - in most use cases, the large offset perf hits don't really happen (aside from abuse, which almost always happens).

Important to call out here, but thanks to GraphQL’s unfortunate habit of using words which already have established meanings, when a lot of people talk about “cursor-based pagination”, they actually mean keyset pagination and not something that requires a server to maintain a stateful cursor.

I’m guessing this is the meaning the OP was referring to.

We can technically offer the users Nth page with cursors+offset hybrid, if we're fine with the computational demands (smaller than pure offset, but bigger than just prev|next|last).

Let's say we want to offer the user 5 pages, 100 records per page:

prev (7) | next (9) | 10 | 11 | 12 | 13 | 14 | ... | last

If you click "13", you'd need to query with cursor for page 9 (i.e. id > last rec of page 8) , and offset 400 limit 100.

This way offset never get gigantic, because if you show up to 5 pages to the user, you need to offset up to 5 * 100 records at a given time (combined with the cursor).

Offset pagination is hard on the backends. You can distribute some of the sorting load (see: https://engineering.medallia.com/blog/posts/sorting-and-pagi... ), but it's tricky, and less efficient in any case.

Cursor based pagination is way better, since the backend aggregation bucket size is capped by the page size times the number of nodes used for a distributed sort (essentially you aggregate on page sized priority queues, one per node, and then aggregate them).