Pagination is a contract: a total order, a bounded page size, a continuation token, and an end signal. Offset/limit is simple and seekable but drifts under writes and slows at depth; cursor pagination encodes the last row's sort key, stays stable when that key is unique and immutable, and needs opaque signed tokens scoped to the filter and sort.
- A list endpoint promises four things: a total order, an effective page size, a continuation token, and an honest end signal; leaving any of them implicit creates duplicates, gaps, and stuck clients.
- Offset/limit is the right tool for small, seekable collections; cursor pagination is the right tool for feeds, exports, sync jobs, and any collection that grows or changes while clients read it.
- Every order needs a unique, immutable tie-breaker such as the primary key, and the cursor comparison and database index must match that order exactly.
- Treat cursors as opaque, signed, versioned, expiring state scoped to the tenant, filter, and sort; validate page size and offset before the database sees them.
- Compute has_more by fetching one extra row, generate body links and Link headers from one function, keep page reads idempotent, and cache only pages whose contents are actually stable.
An API page is a promise: the next request resumes exactly where the previous one stopped. That promise has four parts β a total order, a page size, a continuation token, and an end signal β and most pagination bugs come from leaving one of them implicit. A table that duplicates a row after an insert, a sync job that silently skips an invoice, and an export that stalls at ninety thousand rows are usually the same defect seen through different clients.
The shape of the token also decides whether a deep page costs an index seek or a scan and discard of a million rows. The choices below determine whether the promise survives load, concurrent writes, and retries.
Pagination is an API contract, not a query detail
Every list endpoint should state what it guarantees. Four decisions define the contract. First, the collection has a total order that does not change between requests unless the underlying data changes. Second, the server chooses an effective page size and documents the default and the maximum. Third, the response carries a continuation token that names the exact position after the last returned item. Fourth, the response says whether more data exists and, ideally, provides the next link.
Most APIs document only the page size. The rest is discovered through production incidents: an order that flips between requests, a limit that silently caps, a next link that returns an empty page, or a cursor that cannot be used after a filter changes. A useful contract also states what happens under concurrent writes. Promising no duplicates and no gaps requires a snapshot or a position-based token; promising best effort is acceptable only if it is written down so clients can compensate.
Offset/limit versus cursor pagination
Offset/limit is the familiar form: ?offset=40&limit=20 or ?page=3&per_page=20. It maps directly to SQL OFFSET and LIMIT, supports random access to any page, and pairs naturally with a total count. Its weaknesses appear as collections grow and change. The database must walk and discard offset rows before returning any, so latency grows with depth, and a concurrent insert before the current position shifts every later row, producing duplicates on the next page and gaps after a delete.
Cursor pagination replaces the arithmetic position with a value-based one. The token encodes the sort key of the last item on the page, so the next query becomes WHERE (created_at, id) < (:created_at, :id) ORDER BY created_at DESC, id DESC LIMIT :n. The database seeks directly into the index, cost stays roughly constant with depth, and inserts or deletes behind the cursor cannot shift the boundary. The price is sequential access only, a more complex token, and no natural page number.
| Dimension | Offset/limit | Cursor |
|---|---|---|
| Access pattern | random jumps to any page | sequential next and previous |
| Cost at depth | grows with the offset | index seek plus page size |
| Concurrent inserts | duplicates and gaps at boundaries | no boundary drift |
| Concurrent deletes | rows skipped | deleted rows simply absent |
| Total count | natural, but a separate query | avoid or estimate |
| Client state | small integers | opaque token |
| Filter or sort change | re-query from the first page | invalidate the cursor |
| Cache behavior | URL cacheable, contents shift | high cardinality, rarely shared |
| Best fit | admin tables, small static sets | feeds, exports, sync, large sets |
Choose by access pattern, not fashion. Admin tables that jump to page 40 and show a count fit offset when the table is small or the filter is selective. Feeds, exports, sync jobs, audit logs, and anything that can grow without bound should use cursors. A hybrid endpoint can accept page for small offsets and cursor for traversal, but it should reject mixing both and document the limits; never switch modes silently based on collection size.
Stable total ordering and tie-breakers
Offset pagination without an ORDER BY is undefined; cursor pagination without a total order is unsafe. ORDER BY created_at DESC is not a total order, because timestamps collide. When two rows share a timestamp, the database may return them in any order, and that order can change with the query plan, a different replica, or a new index. A page boundary between equal values then repeats one row and drops the other.
Add a unique, immutable tie-breaker: ORDER BY created_at DESC, id DESC. The cursor must carry both values, and the comparison must match the index, preferably with a row constructor such as WHERE (created_at, id) < (:created_at, :id) where supported, otherwise with an expanded OR comparison. An index on (created_at, id) lets the database seek to the position and read forward; a mixed sort such as created_at DESC, id ASC needs a matching index and comparison.
The tie-breaker must never change. updated_at, status, score, and popular counts move rows between pages as they change, reintroducing duplicates and gaps even with a cursor. A sequence key, UUIDv7, or ULID is stable, unique, and usually indexed. Define null placement explicitly with NULLS LAST, because databases disagree, and remember that case-insensitive or locale-aware collations can treat two strings as equal; the unique tie-breaker still resolves the order.
Opaque cursors and signed cursor state
A cursor is a position, not an identifier. It should look like an opaque string to the client, even if it is readable base64 inside. Opacity lets the server change the encoding without a breaking change, stops clients from fabricating valid cursors, and keeps sequential database IDs out of URLs where they invite enumeration and support confusion.
A practical payload is compact JSON: a version, the sort-key values, the direction, a hash of the normalized filter and sort, the tenant or user scope, and an issue time with an expiry. Encode it with base64url and sign it with HMAC-SHA256 using a server-held key. On the next request, verify the signature before parsing, check the expiry, and compare the filter hash and scope. Reject a mismatch with 400 and a stable error code rather than returning an arbitrary slice of a different query.
Signing protects integrity, not confidentiality. Anyone holding the cursor can decode base64, so do not put an email address, a search term, or a full filter object in the payload unless you encrypt it with an authenticated cipher. Keep the token small: it travels in a query string and must survive proxies, logs, and copy-paste. A stateless signed cursor needs no storage and scales trivially, but it cannot be revoked or pinned to a database snapshot; a server-side cursor can do both at the cost of storage and expiry management. Version the format so old cursors fail cleanly after a deploy.
GET /v1/invoices?limit=2&cursor=eyJ2IjoxLCJjcmVhdGVkX2F0IjoiMjAyNi0xMS0xN1QwODowMDowMFoiLCJpZCI6Imludl8xMDI0In0 HTTP/1.1
Host: api.example.com
Accept: application/json
HTTP/1.1 200 OK
Content-Type: application/json
{
"items": [
{ "id": "inv_1023", "created_at": "2026-11-17T08:00:00Z", "total": 4200 },
{ "id": "inv_1022", "created_at": "2026-11-17T07:59:58Z", "total": 1900 }
],
"has_more": true,
"next_cursor": "eyJ2IjoxLCJjcmVhdGVkX2F0IjoiMjAyNi0xMS0xN1QwNzo1OTo1OFoiLCJpZCI6Imludl8xMDIyIn0"
}
The next request sends that cursor and the same limit; the server verifies the signature, rebuilds the pair (2026-11-17T07:59:58Z, inv_1022), and continues strictly after that position.
Page size defaults, maximums, and validation
An endpoint without a maximum page size is an outage waiting for a client with a loop. Serializing a million rows consumes memory, holds a database connection, fills the network, and often times out after the work is done. Set a default, commonly between 20 and 100 rows, and a hard maximum. Rejecting limit=100000 with 400 is the clearest contract; silently clamping to 100 is acceptable only if the response echoes the effective limit and has_more remains correct.
Validate zero, negative, non-integer, repeated, and absurd values. Bound offset as well, for example at 10,000, and return a 400 that tells the client to use a cursor. Count bytes, not only rows: wide rows, nested expansions, and binary fields can make twenty items larger than a thousand narrow ones, so cap expanded resources separately. OWASP lists unrestricted resource consumption as a top API risk precisely because limits like these are often missing.
Echo the effective limit and the returned item count in every response. This makes clamping visible and gives support a number to ask for. Page size also interacts with rate limits: a default of 10 forces ten requests where one would do, while a default of 1,000 punishes mobile clients. Measure payload size and latency, then pick a default that fits the median record.
has_more, next/prev links, and Link headers
has_more should be computed, not guessed. Fetch limit + 1 rows, return the first limit, and set has_more from whether the extra row existed. This costs one extra row and avoids a COUNT(*) over a large, filtered table. A count is still useful for a range label such as 1β20 of 4,382, but it should be optional and marked as an estimate when it comes from statistics.
The next cursor is the sort key of the last returned item, never an index into the result set. Include next_cursor only when has_more is true, and include prev_cursor only for APIs that support backward traversal, deriving it from the first item. A link that returns an empty page wastes a round trip and signals a broken contract.
The Link header gives HTTP clients the same information without parsing the body: Link: <https://api.example.com/v1/invoices?limit=20&cursor=...>; rel="next". Use absolute URLs, preserve every filter and sort parameter, URL-encode the cursor, and emit rel="prev", rel="first", and rel="last" only when meaningful. Cursor APIs often cannot implement last cheaply, so omit it rather than fake it. Generate body links and header links from one function; divergence between them is a common bug. For an empty result or the final page, return 200 with an empty items array and has_more: false.
Do not expose total unless the count is cheap or explicitly approximate. A precise total on a large filtered collection can cost more than the page itself.
Filters, sorting, and cursor invalidation
A cursor is valid only for the query that produced it. If the client adds status=paid but reuses a cursor created for all statuses, the value-based position still points into the old ordering, and the server returns an arbitrary slice that looks plausible. Bind a hash of the canonicalized filter and sort into the cursor, recompute it on the next request, and reject a mismatch with a specific error such as cursor_query_mismatch. Canonicalize parameter order, case, and encoding before hashing.
Sorting needs an allowlist. Accepting a client-supplied column name invites SQL injection and unindexed sorts that scan the table. Map public sort names to known columns and directions, reject everything else, and document the default sort. When the sort changes, traversal resets: the client starts from the first page without a cursor.
Search is the hard case. A relevance score can change as indexes merge, so ORDER BY score DESC alone is not stable; pair it with a unique tie-breaker and warn clients that order may shift. If stable traversal matters more than fresh ranking, pin a snapshot or an as_of timestamp in the cursor. Time-range filters deserve the same treatment: set an upper bound at the first request so newly created rows cannot enter the traversal halfway through.
Deep offsets, consistency, and idempotent page reads
Deep offset is expensive even when the query uses an index. OFFSET 100000 LIMIT 20 asks the database to locate, count, and discard 100,000 rows before returning twenty; work grows linearly, the buffer cache suffers, and the plan may abandon the index entirely. Keyset pagination turns the same request into an index seek plus twenty reads, which stays flat as the collection grows. If an endpoint keeps offset pagination for compatibility, cap the offset and route deeper requests to a cursor or an export.
Cursor pagination removes boundary drift, not every consistency problem. An insert before the cursor is invisible to the traversal because it was already passed; an insert after the cursor appears on a later page. Neither duplicates a row. A delete after the cursor simply removes an item, and deleting the cursor row does not break the query because the comparison uses values, not existence. Updates are different: if the sort key changes, the row can move behind or ahead of the cursor. That is why sort keys must be immutable.
When the product needs a true snapshot, make it explicit. Embed a snapshot timestamp or transaction identifier in the cursor and add created_at <= :snapshot to every page query, or run the traversal in a repeatable-read transaction or against a materialized export. Document the cost and expiry. Without a snapshot, promise best effort: no duplicates from inserts or deletes, while updates to sort keys may move rows.
Page reads should be idempotent. A GET with the same cursor and parameters must return the same page when the data has not changed, and retrying it must be safe. Do not consume cursors server-side; a retry after a timeout should not receive 409. If a cursor must be single-use, return an idempotency key and document the behavior.
Caching follows from the same contract. First pages without a cursor are shared by many clients and can carry a short TTL; cursor pages are high-cardinality and change with the collection, so they rarely hit a shared cache. Mark user-scoped pages private or no-store, use ETag for conditional reads, and never let a CDN key omit the cursor or filters. A cursor in a URL can leak through logs and referrers, so keep sensitive values out of the payload.
Verification checklist with concrete pass criteria
Test pagination against a table with duplicate sort keys, rows inserted and deleted during traversal, and at least one page boundary. Verification is a sequence of requests, not a unit test of the serializer.
# 1. Fetch the first page and capture the cursor.
curl -sS 'https://api.example.com/v1/invoices?limit=2' -o page1.json
# 2. Insert a row before the boundary, then fetch the next page with the old cursor.
curl -sS 'https://api.example.com/v1/invoices?limit=2&cursor=...' -o page2.json
# 3. Tamper with the cursor and confirm a clean rejection.
curl -sS -o /dev/null -w '%{http_code}\n' \
'https://api.example.com/v1/invoices?cursor=not-a-real-cursor'
# 4. Request an oversized page and inspect the effective limit.
curl -sS 'https://api.example.com/v1/invoices?limit=100000' | head -c 300
The HTTP request workbench replays these calls with exact headers; the JSONPath explorer extracts items[].id and has_more from captured responses; the JSON formatter pretty-prints each page so ordering and duplicate IDs are visible.
Treat a check as passed only when every row in the table below holds:
| Check | How to test | Pass criteria |
|---|---|---|
| Total order | Request the same page twice with no writes | Identical IDs in identical order |
| Tie-breaker | Insert rows that share the boundary timestamp | Traversal returns each row once, with no gap |
| Offset drift | Insert before the boundary and page with offset | The duplicate appears; the cursor path does not duplicate |
| Cursor integrity | Flip one character in the token | 400 with a stable error code, no data returned |
| Cursor scope | Reuse a cursor with a different filter or sort | 400 cursor_query_mismatch, no partial slice |
| Page size | Send limit=0, limit=-1, and limit=100000 | 400 or a documented clamp, with the effective limit echoed |
| has_more | Fetch the penultimate and final pages | true then false, and no next link on the final page |
| Link header | Inspect headers with curl -sSI | Absolute rel="next" URL, encoded cursor, filters preserved |
| Idempotent retry | Request the same cursor twice | Same page and status both times |
| Cache policy | Request an authenticated page and a public first page | no-store for the private page, explicit TTL for the public one |
A design that passes nine of ten checks can still duplicate rows in production. The failures that matter are at boundaries, so test boundaries explicitly.
Common mistakes and production rules
The expensive mistakes are predictable:
- Using
OFFSETfor deep pages and calling the latency a database problem. - Sorting only by
created_atand assuming ties are stable. - Exposing sequential database IDs as cursors, which invites enumeration.
- Signing nothing, so clients can forge a position or another tenant's scope.
- Omitting the filter hash, so a cursor silently applies to a different query.
- Accepting an unlimited
limitor offset and discovering it during an incident. - Returning
has_more: trueon the last page, or a next link that yields nothing. - Computing
totalwithCOUNT(*)on every page. - Consuming cursors server-side, so retries fail after a timeout.
- Caching cursor pages publicly or keying a CDN without the cursor.
The rules that prevent them are short. Define the ordering contract first: a unique, immutable sort key and a matching index. Choose offset for small, seekable collections and cursor for anything that grows or changes. Make the cursor opaque, signed, versioned, scoped, and expiring. Validate page size and offset at the edge, fetch one extra row for has_more, and generate links from one function. Treat page reads as idempotent GETs, document the consistency guarantee, cache only what is stable, and verify boundary behavior with real writes before shipping.
Frequently asked questions
Should an API use offset/limit or cursor pagination?
Use offset/limit for small or slowly changing collections that need random page access and a total count, such as an admin table. Use cursor pagination for feeds, exports, synchronization, audit logs, and any collection where deep offsets are expensive or concurrent writes would shift page boundaries.
Why do rows repeat or disappear while paging through a changing table?
An insert before the current offset shifts every later row down, so the next page repeats one row, and a delete shifts rows up, so one row is skipped. Cursor pagination avoids boundary drift because it compares sort-key values instead of counting rows, provided the sort key is unique, immutable, and indexed.
Can a cursor simply be the database ID?
It can, but exposing sequential IDs invites enumeration and fixes the encoding permanently. Prefer an opaque token that carries the full sort key, a version, a filter hash, a scope, and an expiry, signed with HMAC so clients cannot forge a position or read another tenant's data.
How should an API signal that more pages exist?
Fetch one row beyond the page size, return only the requested rows, and set has_more from whether the extra row existed. Include a next cursor only when has_more is true, and emit a Link header with rel=next built from the same values so body and header never disagree.
How large should a page be, and what happens above the limit?
Pick a default near the median response size you can serve comfortably, commonly 20 to 100 rows, and enforce a hard maximum. Reject larger requests with a documented 400, or clamp and echo the effective limit; never let a client request an unbounded page or offset.
JsonMind guides are written and reviewed by the engineering team. We check each guide against the relevant RFC or vendor documentation, link the primary sources, and update the guide when behavior changes.
Primary sources are linked at the end of each guide.