How Should You Design API Pagination for Millions of Records?

Cursor-Based Pagination for Large REST APIs

TL;DR

For large datasets, use cursor-based or keyset pagination instead of offset-based pagination. Offset pagination (?page=1&limit=20) performs poorly with millions of records and can produce inconsistent results while data changes. Modern PetstoreAPI uses cursor-based pagination with opaque tokens and HATEOAS links for efficient, predictable traversal.

Try Apidog today

Introduction

Imagine an API that returns a list of pets from a database containing 10 million records. A client requests:

GET /pets?page=500000&limit=20

The database may execute a query equivalent to:

SELECT *
FROM pets
OFFSET 10000000
LIMIT 20;

Although the API returns only 20 pets, the database may need to scan millions of rows to reach the offset. The query becomes slow, and the request may eventually time out.

This is the offset pagination problem. It works well for small datasets but becomes expensive at scale. The old Swagger Petstore does not define a pagination strategy. Modern PetstoreAPI uses cursor-based pagination to support large datasets with consistent performance.

In this guide, you’ll learn:

• Why offset pagination degrades at scale
• How cursor-based and keyset pagination work
• How to design a practical pagination response
• How to implement and test pagination in Modern PetstoreAPI
• How to choose the right strategy for your API

Why Offset Pagination Fails at Scale

Offset pagination is simple and familiar, but it has several limitations.

How Offset Pagination Works

Each page maps to a database offset:

GET /pets?page=1&limit=20

OFFSET 0 LIMIT 20

GET /pets?page=2&limit=20

OFFSET 20 LIMIT 20

GET /pets?page=3&limit=20

OFFSET 40 LIMIT 20

The database skips the offset rows and returns the next limit rows.

Problem 1: Performance Degrades with Page Number

Consider these queries:

-- Page 1: scans approximately 20 rows
SELECT *
FROM pets
OFFSET 0
LIMIT 20;

-- Page 1,000: scans approximately 20,020 rows
SELECT *
FROM pets
OFFSET 20000
LIMIT 20;

-- Page 500,000: scans approximately 10,000,020 rows
SELECT *
FROM pets
OFFSET 10000000
LIMIT 20;

The database processes every row before the requested offset, even though those rows are discarded. As the page number increases, query cost generally increases with it.

Problem 2: Results Can Change Between Requests

Offset pagination can produce duplicates or skipped records when the underlying data changes between requests.

Suppose a client requests two pets per page.

The first request returns:

GET /pets?page=1&limit=2

[Pet A, Pet B]

Now someone adds Pet Z, which sorts before the existing records.

The second request returns:

GET /pets?page=2&limit=2

[Pet B, Pet C]

Pet B appears on both pages because the insertion shifted the offset. Deletions can cause the opposite problem: a record may be skipped entirely.

Problem 3: Deep Pagination Is Expensive

Most users do not browse beyond the first few pages. However, if your API accepts requests such as:

GET /pets?page=1000000&limit=20

the database still has to process the deep offset. Expensive deep-pagination queries can also increase resource consumption and create an abuse or denial-of-service concern for public APIs.

When Offset Pagination Is Acceptable

Offset pagination is usually reasonable for:

• Small datasets, such as fewer than 10,000 records
• Internal APIs with controlled usage
• Admin interfaces where users rarely navigate deeply
• Data that changes infrequently
• Interfaces that require random page access

For public APIs or large, frequently changing datasets, cursor-based pagination is usually a better choice.

Cursor-Based Pagination Explained

Cursor-based pagination uses an opaque token to represent a position in an ordered result set.

Basic Request and Response

The initial request does not include a cursor:

GET /pets?limit=20

The response includes a token for the next page:

{
  "data": [
    {}
  ],
  "pagination": {
    "nextCursor": "eyJpZCI6IjAxOWI0MTMyLTcwYWEtNzY0Zi1iMzE1LWUyODAzZDg4MmEyNCJ9",
    "hasMore": true
  }
}

The client sends that token with the next request:

GET /pets?cursor=eyJpZCI6IjAxOWI0MTMyLTcwYWEtNzY0Zi1iMzE1LWUyODAzZDg4MmEyNCJ9&limit=20

The client should treat the cursor as opaque. It should store it and send it back without parsing or modifying it.

Benefits

1. More Consistent Performance

The database can seek directly to the cursor position using an index:

SELECT *
FROM pets
WHERE id > '019b4132-70aa-764f-b315-e2803d882a24'
ORDER BY id
LIMIT 20;

Unlike a deep offset query, this uses an index seek rather than scanning and discarding all preceding rows. Query cost remains more consistent regardless of how far the client has paged.

2. More Predictable Traversal

The cursor represents the last position seen in the ordered result set. Inserts before that position do not shift the client’s next request in the same way that offset pagination does.

Your ordering must be deterministic. If the primary sort value is not unique, include a unique tie-breaker such as id in the ordering and cursor.

3. Reduced Deep-Pagination Abuse

Cursor pagination does not provide an arbitrary page number that clients can jump to. Clients must normally traverse the result set sequentially, which reduces the cost exposure of deep page requests.

Cursor Format

A cursor often contains the values needed to resume the query. It may be encoded as base64, but its format should remain an implementation detail.

For example, a decoded cursor might look like this:

{
  "id": "019b4132-70aa-764f-b315-e2803d882a24",
  "createdAt": "2026-03-13T10:30:00Z"
}

For Modern PetstoreAPI, the cursor includes the resource ID and sort field. In a production API, consider signing or otherwise protecting cursor contents so clients cannot alter the pagination position.

Keyset Pagination for Sorted Data

Keyset pagination is a cursor-based approach that exposes the last value from the previous page instead of returning an opaque token.

How It Works

The first request specifies a sort field:

GET /pets?limit=20&sortBy=createdAt

The response contains the last item’s sort value:

{
  "data": [
    {
      "id": "...",
      "createdAt": "2026-03-13T10:00:00Z"
    },
    {
      "id": "...",
      "createdAt": "2026-03-13T10:30:00Z"
    }
  ]
}

The client uses that value in the next request:

GET /pets?limit=20&sortBy=createdAt&after=2026-03-13T10:30:00Z

The corresponding SQL query is:

SELECT *
FROM pets
WHERE created_at > '2026-03-13T10:30:00Z'
ORDER BY created_at
LIMIT 20;

An index on created_at allows the database to seek to the requested position efficiently.

For duplicate sort values, use a compound condition and ordering. For example:

SELECT *
FROM pets
WHERE (created_at, id) > ('2026-03-13T10:30:00Z', '019b4132-70aa-764f-b315-e2803d882a24')
ORDER BY created_at, id
LIMIT 20;

When to Use Keyset Pagination

Keyset pagination is useful when:

• Data is naturally sorted by a timestamp, ID, or another indexed field
• Clients need to understand the pagination key
• Transparent pagination is preferred
• Sequential access is sufficient

Modern PetstoreAPI uses cursor-based pagination by default and supports keyset pagination for time-series data.

How Modern PetstoreAPI Implements Pagination

Modern PetstoreAPI uses cursor-based pagination with HATEOAS links.

Request Format

The first page:

GET /pets?limit=20

A subsequent page:

GET /pets?cursor={token}&limit=20

Supported parameters include:

• limit — Number of results per page. The default is 20, and the maximum is 100.
• cursor — Opaque pagination token returned by the previous response.

Response Format

{
  "data": [
    {
      "id": "019b4132-70aa-764f-b315-e2803d882a24",
      "name": "Fluffy",
      "species": "CAT"
    }
  ],
  "pagination": {
    "limit": 20,
    "hasMore": true,
    "nextCursor": "eyJpZCI6IjAxOWI0MTMyLTcwYWEtNzY0Zi1iMzE1LWUyODAzZDg4MmEyNCJ9"
  },
  "links": {
    "self": "https://petstoreapi.com/pets?limit=20",
    "next": "https://petstoreapi.com/pets?cursor=eyJpZCI6IjAxOWI0MTMyLTcwYWEtNzY0Zi1iMzE1LWUyODAzZDg4MmEyNCJ9&limit=20"
  }
}

Key Features

Opaque Cursors

Clients pass cursors back to the API without parsing them. This allows the server to change the internal cursor representation without requiring client changes.

HATEOAS Links

The links object provides ready-to-use URLs. Clients can follow the next link instead of constructing cursor URLs themselves.

hasMore Flag

The hasMore field tells clients whether another page exists. Clients can stop requesting pages when it becomes false.

Limit Validation

The maximum page size is 100. Rejecting larger values prevents clients from requesting unnecessarily large responses.

See the Modern PetstoreAPI pagination documentation for complete details.

Designing a Consistent Pagination Response

Modern PetstoreAPI wraps every paginated collection in the same structure:

{
  "data": [],
  "pagination": {},
  "links": {}
}

Why Use a Collection Wrapper?

A wrapper provides:

• Extensibility — Add metadata without changing the top-level response shape
• Consistency — Use the same structure across collection endpoints
• HATEOAS support — Return navigation links alongside the data

Pagination Metadata

A complete response might include:

{
  "pagination": {
    "limit": 20,
    "hasMore": true,
    "nextCursor": "...",
    "totalCount": 1000
  }
}

totalCount should be optional. Computing an exact count can require a separate, expensive COUNT query on large datasets. Include it only when clients actually need it.

Recommended Client Loop

A client can consume cursor pages with a loop like this:

let cursor;

do {
  const params = new URLSearchParams({ limit: "20" });

  if (cursor) {
    params.set("cursor", cursor);
  }

  const response = await fetch(`/pets?${params}`);
  const body = await response.json();

  for (const pet of body.data) {
    processPet(pet);
  }

  cursor = body.pagination.hasMore
    ? body.pagination.nextCursor
    : undefined;
} while (cursor);

The client stops when hasMore is false or no next cursor is returned.

Testing Pagination with Apidog

Use Apidog to test pagination behavior, response structure, validation rules, and edge cases.

Test Scenarios

1. First Page

GET /pets?limit=20

Verify that:

• The response contains up to 20 results
• pagination.hasMore is present
• nextCursor is present when another page exists

2. Subsequent Pages

GET /pets?cursor={token}&limit=20

Verify that:

• The cursor is accepted
• The response contains the next set of records
• hasMore correctly reflects whether another page exists
• nextCursor is present only when another page exists

3. Last Page

GET /pets?cursor={lastToken}&limit=20

Verify that:

• The response can contain fewer than 20 results
• hasMore is false
• nextCursor is absent or null

4. Empty Results

GET /pets?status=NONEXISTENT&limit=20

Verify that:

• data is an empty array
• hasMore is false
• No next cursor is returned

5. Limit Validation

GET /pets?limit=1000

Verify that the API returns:

400 Bad Request

because the requested limit exceeds the maximum of 100.

Example Assertions

The following assertions use the pm syntax supported by Apidog-style API tests:

pm.test("Response has pagination", () => {
  const body = pm.response.json();

  pm.expect(body).to.have.property("pagination");
  pm.expect(body.pagination).to.have.property("hasMore");
});

Test the HATEOAS links as well:

pm.test("Response has links", () => {
  const body = pm.response.json();
  const links = body.links;

  pm.expect(links).to.have.property("self");

  if (body.pagination.hasMore) {
    pm.expect(links).to.have.property("next");
  }
});

Also test behavior across multiple requests:

1. Send the first-page request.
2. Capture pagination.nextCursor.
3. Send the cursor in the next request.
4. Confirm that records do not repeat across pages.
5. Continue until hasMore becomes false.

Choosing the Right Pagination Strategy

Different access patterns require different strategies.

Offset Pagination

Use offset pagination when:

• The dataset is small, such as fewer than 10,000 records
• Users need random access, such as jumping directly to page 50
• Data changes infrequently
• The API is internal and usage is controlled

Avoid it when:

• The dataset is large, such as more than 100,000 records
• Query performance matters
• Data changes frequently
• The endpoint is public and allows unbounded page numbers

Cursor-Based Pagination

Use cursor pagination when:

• The dataset is large
• Consistent performance matters
• Data changes frequently
• Sequential access is sufficient
• You want the server to control pagination state

Avoid it when:

• Users must jump to arbitrary pages
• Random access is a core requirement
• The additional cursor handling is not justified for a small dataset

Keyset Pagination

Use keyset pagination when:

• Data is naturally sorted
• The sort column is indexed
• Transparent pagination is preferred
• Performance matters

Avoid it when:

• The sort order is highly complex
• Multiple sort fields are required but cannot be represented reliably
• Clients should not depend on internal sort keys

Modern PetstoreAPI recommends cursor-based pagination for public APIs and large datasets.

Conclusion

Pagination is essential for APIs that return large collections. Offset pagination is easy to implement, but its query cost increases with the page number and its results can shift when data changes.

Cursor-based pagination uses an ordered position instead of a numeric offset. With indexed queries, opaque tokens, and consistent response metadata, it provides a more scalable way to traverse millions of records.

Modern PetstoreAPI combines:

• Opaque cursor tokens
• HATEOAS navigation links
• A hasMore flag
• Maximum limit validation
• A consistent collection wrapper

Test your implementation with Apidog to validate response shapes, cursor behavior, filtering, empty results, limit validation, and the final page.

Key takeaways:

• Avoid offset pagination for large datasets
• Use cursor-based pagination for scalable sequential access
• Use keyset pagination when clients can work with a sorted key
• Add metadata and navigation links to collection responses
• Define deterministic ordering for every cursor query
• Test first, middle, last, empty, and invalid requests

FAQ

Why not return all results without pagination?

Returning millions of records in one response can cause memory issues, slow network transfers, and a poor client experience. Pagination limits the amount of data processed and transferred per request.

Can clients jump to a specific page with cursor pagination?

No. Cursor pagination normally requires sequential access. If random access is required, use offset pagination for a small dataset or provide search and filtering capabilities instead.

How do I handle pagination with filtering?

Include the filter parameters in every pagination request:

GET /pets?status=AVAILABLE&cursor={token}&limit=20

The cursor should represent the position within that filtered and sorted result set. The server should validate that the cursor is compatible with the requested filter and sort state.

Should I include totalCount in pagination responses?

Include it only when clients need it and the dataset is small enough for the additional query cost. Computing an exact total for a large dataset can require a separate COUNT query.

How do I implement cursor pagination in SQL?

Use a WHERE clause based on the cursor value and order by the same column:

SELECT *
FROM pets
WHERE id > ?
ORDER BY id
LIMIT 20;

Create an index on the sort column. If the sort column is not unique, add a unique tie-breaker such as id.

What if cursor tokens become invalid?

Return 400 Bad Request with a clear error message. A cursor may become invalid if the referenced resource is deleted or the pagination state has expired.

How long should cursors remain valid?

Modern PetstoreAPI cursors remain valid indefinitely as long as the referenced resource exists. Other APIs may expire cursors after a fixed period, such as 24 hours.

Can I use cursor pagination with multiple sort fields?

Yes, but the cursor must encode all values needed to resume the ordering. This makes cursor handling more complex. When possible, use a single composite sort key or a deterministic combination of the sort fields and a unique ID.