Cursor-Based Pagination

Photo by Eugenio Mazzone on Unsplash

Before diving into Cursor Based pagination, here is the small context about pagination:

Pagination is a technique used in software applications, websites, and databases to divide content into discrete pages, making it easier to display, manage, and navigate large sets of data. Instead of loading and displaying all the data at once, which can be overwhelming and resource-intensive, pagination helps in breaking the content into manageable chunks.

Based on the application’s use-case, pagination technique can be utilised in multiple ways. Common use-case we all observed is via:

1. Providing “Next” or “Previous” buttons along side page numbers listed out to select from.
2. Infinite scrolling where user can infinitely scroll down the screen, new content will keep adding up when user reaches the bottom of the view port. (Social media feeds, e-commerce search results…)

Both of the above pagination use-cases can be achieved with two approaches:

1. Offset Based approach
2. Cursor Based approach

Offset Based approach is simple and easy to implement and it works well for static data sets which don’t get updated frequently.

It takes Limit (Number of records needed) and Offset / PageNumber (Indicates where to start fetching the data )as the client-side parameters. i.e:

fetching records for 1st page: Offset = 0, Limit = 10

SELECT * FROM records LIMIT 10 OFFSET 0;

fetching records for 10th page: Offset = 90, Limit = 10

SELECT * FROM records LIMIT 10 OFFSET 90;

Drawbacks of Offset based approach:

1. This approach is not well suited when the offset value increases. As the offset increases, the database must skip an increasing number of rows before returning the desired results. This can lead to slower query performance, especially with large datasets.
2. Skipping a large number of rows consumes more CPU and memory resources, making the process inefficient and potentially impacting the performance of the database server.
3. For data that updates frequently, the current page window might be inaccurate after some time. Imagine a user has fetched the first 10 records in their feed. After sometime, 5 more records were added. If the users scroll to the bottom of the feed and fetches page 2, the same records in the original page 1 will be fetched, and the user will see duplicate records.

Now we understand what is pagination, different approaches to tackle it, advantages and drawbacks of one of the most used pagination type which is offset-based approach. Lets dive deep into Cursor-Based Pagination and understand its use-cases and advantages.

Here the term cursor is nothing but a unique identifier or set of identifiers that mark the position of an item in the dataset. This could be a timestamp, an ID, or a combination of fields that uniquely identify a record.

Cursor-based pagination uses a unique identifier (the cursor) to a specific record in a dataset. Instead of saying “give me records from 11 to 20,” it says “give me 10 items starting after [specific item].”.

This method can handle large datasets more gracefully and can be more efficient in certain scenarios, especially when dealing with dynamic data that frequently changes.

1. Here in the initial API call, client (Frontend) provides only the limit. API responds with requested number of records and along with that it also provides us the cursor pointing towards the last item in the dataset.
2. In subsequent API calls, client should provide both the limit and the cursor value it got from the previous API’s response.
3. Server uses this cursor value to fetch the records from the database. Cursor value which is nothing but a unique identifier, identifies the item quickly and returns us the requested number of records before or after that item.

let’s take an example of a social media feed where millions of posts exists in the database and each of the post has its own unique id.

when we make the initial call we send only limit i.e

1. fetching records for 1st page: Limit = 10

/* underlying sql query */

SELECT * FROM posts
ORDER BY post_id
LIMIT 10;

This responds us with the first 10 posts and along with that the cursor value (id of the last post) i.e:

/* API response */
{
  "pagination": {
    "size": 10,
    "cursor": "post_10"
  },
  "results": [
    {
      "id": "post_1",
      "author": {
        "id": "123",
        "name": "John Doe"
      },
      "content": "I got a new car",
      "image": "https://www.example.com/newcar.jpg",
      "reactions": {
        "likes": 20,
        "haha": 15
      },
      "created_time": 1620639583
    }
    // ... More posts.
  ]
}

In subsequent calls we need to send this cursor value and limit i.e

2. fetching records for 2nd page: Limit = 10, cursor: “post_10”

SELECT * FROM posts
WHERE post_id > "post_10"
ORDER BY post_id
LIMIT 10;

This responds us with 10 posts that appears after post_10 and along with that the cursor value (id of the last post) i.e:

/* API response */
{
  "pagination": {
    "size": 10,
    "cursor": "post_20"
  },
  "results": [
    {
      "id": "post_11",
      "author": {
        "id": "234",
        "name": "Chidanandan"
      },
      "content": "I got a new dog",
      "image": "https://www.example.com/newdog.jpg",
      "reactions": {
        "likes": 20,
        "haha": 15
      },
      "created_time": 1620639583
    }
    // ... More posts.
  ]
}

When requesting the previous page, we ask the database to send us records lesser than the cursor.

SELECT * FROM posts
WHERE post_id < "post_10"
ORDER BY post_id
LIMIT 10;

Handling edge cases:

To make sure we don’t call API when there is no subsequent data, we can use next_cursor instead of cursor. Here next_cursor is nothing but the unique id of next element in the database. For example if we are requesting 10 elements, then the next_cursor value will be post_11 instead of post_10.

This enables us to know in prior that if there are subsequent posts are available or not. If next_cursor value is empty in the response then we can conclude that the current page is the last page.

Drawbacks of Cursor based approach:

1. Cursor-based pagination does not allow users to jump directly to an arbitrary page (e.g., “Go to page 5”). This can be a limitation for applications where users expect to navigate directly to a specific page.
2. Cursor-based pagination is more complex to implement compared to offset-based pagination. It requires additional logic to handle cursors and manage state between requests.

When to use Cursor-Based Pagination:

1. Cursor-based pagination is more efficient for large datasets because it avoids the performance cost associated with skipping large numbers of rows, which is common with offset-based pagination.
2. If dataset is frequently updated (e.g., new records are added or deleted), cursor-based pagination provides more consistent results by focusing on unique identifiers and avoiding issues with data changes.
3. Ideal for applications where users are continuously scrolling through content, such as social media feeds, news articles etc.

When to use Offset-Based Pagination:

1. For smaller datasets where performance is not a concern, offset-based pagination is straightforward and easy to implement.
2. When the data does not change frequently, offset-based pagination can be sufficient and simpler to use without worrying about consistency issues.
3. If users need to jump to a specific page number (e.g., “Page 5”), offset-based pagination allows direct access to arbitrary pages, which is more user-friendly in some scenarios.

Selecting the right pagination approach depends on balancing these factors based on your specific application needs and data characteristics.

Checkout my articles at https://chidanandan.medium.com/