REST API Pagination


772 words • 4 min read
backend › rest-api
,

Pagination

If you are completely new to web development then the concept of pagination simply means that we try to fetch record in batches or chunks. Think about a blog website where a user has written hundreds of blogs in couple of years.

In such scenarios it’s not a wise idea to send all the blog posts at once in the listing page or list API. This is because the data volume will be too high which can cause load on the database, the frontend application (like web or mobile) which is fetching the list of posts.

There are different techniques we employ for pagination, let’s check the most prominent ones.

Offset Pagination

Offset pagination uses a page number or offset value to skip a certain number of records and then fetch the next set. This is the most widely used pagination technique.

Let’s take an example, if we have 120 rows of data, and we want to fetch 10 records in each API call, we will have 12 pages.

  • For the first page, limit=10&page=1
  • For the second page, limit=10&page=2

The corresponding MySQL queries will be like:

  • For the first page:
1
SELECT * FROM todos LIMIT 10;
  • For the second page:
1
SELECT * FROM todos LIMIT 10 OFFSET = 10;

We will generate the offset like: offset=page*limit.

It has a few downsides though and hence it should be avoided in a few scenarios.

  • It become slow on large datasets.
  • It may return inconsistent results if data changes while paging.

Cursor Pagination

Cursor Pagination uses a cursor (a reference to the last record from the previous page) instead of a page number. The cursor is usually a unique and ordered value like an id or created_at.

Let’s take the same example of 120 rows and fetching 10 records per API call. Instead of pages, we pass the last seen value.

  • For the first request: limit=10
  • For the next request: limit=10&cursor=25 (where 25 is the last fetched ID)

The corresponding MySQL queries will be like:

  • For the first page:
1
SELECT * FROM todos ORDER BY id ASC LIMIT 10;
  • For the next page:
1
SELECT * FROM todos WHERE id > 25 ORDER BY id ASC LIMIT 10;

The cursor is generated from the last record of the previous response.

  • It has some advantages and limitations:
  • It is fast and efficient even for large datasets.
  • It provides consistent results when data changes.
  • It does not support jumping to arbitrary pages directly.

Key-Based Pagination

Key-Based Pagination uses a unique, sequential key (such as an ID) to fetch the next set of records. It is very similar to cursor pagination but typically uses a plain key instead of an encoded cursor.

For example, if we fetch 10 records at a time:

  • First request: limit=10
  • Next request: limit=10&last_id=25

The MySQL queries will be like:

  • First page:
1
SELECT * FROM todos ORDER BY id ASC LIMIT 10;
  • Next page:
1
SELECT * FROM todos WHERE id > 25 ORDER BY id ASC LIMIT 10;

Here, last_id comes from the final record of the previous response.

Some key points to note:

  • It performs well on large datasets.
  • It avoids duplicate or missing records.
  • It requires a unique and sortable key.
  • Page numbers are not supported.

Time Window Pagination

Time Window Pagination divides data into time-based ranges, such as minutes, hours, or days, instead of using pages or offsets.

For example, to fetch todos created within a specific time window:

  • First request: start=2023-01-01 10:00:00&end=2023-01-01 10:10:00
  • Next request: start=2023-01-01 10:10:00&end=2023-01-01 10:20:00

The MySQL query might look like: 

1
SELECT * FROM todos WHERE created_at BETWEEN start_time AND end_time ORDER BY created_at ASC;

Each API call moves the time window forward.

This approach has its own trade-offs:

  • It works well for logs, metrics, and event-based data.
  • It scales efficiently for time-series data.
  • Records with the same timestamp can cause duplicates or gaps.
  • Late-arriving data may be missed if not handled carefully.

Outro

Choosing the right pagination strategy is critical for building scalable and reliable APIs. While offset pagination is simple and familiar, it can lead to performance issues and inconsistent results as datasets grow.

Cursor-based, key-based, and time window pagination offer more efficient and stable alternatives, especially for large or frequently changing data. Understanding the trade-offs of each approach helps us select the most suitable pagination technique based on our data size, access patterns, and consistency requirements.

Keep building and shipping!