Pagination: offset vs cursor

Pagination is how an API hands out a large, growing list in bounded pieces. Offset pagination says “skip 40, give me 20”: it is simple and lets users jump to page 7, but deep pages get slower and every insert or delete between requests shifts the window, so items repeat or vanish. Cursor pagination says “give me 20 after this item”: every page costs the same and the window doesn’t drift, but you can’t jump to page 7 and totals become a separate problem. Most large public APIs now use cursors wrapped in opaque tokens.

Picture it first: live cricket commentary

You are following a match on a ball-by-ball commentary page. The newest ball is at the top, and the page shows 20 balls at a time. You read the first 20, then tap “older”.

Suppose the app asks for “balls 21 to 40”. While you were reading, two more balls were bowled and went in at the top. Every older ball slid down two places, so “21 to 40” now starts with the last two balls of your first screen, and you read them again. If the scorer had instead deleted a ball to correct a mistake, everything would slide up one place, and one ball would fall into the gap between your two screens and never be shown.

Now suppose the app asks for “the 20 balls before over 14.3”. New balls at the top don’t matter; you continue exactly where you stopped. The price is that you can’t ask for “page 37” any more, because a bookmark knows where you are, not how far it is to the end.

Map it back. “Balls 21 to 40” is offset pagination: a position counted from the top of a list that keeps changing. “Before over 14.3” is cursor pagination: a bookmark that names the last item you saw.

Why paginate at all

A list endpoint without a limit is a time bomb. A merchant with 2 million orders at about 1 KB of JSON each would get a 2,000,000 × 1 KB ≈ 2 GB response: a database connection tied up for the whole scan, rows held in server memory, minutes on a mobile link and probably a crashed app. A page size bounds database time, server memory, bytes on the wire and client memory.

So every list gets a page-size parameter with a documented default and maximum:

• Google’s AIP-158 makes page_size optional. Zero or missing means a documented default, values above the maximum are coerced down rather than rejected, and negative values are an INVALID_ARGUMENT error. Its example uses a default of 50 and a maximum of 1,000.
• GitHub’s REST API returns 30 issues by default and caps per_page at 100 for most endpoints, silently reducing larger values.
• Stripe accepts limit from 1 to 100, default 10.
• Slack recommends 100–200 per call, with a maximum of 1,000 that may vary by method.

One more contract rule matters as much as the limit: the server may return fewer items than asked for, even when more exist. AIP-158 allows short and even empty pages, and Slack tells clients not to compare the count with the limit. A page comes back short when a permission filter drops rows or the server hits a time budget. The only end-of-list signal is the one you define explicitly: an empty next_page_token, has_more: false, or no rel="next" link.

Offset and page-number pagination

The oldest scheme exposes positions directly:

GET /v1/orders?page=3&per_page=20      # page-number style
GET /v1/orders?offset=40&limit=20      # offset style: the same page

The two are one idea: offset = (page − 1) × per_page = 2 × 20 = 40. The server runs ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 40 and often returns a total so the UI can draw page links:

{ "data": ["...20 orders..."], "page": 3, "per_page": 20, "total": 295, "pages": 15 }

What you gain is real: users can jump to any page, see “page 3 of 15” and share a URL to it. Any sort order works, and it takes an afternoon to build. For an admin table of 300 coupons, nothing beats it.

What you pay shows up as the list grows and changes: deep pages get slower (next section), and the window drifts when rows are added or removed between requests.

Drift: an insert repeats items, a delete skips one

Take a feed sorted newest first, 10 per page. Page 1 returns items 100 to 91. Before the user scrolls, items 101 and 102 are posted. Page 2 asks for offset 10, which now skips 102, 101 and 100 down to 93, and returns 92 to 83: items 92 and 91 appear a second time. Slack’s engineers describe the extreme: 10 new messages arrive and page 2 is a copy of page 1.

Deletes are worse because nobody notices. Start again with page 1 = 100 to 91, but this time item 95 is deleted. Everything below it shifts up one place, so offset 10 skips 100 to 96 and 94 to 90 and returns 89 to 80. Item 90 is never shown.

Clients can filter duplicates by ID, but can’t detect skips at all: the strongest argument for cursors on any list that changes while people read it.

What the database pays for a deep page

The database half of this story, with query plans and index internals, lives in offset vs keyset pagination. Here is the part an API designer needs. PostgreSQL’s documentation puts it in one line: rows skipped by OFFSET “still have to be computed inside the server”. Even with a perfect index on (created_at, id), LIMIT 20 OFFSET 99980 walks 100,000 index entries, typically fetches the table row behind each one, and throws 99,980 of them away.

A worked example at 20 per page: page p reads (p − 1) × 20 + 20 = 20p rows. Page 1,000 reads 20,000 rows; page 5,000 reads 100,000 to return the same 20. A cursor query seeks straight to its starting point (a B-tree descent of three or four pages) and reads 21 rows. At page 5,000 that is 100,000 ÷ 21 ≈ 4,760 times less work. On top of that, the “page 3 of 15” total usually costs a COUNT(*) over every matching row, on every request.

Who asks for page 5,000? Rarely a person: exporters, scrapers and integrations walk whole collections, so they hit the deepest pages.

Cursor (keyset) pagination

A cursor API returns a bookmark with each page and accepts it on the next request. The client sees only an opaque string:

GET /v1/customers/42/orders?limit=20&cursor=eyJ2IjoyLCJrIjpb...Xq9w

200 OK
{
  "data": [ { "id": 9182, "created_at": "2026-10-27T08:14:03.120Z", "total_paise": 45900 } ],
  "has_more": true,
  "next_cursor": "eyJ2IjoyLCJrIjpbMTc5MzA4ODg0MzEyMCw5MTgyXSwi...Rk2c"
}

Inside, the bookmark holds the sort key of the last item returned plus a unique tiebreaker, and the server seeks past it:

-- First page: 20 rows plus one to learn whether more exist
SELECT id, created_at, total_paise
FROM orders
WHERE customer_id = :cid
ORDER BY created_at DESC, id DESC
LIMIT 21;

-- Next page: continue strictly after the last row returned
SELECT id, created_at, total_paise
FROM orders
WHERE customer_id = :cid
  AND (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 21;

Three details make it correct.

A unique tiebreaker. created_at is not unique: a flash sale creates dozens of orders in one millisecond. If a page ends mid-group, created_at < :ts skips the rest and <= repeats the first part. Appending the primary key makes the order total, so every row has exactly one position. The row comparison (a, b) < (x, y) means a < x OR (a = x AND b < y); if your sort mixes directions, such as price ASC, id DESC, write that expanded form by hand. Keep sort columns NOT NULL too: in SQL a comparison with NULL is neither true nor false, so those rows silently drop out of every page.

Fetch n + 1. Ask for 21 and return 20. If a 21st row exists, there is a next page and the cursor is built from row 20; if not, next_cursor is empty. The extra row costs about 5% more work and saves a second query or a COUNT. Slack’s variant uses the extra row’s own ID as the cursor and seeks with id <= cursor; either works if you are consistent.

An index that matches the sort. The seek is only cheap with an index whose columns match the filter and order, here (customer_id, created_at DESC, id DESC). Designing it is covered in composite index design, and checking that the planner uses it in query execution plans.

What you gain and what you pay

You gain flat cost, since page 5,000 costs the same as page 2, and stability: inserts and deletes elsewhere don’t move the bookmark, because it names a point in the sort order, not a count of rows. In Fig. 1, “after item 17” returns 16 to 13 however many items arrive above.

You pay three ways. There is no jumping to page N: to reach page 500 a client walks 499 pages. There is no free total: “1,203 results, page 7 of 61” needs a separate count. And the cursor is welded to its sort order: a cursor from a “newest first” list means nothing in “cheapest first”.

Opaque cursors and page tokens

The simplest cursor is a raw object ID, as in Stripe’s starting_after=cus_4QFJOjw2pOmAGJ. That works when the order is fixed (Stripe lists are always newest first) and IDs are public anyway. Once you support several sort orders, filters or storage back-ends, wrap the position in an opaque token that only the server can read.

Opacity is not decoration. AIP-158 says page tokens must not be user-parseable because “if users are able to deconstruct these, they will do so”, and your internal scheme becomes public API you can never change. Base64 alone is not obfuscation: Slack’s dXNlcjpXMDdRQ1JQQTQ= decodes to user:W07QCRPA4. But because clients treated it as opaque, Slack could encode offset:10 for older endpoints behind the same interface, and later pack one position per shard into a cursor.

A production token carries five things:

• A version, so you can change the format and still accept old tokens for a deprecation window, the same discipline as schema evolution.
• The position: sort-key values and tiebreaker of the boundary row, plus the direction.
• A query fingerprint: a hash of the filters and sort it was issued for. AIP-158 says other request parameters must match the call that produced the token (else INVALID_ARGUMENT), while a changed page_size is honoured.
• An expiry. Slack’s cursors expire and its docs say not to keep them for “hours or days”; for tokens stored server-side, AIP-158 suggests about three days as a rule of thumb.
• A signature, such as HMAC-SHA256 with a server-side key, so clients can’t forge a position; use authenticated encryption such as AES-GCM if the contents are sensitive.

A small, complete version in Python:

import base64, hashlib, hmac, json, time

class InvalidCursor(Exception): ...

def _b64(b: bytes) -> str:
    return base64.urlsafe_b64encode(b).rstrip(b"=").decode()

def _unb64(s: str) -> bytes:
    return base64.urlsafe_b64decode(s + "=" * (-len(s) % 4))

def encode_cursor(created_ms: int, row_id: int, query_hash: str, key: bytes) -> str:
    body = json.dumps({"v": 2, "k": [created_ms, row_id], "dir": "next",
                       "qh": query_hash, "exp": int(time.time()) + 86400}).encode()
    return _b64(body) + "." + _b64(hmac.new(key, body, hashlib.sha256).digest())

def decode_cursor(token: str, query_hash: str, key: bytes) -> list:
    try:
        body_part, sig_part = token.split(".")
        body, sig = _unb64(body_part), _unb64(sig_part)
    except ValueError:
        raise InvalidCursor("malformed")
    if not hmac.compare_digest(hmac.new(key, body, hashlib.sha256).digest(), sig):
        raise InvalidCursor("forged or corrupted")
    c = json.loads(body)
    if c["v"] not in (1, 2) or c["exp"] < time.time() or c["qh"] != query_hash:
        raise InvalidCursor("old format, expired, or different filters")
    return c["k"]  # [created_ms, id] for the seek query

This token is signed, not encrypted: anyone can read the body but nobody can change it. A token is a position, never a permission: AIP-158 says page tokens must not grant access, so authorise every page as if the token were absent. And every rejection is a clean client error, such as a 400 with a code like Slack’s invalid_cursor, telling the client to restart. Put a key ID in the token so you can rotate signing keys (see encryption and key management).

Stateless or stateful tokens

Everything above is a stateless token: the position travels inside it. A stateful token is a random ID pointing at state the server keeps, for example in Redis. It can hold things too big for a URL, such as 500 ranked post IDs, but costs memory, needs a TTL and breaks on eviction. Elasticsearch’s point-in-time IDs are stateful; Stripe’s object IDs are the stateless extreme.

How real APIs expose it

Five conventions cover most of what you will meet:

Link headers. GitHub puts navigation in the HTTP Link header defined by RFC 8288, so the body stays a plain array and clients follow URLs instead of building them:

Link: <https://api.github.com/repositories/1300192/issues?page=4>; rel="next",
      <https://api.github.com/repositories/1300192/issues?page=515>; rel="last"

The same header works for cursor URLs; it just can’t offer rel="last", because a cursor doesn’t know where the end is.

Relay connections. The GraphQL Cursor Connections specification wraps each item in an edge with its own cursor, so a client can resume from any item. pageInfo must report hasNextPage accurately when paging forward; the opposite flag may be false if expensive to compute. GitHub’s GraphQL API requires first or last between 1 and 100, part of the cost limits in REST vs GraphQL vs gRPC.

Choosing a scheme

My default for a public or app-facing list is an opaque token backed by keyset pagination, with a default limit of 20 and a maximum of 100. Offset survives where people want page links over a few thousand rows that rarely change. Time windows suit logs and events, but only with a tiebreaker: if 2,000 events share one second, a page of 100 keyed on the second alone can never get past it.

Hard cases

Paging backwards

A “previous” button needs a second cursor, built from the first row of the current page. Flip both the comparison and the sort, then reverse the rows before returning them so the page still reads newest first:

SELECT id, created_at, total_paise FROM orders
WHERE customer_id = :cid AND (created_at, id) > (:first_created_at, :first_id)
ORDER BY created_at ASC, id ASC
LIMIT 21;   -- then reverse the 20 rows in application code

The Relay specification states the same rule: edges come back in the same order whichever way you page, with the edge closest to before last. Stripe’s ending_before does this too. Return both cursors in each response (Relay calls them startCursor and endCursor).

Sorting by something that changes

“Sort by price” or “most liked” uses a key that changes while people page. A product whose price drops from above the cursor to below it is shown twice; one whose price rises across the cursor is never shown. Keyset pagination can’t fix this, because the row really did move. Your options: accept it for browsing UIs and dedupe by ID on the client, sort by something immutable (creation time, an ID), or freeze the order in a snapshot.

Ranked feeds: paginate a snapshot

A ranked home feed is the extreme case: scores depend on the viewer, the time and fresh engagement, so re-ranking for page 2 reshuffles everything. The usual answer, as in design a news feed, is to rank once and page through the frozen result:

1. The first request ranks a few thousand candidates and stores the top 500 post IDs under a feed-session key with a TTL of about 30 minutes.
2. The cursor is the session ID plus an index. Page 2 reads positions 20 to 39 from the stored list and fetches those 20 posts.
3. New posts don’t enter the frozen list; a “new posts” pill starts a fresh session.
4. When the session expires or runs out, the server ranks again and filters out IDs the user has already seen.

Index-based paging is safe here because the frozen list never changes, so offsets into it can’t drift. The cost is memory: 500 IDs × 8 bytes = 4 KB per session, so 10 million concurrent sessions need 10,000,000 × 4 KB ≈ 40 GB, a modest cache cluster.

Search results

Search engines rank by relevance, and deep pages are expensive. Elasticsearch pages with from and size, but a search spans shards and each shard must load from + size hits so they can be merged. Page 500 at 20 per page across 5 shards means each shard returns its top 10,000 hits, and 50,000 are merged to return 20. That is why from + size is capped by index.max_result_window, 10,000 by default. Past that, Elasticsearch recommends search_after: send the last hit’s sort values (keyset again), and open a point in time (PIT) so index refreshes between pages don’t reorder results. A PIT search adds an implicit _shard_doc tiebreaker; the older scroll API is no longer recommended for deep pagination.

Products often cap depth instead: GitHub’s search API returns at most 1,000 results per query, because people refine a search rather than read page 50. See Elasticsearch for the engine itself.

Sharded lists

When a list spans shards, such as “all orders on the platform, newest first” with orders sharded by customer, offset pagination has the Elasticsearch problem: each of S shards must return its top offset + limit rows.

Keyset pagination stays flat if the sort key is a global total order, such as a timestamp plus a globally unique ID (see design a unique ID generator). Send the same cursor to every shard, take limit + 1 rows from each, merge the sorted streams and return the top limit. That reads S × (limit + 1) rows per page at any depth. When the sources don’t share a key space, encode one position per source in the token, as Slack describes for Enterprise Grid, where a cursor could carry a last ID for the team shard and another for the organisation shard.

Exports and incremental sync

A partner syncing your orders hourly shouldn’t re-download everything. Give it a watermark on (updated_at, id), ascending: WHERE (updated_at, id) > (:wm_ts, :wm_id) ORDER BY updated_at, id LIMIT 1000. An updated row moves to the end, so the client sees its latest version on a later page.

Two traps remain. The first is late commits: if updated_at is stamped when a transaction starts and the transaction commits 3 seconds later, a client that has already read past that timestamp misses the row. Re-read an overlap window, say the last 5 minutes, and dedupe by ID and version, or page over a sequence assigned at commit time. The second is deletes, which no query can return; use soft deletes or tombstones that sync like updates. At scale, sync clients are better served by a change stream, via change data capture or webhooks, than by polling a list.

Going deeper: counts, abuse and short pages

Total counts

An exact COUNT(*) over 40 million matching rows reads 40 million rows or index entries to print one number, and offset UIs ask for it on every page view. Options, from cheapest to most exact:

• Don’t show it. Infinite scroll needs only has_more; Slack’s cursors have no concept of a total.
• Estimate it. Use the query planner’s row estimate or a periodically refreshed figure. AIP-158 allows total_size to be an estimate, as long as the API documents that.
• Cap it. Count at most 10,001 rows and show “10,000+”: SELECT count(*) FROM (SELECT 1 FROM orders WHERE ... LIMIT 10001) t. The cost is bounded at 10,001 rows.
• Maintain it. A per-customer counter updated with inserts and deletes: exact, but every write pays, and a hot counter becomes contention.
• Fetch it separately. A /orders/count endpoint that the UI calls lazily and caches for a minute.

Abuse and security

Pagination is how scrapers dump your data. Cap the page size and depth, as Elasticsearch (10,000) and GitHub search (1,000) do. Rate-limit list endpoints per client and consider charging deep or wide scans more. Sign cursors so they can’t be forged to probe positions, and keep sequential IDs out of readable cursors, because order 1,000,000 tells a competitor how many orders you take. Above all, authorise every page.

Cheat sheet

How to say it in the interview

“Every list endpoint takes a limit, default 20 and max 100, and returns data, has_more and a next_cursor. The cursor is opaque: a signed, versioned blob holding the last item’s created_at and ID plus a hash of the filters, with an expiry. On the server it becomes WHERE (created_at, id) < (:ts, :id) ORDER BY created_at DESC, id DESC LIMIT 21 on an index in that order, so every page costs the same and new posts don’t cause duplicates. I wouldn’t show a total on the feed; new items surface as a ‘new posts’ pill. Offset with page numbers I’d keep for small admin tables.”

What happens if new items are inserted while the user scrolls? With offset, two inserts at the top make page 2 repeat two items, and a delete makes one vanish unseen. With a cursor, the next page starts strictly after the last item seen, so nothing repeats or disappears. New items are above the user; a “new posts” pill fetches everything newer than the first item shown.

The product manager wants “page 7 of 120”. How? Totals and jumping need offsets or a count. For a small filtered list, a few thousand rows, offset is fine. For a large one, I’d show a capped or estimated total (“10,000+ results”), let users jump by something meaningful such as a date instead of a page number, and cap depth the way GitHub search stops at 1,000 results.

How do you paginate a ranked feed? Not by score, which changes between requests. I rank once per session, store the top few hundred IDs in Redis with a 30-minute TTL, and the cursor is the session ID plus an index into that frozen list: about 4 KB per session. When it runs out or expires, I rank again and filter out what the user has already seen.

Why not just put the last ID in the cursor as plain text? Clients will parse it and build their own, and then I can never change the sort or move to per-shard positions without breaking them. I’d sign it, bind it to the query, version it and expire it.

Is cursor pagination consistent? It guarantees that each row which exists throughout and keeps its sort key appears exactly once. It isn’t a snapshot: rows whose sort key changes can be skipped or repeated. For a true point-in-time read I’d page through a snapshot, such as a frozen ID list or an Elasticsearch point in time.

Where to go next

• Offset vs keyset pagination: the query plans and index mechanics behind this page.
• Composite index design: the index that makes a (created_at, id) seek cheap.
• API design for system design interviews: where pagination fits in the endpoint sketch.
• REST vs GraphQL vs gRPC: GraphQL connections and query cost limits.
• Design a news feed: ranked feeds, fan-out and feed snapshots.
• Elasticsearch: shards, scoring and deep-pagination limits.
• Change data capture: syncing from a change stream instead of polling a list.
• Rate limiting: protecting list endpoints from scrapers.

References and further reading

1. AIP-158: Pagination (Google API Improvement Proposals)google.aip.devpage_size defaults, maximums and coercion, opaque page tokens, matching request parameters, empty next_page_token as the only end signal, estimated totals and token expiry.
2. Evolving API Pagination at Slack (Slack Engineering)slack.engineeringWhy Slack moved from offsets to cursors: the drift example, the limit + 1 trick, Base64 cursors that hide offset or per-shard positions.
3. Slack platform docs: Paginationdocs.slack.devcursor, limit and response_metadata.next_cursor; recommended and maximum limits, short pages, cursor expiry and the invalid_cursor error.
4. Stripe API reference: Paginationdocs.stripe.comlimit from 1 to 100 (default 10), starting_after and ending_before object IDs, has_more, reverse chronological order.
5. GitHub Docs: Using pagination in the REST APIdocs.github.comLink headers with next, prev, first and last; page, before/after and since parameters; per_page maximum of 100, silently reduced.
6. GitHub Docs: REST API endpoints for searchdocs.github.comSearch returns up to 1,000 results per query: a deliberate cap on pagination depth.
7. RFC 8288: Web Linkingrfc-editor.orgDefines the Link HTTP header field and link relation types used for next and previous page links.
8. GraphQL Cursor Connections Specification (Relay)relay.devConnections, edges, node and cursor; first/after and last/before; PageInfo rules and identical edge order in both directions.
9. PostgreSQL documentation: LIMIT and OFFSETpostgresql.orgRows skipped by OFFSET are still computed, so large offsets are inefficient; LIMIT needs a unique ORDER BY to be predictable.
10. Elasticsearch documentation: Paginate search resultselastic.cofrom/size and the 10,000-hit max_result_window, search_after with a tiebreaker, point in time and the implicit _shard_doc tiebreaker.