Q22 — Why do we paginate APIs instead of returning all results?

What happens if you return everything?

Suppose you have a GET /posts endpoint. Your database has 10 million posts. A client calls the endpoint. What does the server do?

If it returns everything: it queries 10 million rows from the database, serializes them all to JSON (at roughly 500 bytes per post, that's 5 GB of JSON), sends 5 GB over the wire, and waits for the client to parse 5 GB of data before it can display anything.

The database query alone might take minutes. The network transfer, even on a fast connection, takes seconds to minutes. The client's memory would be overwhelmed trying to hold 10 million parsed objects. And for what? The user can read maybe 20 posts on their screen at once.

Even returning a more "reasonable" 10,000 posts has the same problem at a smaller scale: you're doing more work than the user needs, on every dimension — database, network, memory, and rendering.

Pagination exists because every stakeholder in the pipeline has finite capacity, and the client never needs all the data at once.

What does pagination give you?

Pagination breaks the result set into chunks — pages — and returns one chunk at a time. The client asks for page 1, gets 20 results. The user scrolls, the client asks for page 2, gets the next 20. The database only queries the records that will actually be displayed.

This bounds the cost of every operation: one page means one bounded database query, one bounded network response, one bounded render. No matter how large the total dataset grows, each individual request stays cheap.

Pagination also lets you start rendering immediately. The user sees the first 20 results in milliseconds. Without pagination, they'd stare at a loading spinner while 10 million records transferred.

Why is offset-based pagination the obvious choice — and why does it fail?

The first approach most engineers reach for: LIMIT 20 OFFSET 40 — give me 20 results, skipping the first 40. Page 1 is offset 0, page 2 is offset 20, page 3 is offset 40.

This is intuitive. It lets clients jump to arbitrary pages ("go to page 50"). It maps directly to SQL.

But it has two significant problems.

Problem 1: OFFSET is slow on large tables. LIMIT 20 OFFSET 1000000 doesn't magically skip to row 1,000,001. The database must scan and discard the first 1,000,000 rows, then return the next 20. The further into the dataset you paginate, the slower each query gets. On a table with 10 million rows, page 500,000 is much slower than page 1 — the query cost grows linearly with the offset. Most users never reach page 500,000, but crawlers, data exporters, and "load more" implementations on mobile apps often do.

Problem 2: Results shift under concurrent writes. Suppose the client fetched page 1 (posts 1–20). While reading, someone adds a new post. The new post is now at position 1 in the list (sorted by newest). When the client fetches page 2, it asks for OFFSET 20 — but the dataset has shifted. Post 20 from the previous page is now at position 21 in the database. The client receives post 21 onward, meaning post 20 is skipped entirely. Conversely, a deletion causes the same post to appear on two consecutive pages. The user sees duplicates and gaps depending on whether items were inserted or deleted between fetches.

For a paginated blog with rare writes, this may not matter. For a high-write social feed, it's visible and confusing.

Why does cursor-based pagination fix both problems?

Instead of saying "skip N rows," cursor-based pagination says "give me the 20 items that come after this specific item."

The cursor is typically an encoded pointer to a position in the result set — the ID or timestamp of the last item seen. The query becomes:

SELECT * FROM posts
WHERE created_at < '2024-01-15 10:32:00'
ORDER BY created_at DESC
LIMIT 20;

This query uses an index on created_at to jump directly to the right position. No scanning and discarding. The cost is always O(log n) index traversal + 20 row fetches, regardless of how deep into the dataset you are. Page 1 and page 500,000 cost the same.

And because the cursor is anchored to a specific row in the data (by ID or timestamp), insertions and deletions don't move it. A new post added while the user is paginating doesn't shift existing posts relative to the cursor's anchor. The user sees a stable, consistent view of the data as of when they started.

The cursor is usually returned in the API response — an opaque string the client passes back in the next request. It looks like:

{
  "data": [...20 posts...],
  "next_cursor": "eyJpZCI6IDEyMzQsICJ0cyI6ICIyMDI0LTAxLTE1In0="
}

The encoded value (base64 JSON) hides implementation details from the client — the client doesn't need to know it's a timestamp and an ID, it just passes the opaque token back.

What does cursor pagination give up?

Cursor pagination can't jump to an arbitrary page. "Go to page 50" requires knowing what the cursor at position 1000 is, which requires sequentially paginating through pages 1 through 49 first. You can't do OFFSET 980 to jump ahead.

This is acceptable for most real-world use cases: infinite scroll, "load more," and sequential browsing don't require jumping to page 50. But if you need arbitrary page access — search results where users click "go to page 15 of 30" — offset pagination may be the right trade-off despite its problems.

What about page size?

Page size is a policy decision about the trade-off between client round trips and per-request cost. Too small (5 items per page) means many requests and latency. Too large (10,000 items per page) means expensive queries and slow responses.

Common conventions: 20–100 items per page for UI-driven use cases. Many APIs let clients request a page size up to a maximum (e.g., ?limit=100), with the server enforcing a hard cap to prevent abuse. Some APIs use different defaults for different clients — mobile apps often prefer smaller pages (less data over cellular), desktop apps can handle larger ones.

The mental model

Why paginate:
  10M rows × 500 bytes = 5GB per response — database, network, memory, rendering all fail
  User sees 20 items at once — return 20, not 10M

Offset pagination:
  LIMIT 20 OFFSET 40 → intuitive, supports arbitrary page jumps
  Failure 1: OFFSET N scans + discards N rows — slow at large offsets
  Failure 2: concurrent writes shift positions → duplicates and gaps

Cursor pagination:
  WHERE id > last_seen_id LIMIT 20 → anchored to a row, not a position
  Always uses index → constant query cost regardless of depth
  Stable under writes → no duplicates or gaps
  Trade-off: can't jump to arbitrary page

Choose:
  Sequential browsing, high-write data, large datasets → cursor
  Search results, arbitrary page jumps, low-write data → offset (with awareness of limits)

API response includes opaque next_cursor → client passes it back for next page

Pagination is not a UI convenience — it's a correctness and scalability contract. The choice of mechanism (cursor vs offset) determines whether that contract holds as your dataset grows and your write rate increases.