API Pagination, Filtering and Sorting Best Practices
On this page
Why every list endpoint needs pagination
An endpoint returning all orders works with 50 orders and times out with 5 million. Add pagination before launch: adding it later is a breaking change, because clients assume they got everything.
Offset pagination and its problems
GET /orders?limit=20&offset=40Simple, and it supports jumping to page 7. But it has two problems:
- Slow on deep pages: the database still reads and discards the first 40,000 rows to return
offset=40000. - Unstable under change: if a new order arrives while someone pages through, every item shifts down one place. The client sees one item twice; a deletion makes it skip one.
Here's the second problem with real numbers. Pages hold 5 orders, newest first:
Page 1 (offset 0): E D C B A client reads these
new order F arrives at the top
Page 2 (offset 5): A ... A again: everything moved down one placeThe client never asked for anything wrong; the list moved under it.
Offsets are fine for small, slowly changing data and admin screens that need page numbers.
Cursor (keyset) pagination
The server returns an opaque cursor pointing just after the last item; the client sends it back for the next page:
GET /orders?limit=3&status=paid
{"items": [1044, 1043, 1041 ...], "nextCursor": "eyJjcmVhdGVkQXQiOiIyMDI2LTA5LTAxVDEwOjQxOjAwLjAwMFoiLCJpZCI6MTA0MX0"}
GET /orders?limit=3&status=paid&after=eyJjcmVhdGVkQXQiOi...
{"items": [1040, 1038, 1037 ...], "nextCursor": "..."}(Those are the order IDs returned by a small Express API.) Base64url is a way of writing any data with only letters, digits, - and _, so it's safe inside a URL; it's an encoding, not encryption, and anyone can decode it. The cursor here is base64url-encoded JSON, {"createdAt":"2026-09-01T10:41:00.000Z","id":1041}: the sort values of the last item. The server's query becomes "items after this position":
let list = orders
.filter(o => !req.query.status || o.status === req.query.status)
.sort((a, b) => b.createdAt.localeCompare(a.createdAt) || b.id - a.id); // newest first, id breaks ties
if (req.query.after) {
const c = decode(req.query.after);
list = list.filter(o => o.createdAt < c.createdAt || (o.createdAt === c.createdAt && o.id < c.id));
}In SQL, that's WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 20, which uses an index on (created_at, id) and stays fast at any depth. New orders arriving at the top don't shift later pages. This is also called keyset pagination.
Rules for cursors:
- Always include a unique tiebreaker (the ID) in the sort; timestamps alone can collide and lose items.
- Keep cursors opaque: clients must not build or parse them, so you can change the format later. Sign or encrypt them if they contain anything sensitive.
- Return
null(or omit the cursor) on the last page.
Filtering and sorting with query parameters
- Filters as query parameters named after fields:
?status=paid&customerId=7&createdAfter=2026-09-01. - Sorting with a field and direction:
?sort=-createdAt(minus for descending) or?sort=createdAt&order=desc. Only allow sorting on indexed fields. - Cap the page size: a client asking for
limit=100000shouldn't get it. The test API clamps to 100 and rejects invalid values with a 400. - Reject unknown parameters or ignore them consistently; silently ignoring a misspelled filter returns unfiltered data, which can be a privacy problem.
Check yourself
If an item is added before the current position while a client pages through, everything shifts down, so the next page starts with an item already seen.
Several items can share a timestamp; without a unique key, items with the same value can be skipped or repeated across pages.
So the server can change their contents or format, for example adding a sort field or switching encoding, without breaking clients. If clients construct or parse cursors, every internal change becomes a breaking API change.
New orders arrive at the top while the user scrolls, shifting every offset. Switch to cursor pagination, which continues after the last item seen.
As query parameters with clear names, such as ?status=paid&sort=-createdAt, limited to fields that are indexed, so filters can't trigger slow full scans.