Skip to content
Tutorials Center
My learning

Topics

Lesson 30 of 39 ยท Pagination, Caching and Versioning

API Pagination: Offset, Page and Cursor

Design API pagination that scales: offset and page numbers, cursor (keyset) pagination, Link headers, response envelopes, sorting rules and common pitfalls.

A collection endpoint such as GET /articles cannot return a million rows in one response. It would take too long, use too much memory on both sides, and hammer your database. Pagination splits a collection into pages that the client requests one at a time. It looks simple, but the choice of pagination style affects performance, correctness when data changes, and how easy your API is to use.

This lesson compares the two main styles, offset and cursor, shows the classic bug of offset pagination, and gives you response formats and rules to apply to any list endpoint.

Pagination styles

Page and size ?page=3&per_page=20
Offset and limit ?offset=40&limit=20
Cursor ?after=abc&limit=20

Page and offset are the same idea.

1 / 2

Offset pagination

The client sends limit (how many) and offset (how many to skip), or equivalently page and per_page. In SQL:

SQL
SELECT id, title FROM articles
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 40;   -- page 3 with 20 per page

Advantages: easy to understand, supports "jump to page 17", and can show "page 3 of 12" if you also return a total count.

Problems:

  1. Slow on deep pages. The database must read and discard all 40, or 400,000, skipped rows before returning your 20. Cost grows with the offset.
  2. Unstable when data changes. If a new row is inserted at the top while a client reads page 1 then page 2, every row shifts down by one. The last item of page 1 appears again at the top of page 2 (a duplicate). Deletes cause the opposite: items are skipped.
  3. Total counts are expensive. COUNT(*) on a big, filtered table can cost more than the page query.
Node.js Offset pagination drifts when data changes

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

The client sees items 8 and 7 twice, because two inserts pushed them onto page 2.

Cursor (keyset) pagination

Instead of "skip N rows", the client says "continue after this item". The server remembers the sort key of the last row and asks the database for rows after it:

SQL
SELECT id, title, created_at FROM articles
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 21;     -- ask for one extra row to know whether a next page exists

With an index on (created_at, id) the database jumps straight to the position, so page 1 and page 10,000 cost the same. New or deleted rows elsewhere never shift your position, so no duplicates and no skips.

The cursor is handed to the client as an opaque string, usually Base64URL of the sort values. "Opaque" means clients must not parse it, so you can change its content later. If it is sensitive or must not be tampered with, sign it. Cursors cannot jump to page 17, which is rarely needed for feeds, logs, timelines and infinite scroll.

The ordering must be total: sort by a unique tiebreaker (the id) or rows with equal created_at will be skipped or repeated.

Node.js Cursor pagination over a live HTTP API

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Pages 2 and 3 continue exactly where page 1 ended even though two articles were inserted in between, and the walk ends when nextCursor is null.

Response formats

Put the list in an object (an envelope), not a bare array, so you can add metadata later without breaking clients:

JSON
{
  "data": [ { "id": 7, "title": "..." }, { "id": 6, "title": "..." } ],
  "page": { "limit": 2, "nextCursor": "eyJpZCI6Nn0", "prevCursor": null },
  "total": 241
}

Some APIs instead use the standard Link header (RFC 8288), which lets clients follow links without knowing URL rules:

HTTP
Link: </articles?after=eyJpZCI6Nn0&limit=2>; rel="next",
      </articles?limit=2>; rel="first"

Either way, return full URLs or cursors and let clients follow them, so you can change the scheme later.

Rules for any list endpoint

  • Always paginate, even when the collection is small today, because adding it later breaks clients.
  • Default and maximum limits: for example 20 by default and at most 100. Clamp or reject (400) bigger values.
  • Stable, total sort order, with a unique tiebreaker. Without ORDER BY there is no guarantee at all.
  • Validate limit, page and cursors; negative or non-numeric values get 400.
  • Counts are optional. If you offer a total, consider an approximate count or ?include=total.
  • Filters and sorting use the same cursor mechanism: encode the sort fields in the cursor, and reject a cursor reused with different filters.
  • Document the style per endpoint, and use one style across the API.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why is offset pagination slow for very deep pages?

    LIMIT 20 OFFSET 400000 still scans past 400,000 rows before returning 20.
  2. 2. What problem happens with offset pagination when new rows are inserted at the top between two requests?

    Positions are relative to the current data, so inserts and deletes move rows across page boundaries.
  3. 3. Why should the sort order for cursor pagination include a unique column?

    A total order guarantees every row has exactly one position, so the "after" condition is exact.
  4. 4. What does it mean that a cursor is opaque?

    Opaque cursors let you change their internal structure without breaking clients.
  5. 5. How can a server know whether a next page exists without a COUNT query?

    If limit + 1 rows come back, there is at least one more. Return only limit rows and a next cursor.

Summary

  • Always paginate collections, with a default and a maximum page size.
  • Offset/page pagination is simple and allows page jumps, but gets slower with depth and drifts when data changes.
  • Cursor (keyset) pagination uses the last row's sort key, stays fast and stable, and returns an opaque next cursor.
  • Use a total sort order with a unique tiebreaker, and fetch limit + 1 rows to detect the next page.
  • Wrap lists in an envelope or use Link headers, and let clients follow links.

Next: HTTP Caching and ETags