How to Implement API Pagination: Offset vs Cursor vs Keyset Pagination in PostgreSQL and REST APIs Step by Step (2026)
On a 2-million-row PostgreSQL 16 table, fetching 50 rows at OFFSET 1000000 took about 170 ms on our test box, while fetching the same 50 rows with a keyset predicate took about 0.06 ms. That gap is not a tuning problem. It is the cost model of API pagination done with offsets, and it grows linearly with every page a client scrolls. It matters now because agents, sync jobs and data exporters walk entire collections far more often than humans ever clicked “page 47”. Offsets also quietly skip or duplicate rows whenever someone inserts while a client is paging. This guide gives you a reproducible SQL benchmark, the exact row-value query that PostgreSQL turns into a single index seek, a signed and versioned cursor format, a FastAPI endpoint, backward paging, and a client loop that survives retries. It also shows how Stripe, GitHub, Slack, Google AIP-158, Relay and JSON:API actually shape their cursors.
What this covers: offset vs cursor pagination mechanics, a measured PostgreSQL repro, step-by-step keyset implementation, cursor design, real API conventions, counts, nulls, sharded stores, a decision table and a checklist.
Context and Background
Every list endpoint eventually needs API pagination. Google’s AIP-158 says list RPCs must paginate from the start, because adding it later is a backwards-incompatible change for clients that assumed they received everything.
There are three families of technique, and the vocabulary is messy. Offset pagination asks the database to skip k rows and return the next n (LIMIT n OFFSET k), usually exposed as ?page=7&per_page=50. Keyset pagination (sometimes called the “seek method”) remembers the sort key of the last row returned and asks for rows strictly after it. Cursor pagination is an API-contract term: the server hands the client an opaque token, and the client hands it back to get the next page. A cursor usually encodes a keyset position, but it can just as easily wrap an offset, a search-engine sort value, or a Kafka partition offset.
That distinction is the thesis of this post. The wire contract (cursor) and the storage access path (keyset) are separate decisions. Teams that conflate them ship “cursor pagination” that base64-encodes offset=1000000 and inherits every offset problem; teams that separate them can change storage later without breaking clients.
The PostgreSQL LIMIT and OFFSET chapter states that “the rows skipped by an OFFSET clause still have to be computed inside the server; therefore a large OFFSET might be inefficient.” It also warns that without an ORDER BY that constrains rows into a unique order, successive LIMIT/OFFSET queries return “an unpredictable subset” of rows.
If you are still choosing protocols, our comparison of gRPC, REST, GraphQL and Connect APIs covers how each one models collections. And because keyset pagination lives or dies on index layout, the companion piece on B-tree and B+tree index internals explains why a composite index lets PostgreSQL jump straight to a position instead of walking to it.
How API Pagination Works: Offset vs Cursor vs Keyset Compared
API pagination splits a large result set into bounded pages. Offset pagination skips k rows, so cost grows with depth and results drift under writes. Keyset pagination filters on the last row’s unique sort key, so each page is an index seek with constant cost. Cursor pagination wraps that position in an opaque, signed token the client returns unchanged.

Figure 1: Two storage paths behind one API pagination contract. The offset path reads k plus n index entries; the keyset path seeks directly to the cursor position and reads n plus 1.
Figure 1 shows one list handler with two possible storage paths; the client never sees which one ran. It sends a limit and, after the first page, an opaque cursor. The database returns one extra row so the server knows whether another page exists, and the response builder signs the last row’s key into the next cursor, also surfaced as an RFC 8288 Link header.
Offset pagination: simple contract, linear cost, unstable results
Offset pagination is the default in most ORMs because it maps neatly onto page numbers. The problem is mechanical. PostgreSQL has no way to “jump to row one million” in an ordered index. A B-tree is navigable by key value, not by ordinal position. To honor OFFSET 1000000 LIMIT 50 the executor descends to the first leaf, then walks one million and fifty index entries, fetches the corresponding heap tuples when the index does not cover the query, and throws away all but the last fifty.
The second problem is correctness. An offset is a position relative to the current state of the table. If a new row is inserted ahead of the client’s position between page 1 and page 2, every subsequent row shifts down by one, and the client sees the last row of page 1 again at the top of page 2. A delete does the opposite and a row is skipped silently. We reproduce both effects in Step 5 below.
Offset pagination still suits “jump to page 37” UIs on small, slowly changing collections. The mistake is using it for unbounded feeds, exports or sync.
Keyset pagination: a seek, not a scan
Keyset pagination replaces “skip k rows” with “give me rows whose sort key is greater than the last one I saw”. If the sort is ORDER BY created_at, id, the next-page predicate is WHERE (created_at, id) > ($last_created_at, $last_id). With a matching composite index on (created_at, id), PostgreSQL descends the B-tree directly to that key and reads the next n entries. The cost depends on tree height and page size, not on how deep the client has scrolled.
The id column in that tuple is not decoration. created_at alone is not unique; two rows can share a timestamp, and a predicate of created_at > $last would skip the second one. Appending a unique column as a tiebreaker makes the ordering total, which is exactly what the PostgreSQL manual asks for when it says the ORDER BY must constrain rows “into a unique order”.
Keyset pagination is also stable under concurrent writes. A row inserted behind the client’s position never appears; a row inserted ahead appears in its correct place. Nothing shifts, because the position is a value, not a count.
Cursor pagination: the contract that hides the mechanism
A cursor is the serialized position plus enough context to validate it. A well-designed cursor carries a version number, the sort key of the boundary row, the direction, and a fingerprint of the filters and sort that produced it. It is signed so clients cannot forge positions, and treated as opaque so you can change its internals later. Google AIP-158 goes further: page tokens must be opaque and URL-safe, must not be user-parseable, and base64 encoding of a readable token “is not a sufficient obfuscation mechanism”.
Here is how the three compare on the dimensions that matter in production.
| Dimension | Offset (LIMIT/OFFSET) |
Keyset (seek predicate) | Cursor (opaque token) |
|---|---|---|---|
| What the client sends | page number or offset | last sort key, often visible | signed token |
| Cost of page N | grows with N | roughly constant | that of the underlying strategy |
| Stable under inserts/deletes | No: skips or duplicates | Yes | Yes if it wraps a keyset |
| Jump to arbitrary page | Yes | No | No |
| Needs unique, indexed sort | Should have one | Must have one | Must, if keyset-backed |
| Total count | often paired with COUNT(*) |
optional | optional, often estimated |
| Typical users | admin UIs, small tables | internal services, SQL | Stripe, Slack, Google APIs, Relay |
The key insight: “cursor” is not a third algorithm competing with the other two. It is the API pagination surface you put in front of keyset queries, or occasionally in front of a search engine’s search_after values.
Step-by-Step: Implementing Keyset Pagination in PostgreSQL and a REST API
The steps below build a working keyset-backed API pagination endpoint from scratch. Each step is independently testable. All measurements in this section were taken by us on PostgreSQL 16.15 (Ubuntu build) on a 2-vCPU, 7 GB RAM Linux VM with the default shared_buffers = 128MB. Treat the absolute numbers as specific to that box; the ratios and the buffer counts are the transferable part.
- Create a reproducible dataset and the indexes your sort order needs.
- Measure offset cost with
EXPLAIN (ANALYZE, BUFFERS)at increasing depth. - Rewrite the page query as a row-value keyset predicate and confirm an index seek.
- Add filters with a composite index whose leading columns match equality filters.
- Prove the drift problem and the keyset fix with a concurrent insert.
- Design an opaque cursor: versioned, signed, bound to the query.
- Build the endpoint with the limit-plus-one trick.
- Add backward pagination.
- Expose
nextandprevthrough the body and an RFC 8288Linkheader. - Write a client loop that iterates safely and resumes after failure.
Step 1: Build a reproducible dataset
You cannot reason about pagination cost from a 200-row dev table. The script below uses generate_series to create two million events spread across 50 tenants, one per second, with an identity primary key.
CREATE TABLE events (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
tenant_id int NOT NULL,
created_at timestamptz NOT NULL,
payload text NOT NULL
);
INSERT INTO events (tenant_id, created_at, payload)
SELECT (g % 50) + 1,
timestamptz '2026-01-01' + g * interval '1 second',
md5(g::text)
FROM generate_series(1, 2000000) AS g;
-- Global feed order
CREATE INDEX events_created_id_idx
ON events (created_at, id);
-- Per-tenant feed order: equality column first, then the sort key
CREATE INDEX events_tenant_created_id_idx
ON events (tenant_id, created_at, id);
VACUUM ANALYZE events;
On our box this loaded in under eight seconds. The heap came to 178 MB and the (created_at, id) index to 60 MB, so the deepest offset pages exceed the 128 MB buffer cache.
Step 2: Measure what OFFSET really costs
Run the same page query at increasing offsets with EXPLAIN (ANALYZE, BUFFERS). The BUFFERS option reports how many 8 KB pages the query touched, which is a better cross-machine signal than milliseconds. If you need a refresher on reading plans, see our guide to finding and fixing slow PostgreSQL queries with EXPLAIN ANALYZE and pg_stat_statements.
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id, created_at, payload
FROM events
ORDER BY created_at, id
LIMIT 50 OFFSET 1000000;
The plan is an Index Scan using events_created_id_idx, but the inner node reports rows=1000050. The executor read every one of those rows to discard a million of them. We ran each query three times after a warm-up and report the typical warm value of the Limit node’s actual time.
| Page position | Offset query time | Offset buffers touched | Rows read by index scan | Keyset query time | Keyset buffers |
|---|---|---|---|---|---|
| Row 0 | ~0.02 ms | 4 | 50 | ~0.02 ms | 4 |
| Row 10,000 | ~1.6 ms | 156 | 10,050 | ~0.06 ms | 5 |
| Row 100,000 | ~15 ms | 1,523 | 100,050 | ~0.06 ms | 4 |
| Row 1,000,000 | ~160 to 174 ms | 15,199 | 1,000,050 | ~0.06 ms | 5 |
| Row 1,950,000 | ~360 to 400 ms | 29,634 | 1,950,050 | ~0.05 ms | 4 |
Measured on PostgreSQL 16.15, 2 vCPU, 7 GB RAM, shared_buffers 128 MB, warm OS cache, three runs each. Your absolute numbers will differ; the linear growth will not.
The buffer column tells the story. Offset cost scales almost perfectly linearly with depth: roughly 15 buffers per thousand rows skipped on this table. Keyset cost is flat at four or five buffers, which is simply the B-tree height plus the leaf and heap pages holding the 50 rows. At the deepest position the offset query touched about 7,400 times more pages than the keyset query.

Figure 2: Where the work goes. OFFSET walks and fetches every skipped entry before discarding it; the keyset predicate descends the B-tree once to the cursor key and reads only the page it returns.
In Figure 2, three of the four offset boxes are wasted work proportional to the offset; on the keyset side the only variable is page size. Cold runs are worse still: our first OFFSET 1950000 execution reported over two seconds of execution time because it pulled about 20,000 pages from disk and wrote dirty buffers along the way.
Step 3: Write the keyset predicate with a row-value comparison
The fastest correct keyset predicate in PostgreSQL uses a row constructor comparison. The PostgreSQL manual’s section on row constructor comparison specifies that for <, <=, > and >=, “the row elements are compared left-to-right, stopping as soon as an unequal or null pair of elements is found.” That is exactly lexicographic order, which is exactly B-tree order.
-- Next page after the last row the client saw
SELECT id, created_at, payload
FROM events
WHERE (created_at, id) > ('2026-01-12 13:46:40+00', 1000000)
ORDER BY created_at, id
LIMIT 51; -- page size + 1, see Step 7
The plan shows Index Cond: (ROW(created_at, id) > ROW(...)) on events_created_id_idx. PostgreSQL uses the whole tuple as the index start key, so it descends straight to the boundary. Execution took 0.05 to 0.08 ms in our runs regardless of depth.
Many ORMs and older tutorials generate the expanded boolean form instead.
WHERE created_at > $1
OR (created_at = $1 AND id > $2)
This is logically equivalent, but with a cursor near row one million our PostgreSQL 16 planner did not turn it into an index condition. It used the right index for ordering, applied the expression as a Filter, reported Rows Removed by Filter: 998000 and took about 129 ms, quietly reintroducing offset-like cost. If your query builder cannot emit row-value comparisons, add a redundant sargable bound such as AND created_at >= $1 so the planner has an index condition to seek on. Then confirm with EXPLAIN.
There is one constraint on the row-value form: every column must sort in the same direction. (created_at, id) > (...) works for ORDER BY created_at ASC, id ASC and < works for both descending. A mixed sort such as ORDER BY score DESC, id ASC cannot be expressed as a single tuple comparison. You either flip one column (for numeric keys, sort on -score), use an expression index, or fall back to the expanded OR form with an explicit leading bound.
Step 4: Add filters and match the index to the query
Real list endpoints filter: ?tenant=7, ?status=open. The rule for the index is equality columns first, then the sort columns in sort order. That is why Step 1 created (tenant_id, created_at, id).
SELECT id, created_at
FROM events
WHERE tenant_id = 7
AND (created_at, id) > ('2026-01-12 13:13:20+00', 1000000)
ORDER BY created_at, id
LIMIT 51;
The plan was an Index Only Scan with Index Cond: ((tenant_id = 7) AND (ROW(created_at, id) > ROW(...))) and ran in about 0.06 ms. The equivalent offset query at a depth of 20,000 tenant rows took about 2.9 ms and touched 103 buffers. That gap widens with depth exactly as in Step 2.
Range filters on a column that is not the sort key, say amount > 100, cannot be part of the seek. They become post-filters, and a selective post-filter can force the scan to read many index entries to fill one page. If a filter is common and selective, give it its own index with the sort columns appended. PostgreSQL 18, released on 2025-09-25, added B-tree skip scan, which lets multicolumn indexes be used when early columns have no equality restriction. It helps some of these cases but does not replace an index designed for the query.
Step 5: Prove the drift problem and the fix
This tiny script shows offset drift deterministically.
CREATE TABLE feed (id int PRIMARY KEY, created_at int NOT NULL);
INSERT INTO feed SELECT g, g FROM generate_series(1, 10) g;
-- Page 1, newest first
SELECT id FROM feed ORDER BY created_at DESC, id DESC LIMIT 5;
-- returns 10,9,8,7,6
INSERT INTO feed VALUES (11, 11); -- a new row arrives
-- Page 2 via OFFSET
SELECT id FROM feed ORDER BY created_at DESC, id DESC LIMIT 5 OFFSET 5;
-- returns 6,5,4,3,2 (6 is duplicated)
-- Page 2 via keyset, after the last row of page 1 (6, 6)
SELECT id FROM feed
WHERE (created_at, id) < (6, 6)
ORDER BY created_at DESC, id DESC LIMIT 5;
-- returns 5,4,3,2,1 (correct)
We ran this on PostgreSQL 16.15 and got exactly these outputs. A delete instead of an insert makes the offset version skip a row silently. In a UI that is a duplicated card; in a sync job it can be silent data loss.
Keyset pagination is not a transaction snapshot. Each page is a separate query, so a row whose sort key is updated across the cursor can be seen twice or missed, which is why mutable columns make risky sort keys.
Step 6: Design an opaque, signed, versioned cursor
The cursor is your public contract. A cursor that is just base64({"id":1000000}) invites three problems: clients parse it and depend on its shape, clients edit it to probe other tenants’ rows, and you cannot change the sort or the encoding without breaking everyone holding a token.
A durable cursor payload has five fields:
v: a format version, so a future change can be detected and rejected or migrated.k: the sort-key values of the boundary row, in sort order, serialized losslessly.d: direction,nextorprev.q: a short hash of the filters and sort that produced it, so a token cannot be replayed against a different query. AIP-158 recommendsINVALID_ARGUMENTwhen parameters change between pages.exp: an expiry timestamp. Slack’s cursors expire; AIP-158 suggests about three days as a rule of thumb.
Then sign it. An HMAC prevents tampering but does not hide the contents, and AIP-158 says tokens must not be user-parseable. To follow that rule strictly, encrypt with an authenticated cipher such as AES-GCM; the Python below signs, which is the minimum.
# cursor.py - opaque, signed, versioned keyset cursors
import base64, hashlib, hmac, json, time
from datetime import datetime
SECRET = b"rotate-me-from-a-secret-manager" # load from your KMS / env
CURSOR_VERSION = 1
CURSOR_TTL_S = 3 * 24 * 3600 # AIP-158 rule of thumb
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 query_fingerprint(filters: dict, sort: str) -> str:
canon = json.dumps({"f": filters, "s": sort}, sort_keys=True, separators=(",", ":"))
return hashlib.sha256(canon.encode()).hexdigest()[:16]
def encode_cursor(created_at: datetime, row_id: int, direction: str, qhash: str) -> str:
payload = {
"v": CURSOR_VERSION,
"k": [created_at.isoformat(), row_id], # isoformat keeps microseconds
"d": direction,
"q": qhash,
"exp": int(time.time()) + CURSOR_TTL_S,
}
body = json.dumps(payload, separators=(",", ":")).encode()
sig = hmac.new(SECRET, body, hashlib.sha256).digest()[:16]
return _b64(body) + "." + _b64(sig)
class InvalidCursor(ValueError):
pass
def decode_cursor(token: str, expected_qhash: str) -> dict:
try:
body_b64, sig_b64 = token.split(".", 1)
body = _unb64(body_b64)
good = hmac.new(SECRET, body, hashlib.sha256).digest()[:16]
if not hmac.compare_digest(good, _unb64(sig_b64)):
raise InvalidCursor("bad signature")
p = json.loads(body)
p["k"][0] = datetime.fromisoformat(p["k"][0])
except InvalidCursor:
raise
except Exception as exc:
raise InvalidCursor("malformed cursor") from exc
if p.get("v") != CURSOR_VERSION:
raise InvalidCursor("unsupported cursor version")
if p.get("q") != expected_qhash:
raise InvalidCursor("cursor does not match this query")
if p.get("exp", 0) < time.time():
raise InvalidCursor("cursor expired")
return p
Two details in that code prevent real bugs. First, isoformat() preserves PostgreSQL’s microsecond timestamptz precision. If a cursor ever passes through a JavaScript Date, which only has millisecond precision, the boundary value is truncated and rows sharing that millisecond are skipped or repeated. Second, hmac.compare_digest does a constant-time comparison so the signature check cannot be probed by timing.
A cursor must never grant authorization. AIP-158 states it directly: authorization must be checked on every request, with or without a token. The tenant filter in the next step comes from the authenticated principal, never from the cursor.
Step 7: Build the endpoint with the limit-plus-one trick
Fetch limit + 1 rows; if the extra row arrives, there is a next page and you drop it from the response. This is cheaper and more honest than a COUNT(*).
# app.py - FastAPI + psycopg 3 keyset endpoint
from fastapi import FastAPI, Depends, HTTPException, Query, Response
from psycopg_pool import ConnectionPool
from cursor import encode_cursor, decode_cursor, query_fingerprint, InvalidCursor
app = FastAPI()
pool = ConnectionPool("postgresql://app@localhost/appdb", min_size=1, max_size=10)
DEFAULT_LIMIT, MAX_LIMIT = 50, 200
SORT = "created_at,id"
def current_tenant() -> int: # replace with your auth dependency
return 7
@app.get("/v1/events")
def list_events(
response: Response,
limit: int = Query(DEFAULT_LIMIT, ge=1),
cursor: str | None = None,
tenant_id: int = Depends(current_tenant),
):
limit = min(limit, MAX_LIMIT) # clamp, do not error
qhash = query_fingerprint({"tenant": tenant_id}, SORT)
params = {"tenant": tenant_id, "n": limit + 1}
if cursor:
try:
c = decode_cursor(cursor, qhash)
except InvalidCursor as e:
raise HTTPException(status_code=400, detail=str(e))
if c["d"] != "next":
return list_events_backward(response, limit, c, tenant_id, qhash)
params.update(ts=c["k"][0], id=c["k"][1])
where = "tenant_id = %(tenant)s AND (created_at, id) > (%(ts)s, %(id)s)"
else:
where = "tenant_id = %(tenant)s"
sql = f"""SELECT id, created_at, payload FROM events
WHERE {where} ORDER BY created_at, id LIMIT %(n)s"""
with pool.connection() as conn:
rows = conn.execute(sql, params).fetchall()
has_more = len(rows) > limit
rows = rows[:limit]
next_cursor = (
encode_cursor(rows[-1][1], rows[-1][0], "next", qhash) if has_more else None
)
prev_cursor = (
encode_cursor(rows[0][1], rows[0][0], "prev", qhash) if cursor and rows else None
)
set_link_header(response, "/v1/events", limit, next_cursor, prev_cursor)
return {
"data": [{"id": r[0], "created_at": r[1].isoformat(), "payload": r[2]} for r in rows],
"has_more": has_more,
"next_cursor": next_cursor,
"prev_cursor": prev_cursor,
}
An oversized limit is clamped, matching AIP-158’s guidance to coerce page sizes above the maximum, while invalid values are rejected (ge=1 yields a 422). JSON:API’s cursor profile instead returns a 400 “max page size exceeded” error. Either is defensible if documented. The tenant comes from the auth dependency, and qhash binds the cursor to it.

Figure 3: The cursor pagination round trip. The server over-fetches by one row, signs the boundary key, and on the next call verifies signature, version and query fingerprint before seeking.
The database never sees the token and the client never sees the SQL, so you can later change stores, encoding or sort columns by bumping only the cursor version.
Step 8: Add backward pagination
Backward paging reverses the comparison and the sort, then flips the result back into canonical order. The Relay connection specification works the same way: last/before select edges from the end, but response order is unchanged.
def list_events_backward(response, limit, c, tenant_id, qhash):
sql = """SELECT * FROM (
SELECT id, created_at, payload FROM events
WHERE tenant_id = %(tenant)s AND (created_at, id) < (%(ts)s, %(id)s)
ORDER BY created_at DESC, id DESC
LIMIT %(n)s
) s ORDER BY created_at, id"""
params = {"tenant": tenant_id, "ts": c["k"][0], "id": c["k"][1], "n": limit + 1}
with pool.connection() as conn:
rows = conn.execute(sql, params).fetchall()
has_prev = len(rows) > limit
rows = rows[-limit:] # drop the extra row from the front
prev_cursor = encode_cursor(rows[0][1], rows[0][0], "prev", qhash) if has_prev else None
next_cursor = encode_cursor(rows[-1][1], rows[-1][0], "next", qhash) if rows else None
set_link_header(response, "/v1/events", limit, next_cursor, prev_cursor)
return {
"data": [{"id": r[0], "created_at": r[1].isoformat(), "payload": r[2]} for r in rows],
"has_more": next_cursor is not None, # we came from a later page, so more exists after
"next_cursor": next_cursor,
"prev_cursor": prev_cursor,
}
PostgreSQL handles this efficiently because a B-tree can be read in either direction. On our test table the inner query ran as an Index Only Scan Backward with a row-value Index Cond in about 0.07 ms, followed by an in-memory quicksort of 50 rows using 26 kB.
Step 9: Expose links in the body and the Link header
Return cursors in the body, which most client code reads, and also emit a Link header. RFC 8288 “Web Linking”, published in October 2017 by Mark Nottingham, obsoletes RFC 5988 and defines that header and relation types such as next and prev. GitHub’s REST API is the best-known user, with prev, next, first and last relations.
from urllib.parse import urlencode
def set_link_header(response, path, limit, next_cursor, prev_cursor):
links = []
if next_cursor:
links.append(f'<{path}?{urlencode({"limit": limit, "cursor": next_cursor})}>; rel="next"')
if prev_cursor:
links.append(f'<{path}?{urlencode({"limit": limit, "cursor": prev_cursor})}>; rel="prev"')
if links:
response.headers["Link"] = ", ".join(links)
Skip last unless it is cheap; GitHub omits it when it cannot be calculated. Always URL-encode cursors: Slack notes its cursors typically end in =, which must become %3D.
Step 10: Write a client loop that is safe to resume
A good client treats the cursor as opaque, persists the last one so a crash can resume, and respects rate limits; our guide to handling API rate limits with retries, backoff and jitter covers that layer in depth.
import time, httpx
def iterate_events(base_url: str, token: str, limit: int = 200, start_cursor: str | None = None):
cursor = start_cursor
with httpx.Client(base_url=base_url, headers={"Authorization": f"Bearer {token}"}) as http:
while True:
params = {"limit": limit} | ({"cursor": cursor} if cursor else {})
for attempt in range(5):
r = http.get("/v1/events", params=params, timeout=30)
if r.status_code in (429, 503):
time.sleep(min(2 ** attempt, 30))
continue
r.raise_for_status()
break
page = r.json()
for item in page["data"]:
yield item
cursor = page.get("next_cursor")
save_checkpoint(cursor) # persist for resume
if not cursor: # empty/None means end of collection
return
The termination test is “no next cursor”, not “the page was short”. AIP-158 permits a server to return fewer results than requested, even zero, while more remain, and says an empty next_page_token is the only way to signal the end. Slack’s docs likewise treat an empty, null or missing next_cursor as the end.
REST API Pagination Best Practices From Real Cursor Designs
Public APIs have converged on cursor pagination but differ in how much position they expose and how they signal the end. Each row was checked against current provider documentation.
| API or spec | Request parameters | End-of-list signal | Page size rules | Notable design choice |
|---|---|---|---|---|
| Stripe v1 list endpoints | limit, starting_after, ending_before (object IDs) |
has_more: false |
default 10, range 1 to 100 | cursor is a plain object ID; results newest first |
| GitHub REST | page, per_page, or before/after/since depending on endpoint |
no next relation in Link header |
per_page usually capped at 100; excess silently reduced |
clients should follow Link URLs rather than build them |
| Slack Web API | cursor, limit |
response_metadata.next_cursor empty |
recommends 100 to 200; max 1,000, may vary by method | cursors expire; encode = as %3D |
| Google AIP-158 | page_size, page_token |
empty next_page_token |
unset or 0 uses default; above max is coerced; negative is an error | tokens opaque, not user-parseable, may expire |
| Relay GraphQL connections | first/after, last/before |
pageInfo.hasNextPage false |
negative first/last is an error |
edges carry per-item cursors; hasPreviousPage may be false when paging forward |
| JSON:API cursor profile | page[size], page[after], page[before] |
links.next null |
exceeding max returns a 400 error | optional total or estimatedTotal in meta.page |
What Stripe teaches: the ID is the cursor
Stripe’s v1 list methods accept an object ID in starting_after or ending_before (mutually exclusive) and return a list object with data, has_more and url, newest first. This is keyset pagination with a deliberately transparent cursor, which works because each list has one canonical sort. Stripe’s search endpoints instead use a page parameter fed from a next_page value: a similar contract over a different mechanism.
The lesson: a transparent cursor is fine when the sort is fixed and the key already public. It becomes a liability once users can choose sorts, because an ID alone no longer locates a position in, say, an amount-sorted list.
What GitHub, Slack and Google teach: links, opacity and query binding
GitHub recommends following the URLs in the link header rather than computing page + 1, which lets each endpoint mix page, before/after and since without clients noticing. That is the core value of RFC 8288 for API pagination: it moves pagination state out of client logic. Slack’s cursors expire and are “meant to be used within a reasonable amount of time”. AIP-158 turns these ideas into rules: opaque tokens, optional expiry, INVALID_ARGUMENT when other parameters change between pages, and an optional skip field for clients that genuinely need to jump.
Totals, Sorting, Nulls and Pagination Beyond a Single Database
Production API pagination also needs answers for total counts, nullable or user-chosen sort columns, and stores that are not a single PostgreSQL table.
The cost of “total: 48,213”
A page counter usually means a SELECT COUNT(*) per request. On our 2-million-row table that query ran as a parallel sequential scan and took about 185 ms, comparable to an OFFSET 1000000 page and thousands of times slower than a keyset page. Under MVCC, PostgreSQL has no stored exact row count to read; it must visit rows to check visibility.
The planner’s estimate lives in pg_class.reltuples, which the PostgreSQL catalog documentation describes as “only an estimate used by the planner”, updated by VACUUM, ANALYZE and some DDL, and set to -1 if the table has never been vacuumed or analyzed. On our table it returned exactly 2,000,000 right after VACUUM ANALYZE, in about a millisecond.
SELECT reltuples::bigint AS estimated_rows
FROM pg_class
WHERE oid = 'public.events'::regclass;
For filtered counts, the PostgreSQL wiki’s count-estimate page shows a function that returns the planner’s EXPLAIN (FORMAT JSON) row estimate, and warns that passing it unsanitized SQL is an injection risk. AIP-158 and JSON:API both explicitly allow an estimated total; JSON:API names the field estimatedTotal. Many products simply drop the total and show “more results”.
Nullable and user-selected sort columns
Row-value comparisons and NULL do not mix. The PostgreSQL manual says the comparison stops at the first unequal or null pair, and if either element of that pair is null the result is null. A cursor whose due_date is NULL therefore matches no rows, and the client sees a premature end of list. PostgreSQL also sorts NULLs as larger than any non-null value by default, so they sit at the end of ascending sorts and the start of descending ones.
Three fixes work. Declare the column NOT NULL with a sentinel default where the domain allows it. Or sort and index on an expression such as COALESCE(due_date, 'infinity') and use the same expression in the predicate. Or split the walk into two phases, non-null values first and then due_date IS NULL ordered by id, with the phase recorded in the cursor.
User-selected sorts multiply indexes, since each needs a composite index ending in a unique tiebreaker. Offer a short allow-list, bind the chosen sort into the cursor fingerprint, and reject replays under a different sort.
Sharded stores, search engines and logs
Keyset pagination generalizes beyond one table. On a sharded SQL system, each shard runs the same keyset query with LIMIT n + 1 and a coordinator merges streams by sort key. The cursor holds one global boundary key that every shard seeks to independently. Our comparison of Vitess, Citus and CockroachDB sharding architectures covers those coordinator architectures.
Search engines hit the offset wall even harder, because every shard must produce from + size candidates for the coordinator to merge. Elasticsearch’s pagination documentation states that by default you cannot use from and size to page past 10,000 hits, enforced by index.max_result_window. Its recommendation for deep paging is search_after with a point in time (PIT), extending the PIT with keep_alive and deleting it when done. The sort needs a unique tiebreaker or results can skip or repeat; PIT searches add _shard_doc automatically. That design is keyset pagination plus a snapshot, the exact combination a SQL keyset lacks. Elastic no longer recommends the scroll API for deep pagination.
Logs and streams are the case where offsets are correct. A Kafka partition is an append-only log whose records have sequential integer offsets, and Confluent’s consumer design documentation notes that because an offset is an integer, consumer state is small; committed positions are stored per group and partition in an internal topic. Records never move in an immutable partition, so a numeric position never drifts. If your API fronts an event log, exposing (partition, offset) pairs inside an opaque cursor is both efficient and stable.
Snapshot consistency, when you really need it
Keyset pagination is stable but not a point-in-time view. If an export must reflect one moment, add a high-water mark captured on the first page, such as created_at <= $snapshot_time, which works for append-only data. Alternatives are a short-lived REPEATABLE READ transaction for internal jobs, since long transactions hold back vacuum, or materializing the result set first. Elasticsearch’s PIT is a managed snapshot with an explicit keep_alive.
Offset vs Cursor Pagination: A Decision Table
The table maps common API pagination situations to a default; Figure 4 shows the same logic as a decision flow.
| Situation | Recommended approach | Why |
|---|---|---|
| Admin table under ~10k rows, users jump to page N | Offset with a unique ORDER BY |
jump-to-page matters; cost is negligible at this size (rule of thumb, not a hard limit) |
| Public REST list endpoint | Keyset behind an opaque signed cursor | constant cost, stable under writes, evolvable contract |
| Infinite-scroll feed | Keyset, forward only | no page numbers needed; inserts must not duplicate cards |
| Bulk export or sync job | Keyset plus high-water mark or snapshot | completeness matters more than latency |
| Full-text search results | Engine-native search_after plus PIT |
from/size is capped and expensive on shards |
| Event log or stream | Partition offsets inside a cursor | the log is immutable, so offsets do not drift |
| GraphQL API | Relay connections over keyset | clients and tooling already expect edges and pageInfo |

Figure 4: Choosing a pagination strategy. Arbitrary page jumps on small collections are the main case for offsets; everything else converges on a keyset-style position wrapped in an opaque cursor.
For the hybrid branch, jump by value rather than by count, for example “jump to March 2026”, which is itself a keyset seek.
Trade-offs, Gotchas, and What Goes Wrong
Keyset-based API pagination fixes the big problems but trades them for smaller, sharper ones.
Non-unique sort keys. Sorting by created_at alone with a keyset predicate skips rows that share the boundary timestamp. Bulk inserts in one transaction often share now(), because now() returns the transaction start time. Always append a unique tiebreaker such as the primary key.
Mutable sort keys. Sorting by updated_at or score means a row can move across the cursor while a client pages, appearing twice or never. For sync use cases, prefer an immutable, monotonic key, or accept duplicates and make consumers idempotent. Our guide to idempotent API design covers the consumer side.
The expanded OR predicate. As measured in Step 3, a > x OR (a = x AND b > y) can be applied as a filter rather than an index condition. Always check the plan for Index Cond, not just the index name.
Precision loss in cursors. Timestamps truncated to milliseconds by JavaScript, or floats rounded during JSON encoding, break the equality half of the comparison. Serialize keys losslessly: ISO strings with microseconds, integers as strings if they exceed 2^53, and decimals as strings.
Transparent cursors becoming API. If clients can read your cursor, some will build on it and freeze your design. Sign, preferably encrypt, and version from day one.
Forgotten authorization. A cursor that encodes a tenant ID and is trusted on return is a cross-tenant data leak. Derive scope from the authenticated principal and bind the cursor to it.
Stopping on short pages. Clients that treat len(data) < limit as the end of the collection silently truncate when the server post-filters or applies a time budget. Stop only on an absent next cursor.
Index drift. A new filter or sort added to the API without a matching index quietly converts seeks into scans. Add a test that runs EXPLAIN on each supported sort and filter combination and fails if Index Cond disappears.
Practical Recommendations
Start every new list endpoint with keyset-backed API pagination behind an opaque cursor, even if the table is small today. It costs one composite index and a cursor helper, and it saves a breaking API change later. Keep offset pagination for internal admin screens where jumping to page N is the main requirement and the collection is genuinely bounded.
Design the cursor as a product surface: versioned, signed or encrypted, bound to the query and caller, and short-lived. Make totals opt-in and estimated, and treat page-size limits as part of the contract.
Finally, verify with the database. Run EXPLAIN (ANALYZE, BUFFERS) on the first page, a deep page and a backward page for every supported sort, and look for an Index Cond containing your row comparison.
Checklist before shipping an API pagination endpoint:
- [ ] Sort order is total: ends in a unique, immutable column.
- [ ] Composite index matches equality filters, then sort columns, in order.
- [ ] Next-page predicate uses a row-value comparison, and the plan shows
Index Cond. - [ ] Query fetches
limit + 1to computehas_more. - [ ] Cursor is opaque, versioned, signed or encrypted, query-bound, and expiring.
- [ ] Authorization is derived from the caller, never from the cursor.
- [ ] Page size has a documented default and maximum.
- [ ] Nullable sort columns are handled with
COALESCEor a two-phase walk. - [ ] Totals are absent, optional or explicitly estimated.
- [ ]
nextandprevappear in the body and an RFC 8288Linkheader. - [ ] Clients stop on an empty next cursor and persist checkpoints.
- [ ] An automated
EXPLAINtest guards each supported sort and filter.
Frequently Asked Questions
What is the difference between offset and cursor pagination?
Offset pagination asks the server to skip a number of rows and return the next batch, so the position is a count. Cursor pagination returns an opaque token that encodes where the last page ended, usually the sort key of the last row, so the position is a value. Counts shift when rows are inserted or deleted, which causes duplicates and skipped rows. Values do not shift. Offset cost also grows with page depth, while a keyset-backed cursor stays roughly constant.
Is keyset pagination always faster than OFFSET in PostgreSQL?
On deep pages, yes, provided a composite index matches the sort and the predicate becomes an index condition. In our PostgreSQL 16 test the first page cost the same either way, about four buffers. By row one million, offset touched 15,199 buffers against five for keyset. Keyset loses its advantage when the sort has no supporting index, when selective filters are applied after the seek, or when the predicate is written in an expanded OR form that the planner treats as a filter.
How do I implement cursor pagination with a non-unique column such as created_at?
Append a unique tiebreaker, usually the primary key, to both the ORDER BY and the cursor: ORDER BY created_at, id with WHERE (created_at, id) > ($1, $2). Index the same column list. Without the tiebreaker, rows sharing the boundary timestamp are skipped. This is common, because PostgreSQL’s now() returns the transaction start time, so every row inserted in one transaction gets an identical timestamp. Serialize both values losslessly in the cursor.
Can you jump to a specific page number with cursor pagination?
Not by page number, because a cursor encodes a position by value rather than by count. You can jump by value instead, for example to the first item after a date or a name prefix, which is itself a keyset seek. If arbitrary page numbers are a hard requirement, use offset pagination on bounded collections. Google’s AIP-158 also defines an optional skip field alongside page tokens for this purpose.
Should a paginated API return the total count?
Only if clients truly need it, and preferably as an estimate. An exact COUNT(*) must visit rows under PostgreSQL’s MVCC model and can cost more than the page itself; it took about 185 ms on our two-million-row table. pg_class.reltuples gives a whole-table estimate in about a millisecond. AIP-158 says total_size may be an estimate if documented, and JSON:API’s cursor profile defines a separate estimatedTotal field. Many products show “more results” instead of a number.
What should the maximum page size be for a REST API?
There is no universal number. Public APIs illustrate the range: Stripe allows 1 to 100 with a default of 10, GitHub usually caps per_page at 100, and Slack recommends 100 to 200 with a maximum of 1,000 that varies by method. Size the maximum so a full page stays within your latency budget and response-size limits. Then document it, and either clamp oversized requests as AIP-158 suggests or reject them as JSON:API does.
Further Reading
- How to find and fix slow PostgreSQL queries with EXPLAIN ANALYZE, pg_stat_statements and auto_explain: the plan-reading skills you need to verify every keyset query.
- B-tree and B+tree index internals: page splits, fill factor and fan-out: why a composite index makes a keyset seek cost a handful of pages.
- gRPC vs REST vs GraphQL vs Connect: an API comparison: how each protocol models collections and streaming.
- Google AIP-158: Pagination: the most precise public rule set for page tokens.
- GraphQL Cursor Connections Specification: the reference contract for cursor pagination in GraphQL.
References
- PostgreSQL Documentation, “LIMIT and OFFSET”: https://www.postgresql.org/docs/current/queries-limit.html
- PostgreSQL Documentation, “Row Constructor Comparison” (Comparison Functions): https://www.postgresql.org/docs/current/functions-comparisons.html
- PostgreSQL Documentation, “Sorting Rows (ORDER BY)”: https://www.postgresql.org/docs/current/queries-order.html
- PostgreSQL Documentation, “pg_class” catalog: https://www.postgresql.org/docs/current/catalog-pg-class.html
- PostgreSQL Documentation, “Date/Time Functions and Operators”: https://www.postgresql.org/docs/current/functions-datetime.html
- PostgreSQL 18 Release Notes (skip scan, released 2025-09-25): https://www.postgresql.org/docs/current/release-18.html
- PostgreSQL Wiki, “Count estimate”: https://wiki.postgresql.org/wiki/Count_estimate
- IETF RFC 8288, “Web Linking”, M. Nottingham, October 2017: https://www.rfc-editor.org/info/rfc8288
- Google API Improvement Proposals, AIP-158 “Pagination”: https://google.aip.dev/158
- Relay, “GraphQL Cursor Connections Specification”: https://relay.dev/graphql/connections.htm
- JSON:API, “Cursor Pagination” profile: https://jsonapi.org/profiles/ethanresnick/cursor-pagination/
- Stripe API Reference, “Pagination”: https://docs.stripe.com/api/pagination
- Stripe Docs, “How pagination works”: https://docs.stripe.com/api-pagination
- GitHub Docs, “Using pagination in the REST API”: https://docs.github.com/en/rest/using-the-rest-api/using-pagination-in-the-rest-api
- Slack Developer Docs, “Pagination”: https://docs.slack.dev/apis/web-api/pagination
- Elastic Docs, “Paginate search results”: https://www.elastic.co/docs/reference/elasticsearch/rest-apis/paginate-search-results
- Confluent Docs, “Kafka Consumer Design”: https://docs.confluent.io/kafka/design/consumer-design.html
By Riju — about
