Exploring Pagination Techniques in Databases: Offset, Keyset, and Cursor Pagination
When working with large datasets, effective pagination is essential for a smooth user experience. The choice of pagination approach can significantly impact performance, especially in apps handling lots of real-time data. In this article, we’ll look at three common pagination techniques—Offset Pagination, Keyset Pagination, and Cursor Pagination—and compare them in terms of use cases, pros and cons, and support for the often-requested "Jump to Page" feature.
1. Offset Pagination
Offset pagination is a straightforward approach used in many applications. This method uses a starting point (or “offset”) and a page size to retrieve rows.
How It Works
Imagine a book with numbered pages; to go directly to page 3, you start counting from page 1 and skip the pages before it. Offset pagination works similarly: if we want to retrieve page 3 with a page size of 10, the offset would be 20, which skips the first 20 rows and retrieves the next 10.
API request:
GET /api/orders?offset=0&limit=10
Example Query
SELECT *
FROM orders
ORDER BY created_at DESC
LIMIT 10 OFFSET 20;
When to Use It
Pros
Cons
Support for “Jump to Page”
Fully Supported. Offset pagination’s design makes it simple to jump to any page since you can calculate the offset directly. If your application needs "Jump to Page" navigation, this is an ideal solution.
2. Keyset Pagination
Keyset pagination, or “value-based pagination,” is a method often used for large datasets. Instead of relying on offsets, keyset pagination retrieves records based on a specific key, like a timestamp, which acts as the starting point for each page.
How It Works
With keyset pagination, each page is defined by the value of the key in the last row of the current page. For instance, if you're sorting by created_at, you retrieve rows with timestamps older than the last timestamp on the current page.
API request:
GET /api/orders?last_key=1729958600
Example Query
SELECT *
FROM orders
WHERE created_at < last_page_last_row_created_at
ORDER BY created_at DESC
LIMIT 10;
When to Use It
Pros
Cons
Support for “Jump to Page”
Not Supported. Because keyset pagination depends on the value of the last row, it doesn’t support jumping to arbitrary pages. You’d need to sequentially retrieve each page until you reach the one you want.
3. Cursor (keyset) Pagination
Cursor pagination is similar to keyset pagination but with a unique twist: instead of a key, it uses a “cursor” to point to the current page. Each page gives a cursor reference for the next one.
How It Works
In cursor pagination, a unique cursor points to the last record on each page. When the user requests the next page, the app uses that cursor to fetch the following set of records.
Recommended by LinkedIn
API request:
GET /api/orders?cursor=eyJpZCI6MX0
Example Query
SELECT *
FROM orders
WHERE id > last_cursor_id
ORDER BY id ASC
LIMIT 10;
When to Use It
Pros
Cons
Support for “Jump to Page”
Not Supported. Cursor pagination doesn’t allow jumping to a specific page since each page depends on the cursor from the previous page.
4. Page-Based Pagination
Page-based pagination involves using a "page" and "page_size" parameter to specify the desired page number.
How It Works
In page-based pagination, the dataset is divided into smaller, manageable chunks or "pages." Each page displays a fixed number of items, allowing users to navigate through large datasets efficiently.
This method enhances user experience by making it easy to find and access specific data without overwhelming the user with too much information at once.
API Request
GET /api/orders?page=2&page_size=20
Example Query
SELECT *
FROM orders
ORDER BY id ASC
LIMIT page_size OFFSET (page - 1) * page_size;
In this API request, we are requesting the second page, where each page contains 20 posts.
3. Windowed Pagination
Windowed pagination, also known as range-based pagination, allows for loading a specific window of records rather than fetching the entire dataset at once. This technique is especially useful for large datasets where only a subset of records is needed for display.
How It Works
In windowed pagination, the dataset is divided into "windows," each containing a set range of items. For instance, if a user wants to view a subset of records, the application can fetch a defined range of rows from the dataset, providing a better balance between performance and user experience.
API Request:
GET /api/orders?start=21&end=40
Example Query:
SELECT *
FROM orders
WHERE id BETWEEN start_id AND end_id
ORDER BY id ASC;
When to Use It
Pros
Cons
Support for “Jump to Page” Partially Supported. While windowed pagination can allow for some form of jumping between records based on defined ranges, it typically lacks the straightforward access offered by offset pagination. Users may find it less intuitive to jump to arbitrary pages without additional logic to compute the appropriate range.