Pagination and Filtering

A GET /orders endpoint that returns every order in the database works fine in development with 50 test rows. In production with 50 million rows, it can take down the database, saturate the network, and freeze the client trying to parse the response. Pagination — returning data in bounded slices — is not an optimization you add later; it's a default you should design in from the start for any endpoint that returns a list.

Offset-based pagination

The simplest approach: the client asks for a page number (or offset) and a page size.

{
  "data": [ /* 20 orders */ ],
  "total": 1583,
  "limit": 20,
  "offset": 40
}

Easy to implement and lets a client jump to an arbitrary page, but it has a real correctness problem: if rows are inserted or deleted between page requests, the offset shifts and the client can see duplicate or skipped rows. It also gets slower on large tables, since the database typically still has to scan past all the skipped rows.

Cursor-based pagination

Instead of a numeric offset, the client passes an opaque cursor — usually derived from the last item it saw — and gets the next batch after that point.

{
  "data": [ /* 20 orders */ ],
  "next_cursor": "cursor_eyJpZCI6MTA2NH0",
  "has_more": true
}

Cursor pagination stays correct even as rows are inserted or deleted, because each page is defined relative to a specific row rather than a shifting numeric position, and it performs consistently on large tables since the database can seek directly to the cursor instead of scanning past skipped rows. The tradeoff is that a client can't jump straight to "page 40" — only forward and backward from a known point — which is why most infinite-scroll feeds use cursors and most classic "page 1, 2, 3..." UIs use offsets.

Filtering and sorting alongside pagination

Real list endpoints combine pagination with filtering and sorting, usually via query parameters:

-created_at is a common convention for descending order. Whatever query parameter format you choose, document it consistently and validate it server-side — an unrecognized sort field or an out-of-range limit should return a clear 400, not be silently ignored or crash the query.

Always cap the page size

Even if a client doesn't specify limit, the backend should apply a sane default and a hard maximum (say, default 20, max 100) rather than trusting a client-supplied limit=1000000. This one habit prevents a huge class of accidental (and intentional) denial-of-service problems.

Pagination protects a backend from a client asking for too much data at once. The next section turns to protecting it from a client asking too often — authentication, authorization, and the other pieces of API security.