API Pagination

What it is

Pagination divides a large ordered collection into pages that clients retrieve sequentially. Without pagination, a GET /orders endpoint might return millions of rows, causing multi-second response times, excessive memory use, and potential timeouts. Every collection endpoint that can grow unboundedly must be paginated.

The key design dimensions for a pagination strategy are: stability (does concurrent insertion/deletion corrupt the page sequence?), performance (is the database query efficient at large offsets?), bookmarkability (can a client return to an exact page?), and navigability (can the client jump to arbitrary pages or only navigate sequentially?).

Offset/limit pagination

GET /orders?offset=0&limit=20   → rows 1–20
GET /orders?offset=20&limit=20  → rows 21–40
GET /orders?offset=40&limit=20  → rows 41–60

Response:
{
  "data": [...],
  "pagination": {
    "offset": 20,
    "limit": 20,
    "total": 1842
  }
}

SQL equivalent:
  SELECT * FROM orders ORDER BY created_at DESC
  LIMIT 20 OFFSET 40;          -- scans 60 rows to return 20

Problems: At offset 1,000,000, the database scans and discards a million rows before returning 20. As new records are inserted, records shift positions — a client on page 3 may see duplicates or skip rows compared to page 2. Total count (SELECT COUNT(*)) is expensive on large tables and may be inaccurate by the time the client reads it.

Use when: Small datasets (≤ 10,000 rows), user-visible "page N of M" navigation UIs (support portals, admin dashboards), when random page access is required.

Cursor-based pagination

First page:
GET /orders?limit=20
→ {
    "data": [...],
    "pagination": {
      "nextCursor": "eyJpZCI6IjEwMiIsImNyZWF0ZWRBdCI6IjIwMjQtMDQtMTUifQ==",
      "hasMore": true
    }
  }

Next page:
GET /orders?limit=20&cursor=eyJpZCI6IjEwMiIsImNyZWF0ZWRBdCI6IjIwMjQtMDQtMTUifQ==

Cursor decoded: { "id": "102", "createdAt": "2024-04-15" }

SQL equivalent (keyset):
  SELECT * FROM orders
  WHERE (created_at, id) < ('2024-04-15', '102')
  ORDER BY created_at DESC, id DESC
  LIMIT 20;

The cursor encodes the position of the last item seen (typically as an opaque Base64-encoded JSON blob). The server decodes the cursor and uses a keyset WHERE clause. This is O(log N) regardless of offset because it uses the index directly — no rows are scanned and discarded. New inserts don't shift existing positions.

Limitations: No random page access — you must traverse forward (and backward, if the API supports it). The cursor is opaque and non-bookmarkable in human-readable form. Requires a stable sort key (usually a unique ordered field or combination).

Keyset pagination

Keyset pagination is the underlying database pattern for cursor-based APIs. Instead of OFFSET N, the WHERE clause uses the values of the sort columns from the last item seen:

-- Single sort key (simple):
WHERE id > :last_seen_id ORDER BY id LIMIT 20

-- Composite sort key (created_at + id for tie-breaking):
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC LIMIT 20

The composite key is necessary when created_at is not unique (two records created in the same millisecond). The tie-breaker (usually a unique id) ensures a total order. Ensure a composite index exists on (created_at, id) or the query will degrade to a full scan.

Opaque page tokens

An opaque page token (used by Google API Design Guide, Stripe, and others) is similar to a cursor but explicitly hides its encoding. The token is an opaque string from the client's perspective — its internal format may be an encrypted offset, a signed cursor JSON, or a pointer into a distributed query snapshot. This gives the server flexibility to change pagination implementation without a breaking change. Stripe, for example, encodes the last object's ID as the cursor internally but exposes it as an opaque starting_after parameter.

GET /v1/charges?limit=20
→ { "data": [...], "has_more": true, "url": "/v1/charges",
    "next_page": "ch_abc123" }

GET /v1/charges?limit=20&starting_after=ch_abc123
→ next 20 charges

Total count implications

SELECT COUNT(*) FROM orders WHERE ... is expensive on large tables without a covering index, and becomes stale immediately after execution. Avoid returning precise total counts unless the UX explicitly requires "Page 1 of 4,231". Instead:

• Return only hasMore: boolean (cursor model).
• Return an estimated count from database statistics (acceptable for analytics dashboards).
• Return counts asynchronously via a separate endpoint and cache them.
• Set an explicit contract: "Total counts are approximate and may lag up to 30 seconds."

RFC 5988 Link header

HTTP/1.1 200 OK
Link: <https://api.example.com/orders?cursor=abc>; rel="next",
      <https://api.example.com/orders>; rel="first"
X-Total-Count: 1842

[ ...items... ]

RFC 5988 (updated by RFC 8288) defines the Link HTTP header for expressing pagination links without modifying the JSON body. GitHub's API uses this pattern. It works well for body-agnostic clients and proxies. rel values: next, prev, first, last.

GraphQL Relay connection spec

query {
  orders(first: 20, after: "cursor-abc") {
    edges {
      cursor
      node {
        id
        status
        total
      }
    }
    pageInfo {
      hasNextPage
      hasPreviousPage
      startCursor
      endCursor
    }
  }
}

The Relay connection specification standardises cursor-based pagination in GraphQL. Every paginated list is a connection with: edges (array of { cursor, node }), pageInfo (hasNextPage, hasPreviousPage, startCursor, endCursor), and optionally totalCount. Arguments are first/after (forward) and last/before (backward). Most GraphQL clients and frameworks have built-in Relay connection support.

Pros and cons comparison

Decision checklist

• Is pagination required for all collection endpoints that can grow unboundedly?
• Is the maximum page size enforced server-side (e.g., limit ≤ 100)?
• Is there a composite index on the sort columns used in keyset queries?
• Are cursors opaque to clients (Base64-encoded or signed) to allow format changes?
• Is total count avoided for large tables, or is it clearly documented as an estimate?
• Does the pagination response include a hasMore / nextCursor to avoid the "last page" problem?

Related patterns

• REST API Design — pagination is a core REST collection pattern.
• API Filtering and Sorting — sort keys determine which columns need keyset indexes.
• HATEOAS — Link header and _links.next are hypermedia approaches to pagination.
• GraphQL — Relay connection spec is the standard GraphQL pagination approach.

Further reading

• RFC 8288 — Web Linking (pagination Link headers).
• Relay Cursor Connections Specification — relay.dev/graphql/connections.htm
• Use the Index, Luke — use-the-index-luke.com (keyset pagination chapter).
• Google API Design Guide — Pagination — cloud.google.com/apis/design/design_patterns#list_pagination