Practical guide

How to implement API pagination

Return bounded and predictable parts of a collection. A client must not download a million rows in one request.

25 minutes · API

First, the short version

Offset or cursor?

Pagination divides a collection in an API into smaller pages. A limit sets the page size and an offset sets the number of skipped records. It is simple, but a high offset becomes slow and changes to the collection can skip or duplicate items.

A cursor marks the last seen position in a stable order. The client sends it in the query string and the server continues after it. A cursor is usually safer for a large or continuously changing list.

Get ready

What you must decide first

Pagination is part of an endpoint contract. Define the order first and its parameters second.

A collection endpoint, such as GET /api/products.
Stable and unique ordering. created_at alone is not enough; add a unique id.
A default and maximum limit, such as 25 and 100. Always validate and constrain the value from a request.
A decision on whether clients need an exact total item count. COUNT over a large table can be expensive.

Steps 1 to 3

Design a stable pagination contract

Start with a simple response shape and preserve the same ordering in the database query on every page.

1. Define parameters and the response

Accept a limit, set a default, and cap it at a reasonable maximum. Return a 400 error for an invalid number, or document how it is normalised.
Do not let a client choose any arbitrary sort column. Accept only predefined values and an ASC or DESC direction.
Keep the JSON response consistent: items contains data, while a page object contains the next cursor, a hasMore flag, or the applied limit.
Return a total count only when clients truly need it and you can calculate it cheaply. Cursor pagination does not need it to work.

GET /api/products?limit=25

HTTP standard for URI references

2. Use an offset for small, stable lists

For an admin screen or short catalogue, a limit and offset can be the clearest option. The first page has offset 0 and the second has an offset equal to the limit.
Always add ORDER BY in SQL. Without it, the database does not guarantee the same order between two requests.
When ordering by a non-unique value, add a unique tie-breaker: ORDER BY created_at DESC, id DESC.
Remember that an inserted or deleted row before the current offset can shift the following page.

SELECT id, name, created_at
FROM product
ORDER BY created_at DESC, id DESC
LIMIT :limit OFFSET :offset;

Official PostgreSQL LIMIT and OFFSET documentation

3. Use a cursor for large or live lists

Build the cursor from the last item on the page, for example from its created_at and id pair. A client should only return it and does not need to understand its contents.
Encode the values into an opaque string, then safely decode and validate them on receipt. Sign it as well when the cursor protects permissions or filters.
The query continues strictly after the last pair. The comparison direction must match the ORDER BY direction.
Fetch limit + 1 rows. The extra row tells you another page exists; do not send that row to the client.

SELECT id, name, created_at
FROM product
WHERE (created_at, id) < (:createdAt, :id)
ORDER BY created_at DESC, id DESC
LIMIT :limitPlusOne;

Official PostgreSQL row comparison documentation

Step 4

Test the boundaries between pages

The most important mistakes happen when moving to the next page and while data changes concurrently.

Walk through the entire collection

Start without a cursor and continue with nextCursor until hasMore is false. No ID may repeat and a page must not contain more items than the limit.
```
curl -s 'http://localhost:8000/api/products?limit=2'
```
Insert a record between two requests

After reading the first page, add a new product and continue with the cursor. Items already returned should not appear again.
Verify invalid inputs

Try a limit of 0, a negative offset, a limit above the maximum, and a malformed cursor. The endpoint should return a predictable error, not a database exception.
```
curl -i 'http://localhost:8000/api/products?limit=25&cursor=broken'
```

If something goes wrong

Common problems

An item appears on two pages

Check that ordering is unique and put every ordering value in the cursor. created_at without a unique id is not a stable boundary.

A query with a high offset becomes slow

The database must still find the skipped rows. Move to cursor pagination and add an index matching the filters and ORDER BY.

The next page is empty even though hasMore was true

Derive hasMore by fetching limit + 1, not because a page contains exactly the limit. Data may also change between requests, so the client must safely handle an empty page.

A cursor can be used with a different filter

Include a fingerprint of relevant filters and ordering in the signed cursor, or reject the cursor when they change. Otherwise, its boundary belongs to a different list.

Done

The API returns predictable pages.

The API now protects the database with a limit and gives clients a stable path through a collection. Keep offsets for small lists and use cursors where data grows or changes while being read.

Read query string in the glossary All guides