Laravel cursor pagination

Photo by Susan Q Yin on Unsplash

Pagination is needed when you have a large dataset to be sent to the user and it’s too big to send all at once. We need to break the data down into chunks, rather than a single response. Let’s look first at what currently exists.

In Laravel the paginate and simplePaginate eloquent methods are built into the framework for pagination. These methods are offset based pagination, where we send page and perPage options which break the data down into ‘pages’ of size ‘perPage’.

The downsides of offset pagination are that you can get duplicate or skipped data.

Photo by Stefan Cosma on Unsplash

Imagine you are on have paged through 50 results and go to the next page but someone deletes a record you have already seen. Now the record that would have been at position 51 goes to 50, and you never see it! That record was ‘skipped’.

Similarly, if someone adds a record which would have appeared with the top 50 of your result set, then the record from 50 bumps to 51! This record would have been the last record on one page, and the first record on the next page!

The response time gets slower the higher the offset, because the database still has to read up to the offset rows to know where to start selecting the data from. If deep pagination is a use case, you might not want to use cursor pagination. That’s where cursor pagination could help — as of Laravel 8.4.1 we now have cursor pagination.

What is cursor pagination?

When cursor pagination is used the client receives a ‘key’ called a cursor in the response. The cursor is a pointer to a specific item — the next item to fetch. The database will use the cursor to go directly to the item with this pointer, and does not need to read any rows to get to it. If the cursor returned in the response is empty then the last page has been reached.

From the laravel documentation:

While paginate and simplePaginate create queries using the SQL "offset" clause, cursor pagination works by constructing "where" clauses that compare the values of the ordered columns contained in the query, providing the most efficient database performance available amongst all of Laravel's pagination methods. This method of pagination is particularly well-suited for large data-sets and "infinite" scrolling user interfaces.

Photo by Joshua Sortino on Unsplash

In Laravel as show here, the usage is actually the same as simplePaginate:

$users = User::orderBy('id')->cursorPaginate(10);

Caveats

Using cursor pagination can really boost performance, but there are some things to take into consideration before use. First, it is not possible to jump to a random page, as the cursor is needed to fetch the records. The cursor must be based on a unique or sequential column. Cursor pagination only enhances performance if the order by columns are indexed.

Conclusion

As with every decision building software — everything is a trade off, and it’s about making the best decision you can. If a user will ever need to go backwards, or to some random page maybe stick with offset pagination. If you are implementing an infinite scroll, consider using cursor pagination.

Thanks for reading!