Introduction
Recently, I made a pull request on Github to add cursor pagination to Laravel. This Laravel feature will be released today! This post describes both offset and cursor pagination strategies, and describes which one to use depending on your use case.
Offset Pagination
Offset pagination is one of the most widely used pagination techniques in the world today. Laravel’s paginate and simplePaginate methods on the Eloquent and database query builder classes use offset pagination under the hood.
Consider the following Laravel code to paginate the users table:
use App\Models\User;
$users = User::orderBy('id')->simplePaginate(10);Under the hood, offset pagination fires a query that uses the offset clause. For example, here’s the query fired if you were to visit the 2nd page:
select * from users order by `id` asc limit 10 offset 10;Cursor Pagination
Cursor pagination is a high performant pagination technique often used for large data-sets, infinite scrolling and APIs (more on that later). Today’s Laravel release adds the cursorPaginate method to the Eloquent and database query builder classes, which uses cursor pagination under the hood.
Here’s how we can cursor paginate the users table in Laravel:
use App\Models\User;
$users = User::orderBy('id')->cursorPaginate(10);The code is exactly the same way as simplePaginate, except that it fires a different query. Here’s the query fired by cursor pagination if you were to visit the 2nd page:
select * from users where `id` > 10 order by `id` asc limit 10;The main difference between offset and cursor pagination is that offset pagination uses an offset clause while cursor pagination uses a where clause with a comparison operator.
Performance Comparison
Cursor pagination outperforms offset pagination by upto 400X (or even higher)! Shopify’s engineering team performed a benchmark study comparing the performance of both techniques. It’s important to note that to achieve a performance boost using cursor pagination, you should have a database index on the order by column(s).
The reason why cursor pagination outperforms offset pagination is that offset pagination scans through all the previous data. This means that an offset of 100,000 records would have the database still scan through those 100,000 records. On the other hand, cursor pagination can skip to the right record immediately if a database index is setup on the order by column(s).
Duplicated Or Missing Content
A common issue with offset pagination is duplicated or missing content, especially for data-sets with high write frequencies. This issue occurs when one or more records on a previous page are either inserted or deleted.
Since the offset SQL construct basically skips a number of records, as soon as there are inserts or deletes on a previous page, offset pagination starts show duplicate records or misses records.
Consider the following example. Let’s say we visit the first page containing 10 items. After that, there’s a new row inserted on a previous page. Now, when we visit the next page, we will see a duplicated record as shown in the diagram below. Similarly, if there’s a delete on a previous page, offset pagination will skip a record.
So, offset pagination isn’t suitable for rapidly changing data-sets. Cursor pagination circumvents this issue because the query fired uses a where clause such as where id > 10. So, even if a new row is inserted or deleted, it will not show any duplicate records or miss any records.
The duplicate or missing content issue is even further exacerbated with infinite scrolling. Unlike page numbered content, infinite scrolling shows records from the “previous” page alongside records from the next page. This means that duplicate content would show back-to-back in infinite scrolling, providing a bad user experience.
Worst case scenario: imagine there are 10 inserts per second, and you can scroll one page every second with 10 items per page. What do you think will happen?
You will keep seeing the same duplicated content over and over again!
Limitations of Cursor Pagination
Like most of programming, cursor pagination is not a silver bullet to all pagination issues! It has limitations.
Page Numbers
The first and most obvious one, is that it does not support page numbers. It can, however, support next and previous links similar to simplePaginate. So, if you need page numbers, you’d need to go with offset pagination.
Database Index
Secondly, to actually improve performance, offset pagination needs a database index on the order by column. That means if you have multiple sort options for your users to choose, you’d probably need indexes for each one. Remember that the more the number of indexes, the slower your write performance, even though they may actually improve read performance depending on the query. It’s a trade-off that you need to decide based on your use case.
Uniqueness
Thirdly, cursor pagination requires that the order by condition is based on a unique column or a combination of columns that is unique. So, if your order by condition is say based on the name column of the users table, cursor pagination won’t work because name is not unique. This is because of the way cursor pagination constructs queries. Imagine you stopped at Bob on the current page. The query for the next page would be:
select * from users where `name` > 'Bob' order by `name` asc limit 10;So, if there are 5 users named Bob, this query would skip the other four. One thing you can do to “fix” this is to add the id column as a secondary order by column. Because the combination of the name and id is unique, this would work for cursor pagination. Here’s how the query would look like this time:
select * from users where (`name`, `id`) > ('Bob', 20) order by `name` asc, `id` asc limit 10;To get the most performance from this query, you need a composite index comprising name and id for cursor pagination. However, even if you have a single column index on the name column, most SQL database engines should be able to use the index for the query above.
Order Directions
Finally, with Laravel’s implementation of cursor pagination, you need the directions of all order by clauses to be the same. So you cannot have one asc and one desc order by clause. This is because Laravel’s implementation relies on tuple comparison (or row values) as shown in the query above. Tuple comparison only accepts one comparison operator. While it is possible to change the implementation with multiple where clauses, I did not attempt this in my pull request to keep the code less complex.
Conclusion
There is no “one size fits all”. Offset pagination works best when you’re working with a small data-set or you need page numbers in your UI.
However, if you’re working with large data-sets, frequent writes or infinite scrolling, cursor pagination would be the go-to choice. Be careful of the indexing and order direction limitations explained above as they may also influence your choice of pagination.
Hope this post was helpful in understanding the pros and cons of cursor pagination over offset pagination. Give the new cursor pagination a shot, if it makes sense for your project, and let me know how it goes in the comments below. Chao!