Pagination Patterns for JSON APIs: Offset, Cursor, and Keyset
Pagination looks like the most boring part of API design until rows shift under it: users see duplicates, feeds skip entries, and page 500 takes two seconds to load. The three standard patterns (offset, cursor, and keyset) trade simplicity, consistency, and performance against each other. This guide shows how each one shapes your JSON, and when to use which.
Offset and limit: the simple default
Everyone knows this pattern: limit and offset in the query string, and a response envelope carrying the page of items plus a total. You can implement it in minutes, it maps to SQL LIMIT/OFFSET, and it gives consumers page numbers and total counts, which admin tables and export dialogs 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 come from the design, and tuning won't fix them. OFFSET 100000 makes the database read and discard 100000 rows before returning yours, so deep pages stay slow whatever you index. And because an offset marks a position in a live list, any insert or delete before that position shifts it. That causes 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. You get no totals and no page numbers, only "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, usually a timestamp paired with the id as a tiebreaker, because many rows can share a timestamp. The cursor holds 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 written 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, because the database seeks into the index instead of counting past discarded rows.
The constraint: you can paginate only 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. Keyset works best for feeds, timelines, and infinite scroll (append-only, few filters, 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, meaning 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 instead of 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 leaked contents or forged tokens would matter in your threat model. Opaque tokens aren't secret by default.
Consistency under inserts
The comparison that decides it happens while a client pages and new rows keep arriving. With offset, page boundaries shift with every insert, so churn guarantees duplicates and skips. With cursor or keyset, "after art_109" still means art_109, so the next page picks up a concurrent insert, or misses it if it sorts before the cursor. The stream stays coherent, though it isn't a snapshot.
If you need a frozen view for exports, audits, or backfills, paginate against an explicit snapshot: an as-of timestamp, a created_before watermark, or a materialized result set. No pagination scheme gives you a transaction, and teams that pretend otherwise ship quiet data bugs.
Link headers or body envelope?
You can deliver pagination metadata two ways. 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) describes itself, survives proxies that strip headers, and wins in most JSON APIs.
If you use Link headers, still expose has_more or an estimate somewhere, because "follow next until it disappears" gives a UI nothing to show progress with. Whichever style you pick, compute exact totals only on offset endpoints. Counting a cursor feed's total often costs more than fetching the page.
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 feeds and high-volume endpoints, 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?
- An offset marks a position in the live result set, not a property of a row. If someone inserts or deletes a row before the current position between two requests, every later page shifts by that amount, and the client sees one row twice or not at all. Cursor and keyset pagination anchor to a column value instead of a position, so the shift can't affect them.
- What should a cursor contain?
- The sort-key values of the last returned row (usually 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 cause problems, since opacity alone doesn't authenticate anything.
- How do I paginate when the sort column isn't unique?
- 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 several pages or vanish, because the database keeps no stable order among ties. You'll rarely see this bug with development data and will see it 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.