API Pagination Patterns: Offset, Cursor, and Keyset

Why Paginate?

Returning an entire dataset in a single response is rarely safe. Even a modest table of 100 000 rows can exhaust server memory, saturate the network, and time out the client. Pagination breaks the result set into manageable pages, giving clients a predictable, bounded response size.

Three patterns dominate production APIs: offset-based, cursor-based, and keyset. Each has a distinct performance profile and UX trade-off.

Offset-Based Pagination

The simplest approach: ?limit=20&offset=40 asks the server to skip 40 rows and return the next 20.

GET /api/orders?limit=20&offset=40 HTTP/1.1

{
  "data": [...],
  "meta": { "total": 1234, "limit": 20, "offset": 40 }
}

Pros: trivial to implement, supports random-page jumps (jump to page 7), total count is easy to expose.

Cons: expensive on large tables — the database must scan and discard the first offset rows on every query (OFFSET 100000 forces a full index scan). Worse, if rows are inserted or deleted between requests, pages overlap or skip records — the classic *phantom row* problem.

Use offset pagination when your dataset is small (< 10 000 rows), when users need random-page access (e.g. admin dashboards), and when simplicity outweighs correctness.

Cursor-Based Pagination

Instead of a numeric offset, the server returns an opaque cursor — a token encoding the position of the last seen item. The client passes this token to fetch the next page.

GET /api/orders?limit=20 HTTP/1.1

{
  "data": [...],
  "next_cursor": "eyJpZCI6IDQyfQ==",
  "has_more": true
}

GET /api/orders?limit=20&cursor=eyJpZCI6IDQyfQ== HTTP/1.1

The cursor is typically a base64-encoded JSON blob containing the sort key of the last item: {"id": 42} or {"created_at": "2024-01-15T10:30:00Z", "id": 42}.

Pros: stable — inserts and deletes between pages do not affect correctness. Efficient — the database uses an indexed WHERE id > 42 clause instead of OFFSET.

Cons: no random-page access; clients can only move forward (or backward with a prev_cursor). Total count is harder to expose cheaply.

Keyset Pagination

Keyset pagination is cursor pagination made explicit. Rather than an opaque token, the client sends the actual column values of the last seen row:

GET /api/orders?limit=20&after_id=42 HTTP/1.1
GET /api/orders?limit=20&after_created=2024-01-15T10:30:00Z&after_id=42 HTTP/1.1

The server translates this to an indexed query:

SELECT * FROM orders
WHERE (created_at, id) > ('2024-01-15T10:30:00Z', 42)
ORDER BY created_at, id
LIMIT 20;

Pros: maximum database efficiency (uses a composite index), fully stable under concurrent writes.

Cons: requires a stable, unique sort key. Multi-column keysets expose internal schema details to the client.

Link Headers for Pagination

RFC 8288 defines the Link header for hypermedia pagination. GitHub's API uses this pattern:

Link: <https://api.example.com/orders?page=2>; rel="next",
      <https://api.example.com/orders?page=10>; rel="last"

Supported rel values: next, prev, first, last. Clients that parse Link headers can paginate without hard-coding URL construction logic.

Choosing the Right Approach

A safe default for new APIs: start with cursor-based pagination using an opaque token. It is stable, efficient, and forward-compatible — you can change the cursor encoding without breaking clients.