« Back to Index

Pagination: offset vs cursor

View original Gist on GitHub

Tags: #pagination #design

README.md

What is pagination?

We use pagination to request specific segments of a dataset from an API (and a database) instead of everything at once.

Offset-based pagination

The use of offset-based pagination requires passing:

• A limit: the number of records per page
• An offset: the position of the first item of a page

Offsets allow page-specific features, and are simple to implement and use.

[!WARNING] Offsets create database performances issues:

• Page 1: offset = 0, limit = 100, reads and returns 100 records
• Page 2: offset = 100, limit = 100, reads 200 records, returns 100 records

/* First request */

/services?filter[customer_id]=123&page[number]=1&page[size]=500

/* Response */

“data” : [,...],
“meta”: {
  "current_page": 1,
  "per_page": 500,
  "record_count": 1133,
  "total_pages": 3
}

/* Next request */

/services?filter[customer_id]=123&page[number]=2&page[size]=500

Cursor-based pagination

The use of cursor-based pagination requires passing:

• A limit: the number of records per page

The response will include:

• A cursor: the identifier of the last item in a page

And subsequent request will pass such cursor:

• Page 1: limit = 100, reads and returns 100 records, with a cursor pointing to item 100 (cursor = ABC*)
• Page 2: limit = 100, cursor = ABC, reads and returns 100 records starting with the next item after ABC, with a cursor pointing to item 200 (cursor = DEF*)

[!NOTE] *example identifiers

/* First request */
/services?filter[customer_id]=123&limit=500

/* Response */
“data” : [,…],
“meta”: {
  "next_cursor": “1tkSVoL9b7VAvdIhad9aH8”,
  "limit": 500
}

/* Next request */
/services?filter[customer_id]=123&limit=500&cursor=1tkSVoL9b7VAvdIhad9aH8

[!IMPORTANT] Cursor-based pagination provides a consistent query time, ideal for vast and changing datasets, and excluding total count and number of pages makes the performance optimization even more significative.

Issues with cursor-based pagination

• The lack of total number of items in cursor-based responses makes it difficult to provide that information with certainty for clients in one request
• In the case of some UI clients, this removes the ability to provide numbered pages for large datasets, but traversing to previous and next “pages” is still possible.
• It is recommended that you remove page counts as a feature provided in the API responses.
• The total number of items remains a needed feature, for what we recommend a secondary request to obtain that number.
• Lack of page numbers make a strong argument for record discoverability in the UI, an issue we can address with better dataset search capabilities.

/* Count request */
/services?filter[customer_id]=123&total=true

/* Response */
“data” : [],
“meta”: {
  "total": 1113
}