Comparison between the cursor and offset pagination

Overview of Pagination

When one has a large database that needs to be sent to the user and one decides to send it in chunks rather than sending the entire database at a go, there comes the role of pagination. It is quite obvious that the concept of pagination is used by almost all the sites that are visited daily. Before getting into the depth of pagination, one must know about the two different types of paginations.

Two types of paginations can be implemented on the database:

• Cursor Based Pagination: The cursor is considered to be a key parameter in this type of pagination. A variable named cursor is received by the client along with the response. It is a pointer that points at a particular item that needs to be sent with the following request. The server then uses the cursor to seek the other set of items. Cursor based pagination is more complex and is preferred during the case of a real-time data set.

• Offset Based Pagination:  Offset based pagination is a very famous technique wherein the client requests with parameters in a certain limit (the number of results) and offset (the number of records that needs to be skipped). Offset based pagination is easy to use and is preferred for static data.

Pros and Cons of Using Cursor and Offset Pagination

Cursor based pagination is considered to be one of the most effective methods of pagination and must be integrated wherever possible.

The advantages of Cursor based pagination compared to offset pagination are:

• No data is skipped- Rather than sending an offset parameter that behaves like an index, the cursor pagination sends a cursor parameter that behaves like a pointer to a particular record present in the database to show where the last page was left off.
• Great real-time data capabilities- One of the greatest advantages of cursor pagination is its ability to manage real-time data very efficiently. The main reason behind this is that cursors do not need static data. Thus, new rows or items can be easily removed or added without affecting the loading procedure of each page. Due to the increase in real-time applications with time, cursor-based pagination has gained a lot of popularity. However, offset pagination requires static data and if it is not the case then either the item is returned twice or skipped completely.
• Effective management of large data sets -To understand this better one must first know about offset. An offset is the number of records that the database must skip before it selects records. This implies that it just not returns the next data set that has been requested but it also scans through all the other previous data that appears before it. This is a great issue when the number of offset increases. Thus, the page needs a lot of time to load and shall make the user leave the page assuming some glitch in the page.

Everything has its pros and cons. The cursor-based pagination too has its disadvantages. The disadvantages of cursor-based pagination as compared to offset based pagination are:

• Limited sort features

Let us assume that there is a task to sort a user table based on the first name and last name. This can become an issue for cursors since the cursors require the implementation of the unique sequential column to sort. Most of the cursor implementation is based on the timestamp column. Thus, if the requirement is to sort then cursors are not the best choice. One can try different techniques to achieve the same but it will slow down the process and make it even slower than offset.

• Cursors are considered a bit tricky to implement

Cursors may not be very difficult to implement but offsets are generally easy to implement. Thus, if one is looking to implement pagination quickly then offset is the call. This is especially beneficial if the data is static. Thus, the complications can be avoided wherever necessary.

• The Infinite scroll is addictive

Although this is not primarily a cursor issue as offset pagination used in the backend can even implement an infinite scroll. However, extensive use of cursor for real-time application has made it more convenient to use. Infinite scroll is considered a convenient user interface for the users where they can see a lot of data at once. However, this takes away the user’s conscious decisive power to view the next page and thus can lead to a very addictive experience.

Offset Implementation

While retrieving data for an offset pagination one generally requires two parameters in the query: a limit and an offset. This becomes slower when there is an increase in the database items. This happens as the database still needs to be read up to the offset number of rows to identify where it needs to start the selection of data.

It generally has a worst-case complexity of o(n). Also, those large databases are generally not static which shall require frequent writes. The resulting window will often be inaccurate across the various pages.

If one needs to get the first page of the newest posts from the database, the following query must be used

The offset that we can include to know the results of the second page is:

However, this process becomes lengthy when the dataset increases.

Cursor Implementation

The cursor pagination as stated above is a unique pointer to a particular record. with the help of cursors, one gets rid of the need to read all the rows which have already been seen with the help of the WHERE clause. Thus, this makes its complexity o(1). The issue of inaccurate results is dealt with after reading a particular row rather than relying on the fact that the position of records is identical.

The same example can be implemented with the help of the following query using cursor pagination:

To use the cursor, the results need to be returned from the first page in addition to the cursor that has been used for the last item in the set of results. This is a good usage of the cursor to move forward in the result set however if the fetching direction is changed the complexity to retrieve the records increases.

Conclusion

The most widely used pagination for real-time applications is cursor pagination. This is primarily because of the increased frequency of new records that are added to the database. The cursor approach is always recommended as it eliminates the duplicated items and also skips no item. A pointer keeps the track of where the data is and which item needs to be fetched next. There are different situations in which both cursor and offset have their advantages. The usage primarily depends upon the data being dealt with.

However, when static data is being queried the cost of performance may not be enough to use a cursor since the complexity increases in such a situation. It is quite obvious that cursor-based pagination is a better choice as compared to offset pagination but the decision to go for the right approach depends entirely upon the use case and the effect pagination shall have on the product.