Pagination Patterns for JSON APIs: Offset, Cursor, and Keyset
Pagination looks like the most boring part of API design until rows shift underneath it: users see duplicates, feeds skip entries, and page 500 takes two seconds to load. The three standard patterns — offset, cursor, and keyset — make different trade-offs between simplicity, consistency, and performance. Here is how each one shapes your JSON, and when to use which.
Offset and limit: the simple default
The pattern everyone knows: limit and offset in the query string, and a response envelope carrying the page of items plus a total. It's trivial to implement, maps directly to SQL LIMIT/OFFSET, and gives consumers page numbers and total counts — things admin tables and export dialogs genuinely need.
{
"items": [
{ "id": "art_106", "title": "Cursor pagination in practice", "author": "mira" },
{ "id": "art_107", "title": "Offset vs keyset, measured", "author": "jonas" }
],
"total": 412,
"limit": 2,
"offset": 4
}Its two failure modes are structural, not fixable with tuning. OFFSET 100000 makes the database read and discard 100000 rows before returning yours, so deep pages are slow no matter what you index. And because an offset is a position in a live list, any insert or delete before that position shifts it — the classic bug where a feed shows an article twice, or never.
Cursor pagination: give me what comes after this
Cursor pagination replaces the position with a pointer to the last item the client saw. The response includes a next_cursor — a token the client passes back verbatim to fetch the following page. No totals, no page numbers, just "more after this" until next_cursor comes back null.
{
"items": [
{
"id": "art_108",
"title": "Opaque cursors, explained",
"author": "mira",
"cursor": "YXJ0XzEwOA=="
},
{
"id": "art_109",
"title": "Keyset vs offset: the math",
"author": "sam",
"cursor": "YXJ0XzEwOQ=="
}
],
"next_cursor": "YXJ0XzEwOQ==",
"has_more": true
}The ordering key must be stable and unique — typically a timestamp paired with the id as a tiebreaker, because many rows can share a timestamp. The cursor is just that pair, base64-encoded so clients treat it as opaque; you can change what's inside — add a shard hint or a version byte — without breaking anyone.
Keyset pagination: cursors at the database level
Keyset is cursor pagination implemented directly in SQL: WHERE (created_at, id) < (last_created_at, last_id) ORDER BY created_at DESC, id DESC LIMIT n. With an index on (created_at, id), every page costs the same as page one — the database seeks into the index instead of counting past discarded rows.
The constraint is that you can only paginate in directions the index supports, and filters must compose with the key: if the user filters by author, the index needs to lead with author. That's why keyset shines for feeds, timelines, and infinite scroll — append-only, filter-light, ordered by an indexed column — and feels restrictive for arbitrary sorts like price, then rating, then title.
Opaque page tokens
A page token generalizes the cursor: base64-encoded JSON capturing everything needed to resume — the keyset position plus a fingerprint of the filter and sort that produced the page. When a client returns a token whose fingerprint doesn't match its current request, reject it with a 400 rather than silently returning rows from a different query.
{
"v": 1,
"k": { "created_at": "2025-09-16T10:12:44Z", "id": "art_109" },
"q": { "author": "mira", "sort": "newest" },
"exp": "2025-09-17T00:00:00Z"
}Include an expiry so stale bookmarks into a moving dataset fail cleanly instead of returning eerily old pages, and sign the token if leaking its contents — or letting clients forge one — would matter for your threat model. Opaque doesn't mean secret by default.
Consistency under inserts
The decisive comparison happens while a client pages and new rows keep arriving. With offset, page boundaries shift with every insert — duplicates and skips are guaranteed under churn. With cursor or keyset, "after art_109" still means art_109, so a concurrent insert is simply picked up on the next page, or missed if it sorts before the cursor. The stream stays coherent even though it isn't a snapshot.
If you genuinely need a frozen view — exports, audits, backfills — paginate against an explicit snapshot: an as-of timestamp, a created_before watermark, or a materialized result set. No pagination scheme is a transaction, and pretending otherwise is how quiet data bugs are born.
Link headers or body envelope?
There are two delivery styles for pagination metadata. GitHub-style Link headers keep the JSON body pure — a Link header with rel="next" — which is HTTP-native and lets generic clients walk pages without understanding your schema. The body envelope (items plus next_cursor and has_more) is self-describing, survives proxies that strip headers, and is what most JSON APIs end up choosing.
If you use Link headers, still expose has_more — or an estimate — somewhere, because "follow next until it disappears" gives a UI nothing to render progress from. Whichever style you pick, only compute exact totals on offset endpoints: counting a cursor feed's total often costs more than fetching the page itself.
FAQ
- Which pagination pattern should my JSON API use by default?
- Offer offset when consumers need totals and page-jumping — admin tables, reports — on moderate datasets. Make cursor or keyset the default for anything feed-like or high-volume, where stability under inserts and constant per-page cost matter more than page numbers. Many mature APIs ship both: offset for search results, cursors for timelines.
- Why does offset pagination skip or duplicate rows?
- Because an offset is a position in the live result set, not a property of a row. If anything is inserted or deleted before the current position between two requests, every subsequent page shifts by that amount, so the client sees one row twice or not at all. Cursor and keyset pagination anchor to a column value instead of a position, which makes them immune to that shift.
- What should a cursor contain?
- The sort-key values of the last returned row — typically created_at plus a unique tiebreaker like id — base64-encoded so clients treat it as opaque. Keep it small, version its payload so you can change the format later, and include an expiry if the data moves fast. Sign it if forgery would be a problem; opacity alone isn't authentication.
- How do I paginate when the sort column isn't unique?
- Always add a unique tiebreaker, usually the primary key, and order and compare on the pair. Without it, rows sharing a sort value can appear on multiple pages or be skipped entirely, because the database has no stable order among ties. This bug rarely shows up in development data and reliably shows up in production.
Try these tools
Related articles
What is JSON? A Complete Beginner's Guide →
JSON (JavaScript Object Notation) is the lightweight data-interchange format behind nearly every API, config file, and NoSQL database. Learn the syntax, the six data types, how parsing behaves in code, and the mistakes everyone makes hand-writing it.
The History of JSON: From 2000 to Industry Standard →
Trace JSON from Douglas Crockford's idea in 2001, through Yahoo's adoption, to becoming the ECMA-404 standard that powers 90% of modern APIs.
The Complete JSON Schema Guide (Draft 7) →
JSON Schema is the standard for describing and validating JSON structure. Learn the core keywords, build a real API schema, compose and reuse schemas with $ref, and run validation in code with Ajv.