API pagination explained

Why pagination is not optional

Unbounded list responses create three classes of failure. Memory pressure hits the server serializing a giant JSON array and the client parsing it on a phone. Latency grows linearly (or worse) with row count — and slow endpoints burn through rate-limit quotas faster because clients retry or prefetch aggressively. Operational risk spikes when a single heavy query contends with writes or evicts hot cache pages.

Pagination is also a contract decision. Once clients depend on a cursor format or page size default, changing it is a breaking change. Design pagination as deliberately as you design authentication — document defaults, maximum page sizes, and what happens at the end of a list.

What clients need from every page

• A stable ordered slice of items for the current request.
• A reliable way to fetch the next slice (and sometimes the previous).
• Signals for end-of-list (has_more, empty next cursor, or total pages).
• Predictable behavior when filters or sort order change mid-iteration.

Offset and limit pagination

The simplest pattern: GET /orders?limit=50&offset=100 translates to SELECT … ORDER BY created_at DESC LIMIT 50 OFFSET 100. Page numbers are just math: offset = (page - 1) × limit. Integrators understand it immediately, and jumping to "page 7" in an admin UI is trivial.

Simplicity hides two serious problems at scale. First, deep offsets are slow. The database must scan and discard the first 100,000 rows to return offset 100,000 — cost grows with depth even when an index exists on the sort column. Second, results shift under concurrent inserts and deletes. If a new row lands at the top of a descending list while you paginate forward, you can skip a row or see a duplicate across pages. Offset pagination is fine for small, mostly static admin tables; it is a poor default for high-churn feeds.

When offset/limit is acceptable

• Internal tools with <10k rows and low write concurrency.
• Exports where you snapshot a consistent read (transaction isolation or replica lag acknowledged).
• Search UIs that already re-query from page one when filters change.

Mitigations if you must use offsets

• Cap offset (e.g. refuse beyond 10,000) and force cursor pagination deeper.
• Require a deterministic sort tie-breaker (see below).
• Document that clients must not assume stable iteration across long sessions.

Cursor pagination (opaque tokens)

Cursor pagination returns an opaque page token alongside each slice. The client sends that token on the next request: GET /orders?limit=50&cursor=eyJpZCI6MTIzfQ. The server decodes the cursor, resumes from the bookmarked position, and encodes a new cursor for the following page. Tokens are often base64 JSON or HMAC-signed blobs so clients cannot forge arbitrary seek positions.

Cursors fix the deep offset problem when implemented as keyset seeks (next section). Even when they wrap offsets internally, they discourage random access and let you change implementation later without clients parsing cursor internals.

Response envelope patterns

Common shapes integrators expect:

{
  "data": [ /* items */ ],
  "pagination": {
    "next_cursor": "eyJ…",
    "prev_cursor": null,
    "has_more": true
  }
}

Some APIs nest items under items or use Link headers (Link: <url?page_token=…>; rel="next") per RFC 8288. Pick one style and use it site-wide. Avoid returning a exact total_count on huge tables unless you are willing to pay COUNT(*) on every request — many products expose has_more only or offer a separate /count endpoint with stricter rate limits.

Keyset (seek) pagination

Keyset pagination — also called seek method — uses the last seen sort key from the previous page as a bound instead of skipping rows with offset. For a list sorted by (created_at DESC, id DESC):

SELECT *
FROM orders
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 50;

With a composite index on (created_at, id), each page turn is an index range scan — cost stays flat whether you are on page 2 or page 2,000. This is the performance backbone behind most production feed APIs.

Stable sort tie-breakers

Never sort by a non-unique column alone. Two rows sharing the same created_at timestamp can appear on adjacent pages in arbitrary order, causing duplicates or gaps when new rows insert between them. Always append a unique column (almost always primary key id) as the final sort key.

Encoding cursors for keyset

The cursor payload typically stores the tuple values from the last row: {"created_at":"2026-06-07T12:00:00Z","id":918273}. Sign or encrypt if tampering would leak data across tenants. Reject cursors that do not match the current filter set — changing ?status=shipped should invalidate an old cursor rather than returning confusing slices.

Forward-only vs bidirectional

Forward pagination covers infinite scroll. Supporting prev_cursor requires symmetric queries or storing a stack client-side. Chat and social feeds are usually forward-only; admin tables sometimes need backward pages — offset may be cheaper for that narrow case.

GraphQL and Relay-style connections

GraphQL pagination often follows the Relay cursor connection spec: edges { node, cursor }, pageInfo { hasNextPage, endCursor }, and arguments first/after (forward) or last/before (backward). The concepts mirror REST keyset pagination — cursors are opaque, sorting must be stable, and first caps page size.

Whether you expose REST or GraphQL, the database layer is usually identical: indexed seek queries, not offsets. Do not implement GraphQL connections with SQL OFFSET unless the list is tiny.

Filters, search, and consistency

Pagination interacts badly with full-text search and volatile filters. If a user paginates through ?q=invoice and edits a record so it no longer matches, row counts shift — cursors tied to sort keys still work; offsets do not. Document that clients should restart from the first page when filters change.

For near-real-time feeds, time-based cursors ("give me items older than this timestamp") are popular but suffer clock skew and duplicate timestamps. Prefer compound (timestamp, id) keys. For eventually consistent replicas, expose whether pagination reads from primary or replica; stale cursors can briefly repeat items after a write — usually acceptable for activity feeds, unacceptable for billing ledgers.

Total counts and "load more" UX

Mobile apps often show "Load more" without page numbers. That maps cleanly to has_more + next_cursor. Desktop admin UIs that display "Page 3 of 412" need either an expensive count query, a cached approximate count, or honest copy ("many pages") instead of fake precision.

Security and abuse considerations

• Cap limit server-side (50–100 typical); ignore client requests for 10,000.
• Authenticate list endpoints that encode tenant scope inside the cursor.
• Rate-limit deep iteration — scraping via pagination is common; throttle by API key and IP.
• Do not expose sequential internal IDs in cursors if that enables enumeration attacks on other resources; use opaque UUIDs or signed payloads.
• Validate cursor schema version — bump version when sort fields change and reject old tokens with a clear 400 error.

Client implementation notes

Client libraries should treat pagination as an async iterator: fetch page, yield items, continue while has_more. Store only the latest cursor, not every page token, unless you implement backward navigation. When syncing locally (mobile offline-first), persist the last cursor per feed and dedupe by primary key — network retries can overlap pages if timeouts are mishandled.

Pair paginated list fetches with idempotent writes on mutations so a user who creates a row while scrolling does not corrupt local cache state when the new item appears twice (once from POST response, once from the next page).

Choosing a pattern

Production checklist

1. Default limit conservatively; enforce a hard maximum.
2. Sort by a unique compound key; document the sort order in API docs.
3. Implement keyset seeks for large lists; reserve offset for shallow admin views.
4. Add composite indexes matching WHERE + ORDER BY columns.
5. Return has_more and next_cursor in a consistent envelope.
6. Invalidate or reject cursors when sort or filter parameters change.
7. Version cursor payloads; handle decode errors with 400, not 500.
8. Load-test page 1 and page 1,000 — latency should not diverge by 100×.
9. Monitor slow list queries; log cursor age and filter cardinality.
10. Document whether totals are exact, approximate, or omitted.

Key takeaways

• Offset is easy but does not scale — deep pages scan discarded rows and drift under writes.
• Keyset pagination keeps page turns O(1) when indexes align with sort and filter columns.
• Stable sorts need a unique tie-breaker — almost always append id.
• Cursors are a contract — sign them, version them, and reset on filter changes.
• Pagination and indexing are one design — the API shape and the database schema must match.

Related reading

• REST API design explained — resources, verbs, errors, and versioning around list endpoints
• Database indexing explained — composite indexes that make keyset seeks fast
• API rate limiting explained — protect paginated endpoints from scrape abuse
• N+1 query problem explained — batch-load related rows after each page fetch