JSON API Pagination: Offset, Cursor, and Keyset Patterns

Offset/Limit Pagination

Offset/limit is the simplest pagination strategy: GET /items?page=3&limit=20 maps directly to SELECT * FROM items LIMIT 20 OFFSET 60. A page of 20 items starting at page 3 requires an offset of (3 - 1) × 20 = 60 rows. The JSON response envelope typically wraps results with total and pagination metadata so clients can render page number controls and know how many pages exist.

-- SQL query for page 3, 20 items per page
SELECT * FROM items
ORDER BY id
LIMIT 20 OFFSET 60;

// JSON response — offset/limit
{
  "data": [
    { "id": 61, "name": "Widget A" },
    { "id": 62, "name": "Widget B" }
  ],
  "meta": {
    "total": 1543,
    "page": 3,
    "limit": 20,
    "pages": 78
  },
  "links": {
    "first": "/items?page=1&limit=20",
    "prev":  "/items?page=2&limit=20",
    "next":  "/items?page=4&limit=20",
    "last":  "/items?page=78&limit=20"
  }
}

Two problems make offset pagination unsuitable for large or frequently updated datasets. First, concurrent inserts and deletes between page fetches cause items to appear twice (an item shifts from page 3 to page 2 between requests) or be skipped entirely. Second, performance degrades linearly with high offset values — the database engine must scan all preceding rows and discard them. A query with OFFSET 100000 reads 100,001 rows to return 20. Suitable for datasets under 50,000 rows or admin interfaces where exact page numbers are required and data changes infrequently. For high-traffic feeds or large datasets, prefer cursor or keyset pagination.

When building offset-paginated endpoints, validate that limit is capped server-side (e.g., maximum 100) regardless of the client-supplied value. See REST API JSON responses for envelope conventions.

Cursor-Based Pagination

Cursor-based pagination uses an opaque token that encodes the position of the last item in the current page. GET /items?cursor=eyJpZCI6MTAwfQ&limit=20 — the token (base64url-encoded) decodes to {"id":100}; the database query is SELECT * FROM items WHERE id > 100 ORDER BY id LIMIT 20. The response includes a next cursor pointing to the last item of the returned page, which clients pass on subsequent requests.

-- SQL query using a cursor decoded to id = 100
SELECT * FROM items
WHERE id > 100
ORDER BY id
LIMIT 20;

// JSON response — cursor-based
{
  "data": [
    { "id": 101, "name": "Widget A" },
    { "id": 102, "name": "Widget B" }
  ],
  "meta": {
    "hasMore": true,
    "count": 20
  },
  "links": {
    "next": "/items?cursor=eyJpZCI6MTIwfQ&limit=20",
    "prev": null
  }
}

Cursor pagination is stable under concurrent inserts and deletes — because the query anchors to an item's identity rather than a row count, new items cannot shift the position of subsequent items in the result set. The tradeoff is that total item counts are not available by default (count queries are expensive), and clients cannot jump to an arbitrary page number. Cursor tokens should be opaque base64url strings, never raw integer IDs — this prevents clients from constructing arbitrary cursors and lets you change the internal encoding without a breaking change. Used by Twitter, Facebook, and GitHub APIs at scale.

// Node.js — encode and decode cursor tokens
const encodeCursor = (payload: object): string =>
  Buffer.from(JSON.stringify(payload)).toString('base64url')

const decodeCursor = (token: string): { id: number } =>
  JSON.parse(Buffer.from(token, 'base64url').toString('utf8'))

// Encode: { id: 120 } → "eyJpZCI6MTIwfQ"
const nextCursor = encodeCursor({ id: lastItem.id })

// Decode on next request
const { id: afterId } = decodeCursor(req.query.cursor)

Keyset Pagination

Keyset pagination is the fastest strategy for large datasets: filter by the last seen value of an indexed column rather than counting rows. GET /items?after_id=100&limit=20 translates to SELECT * FROM items WHERE id > 100 ORDER BY id LIMIT 20. With a B-tree index on id, this query executes in O(1) time regardless of dataset size — no rows are scanned before the anchor point. The database seeks directly to the indexed position and reads forward 20 rows.

-- Simple keyset: integer primary key
SELECT * FROM items
WHERE id > 100
ORDER BY id
LIMIT 20;

-- Composite keyset: handle ties in timestamp-sorted data
-- Returns items created before 2026-05-01, or at the same
-- timestamp with id < 500 (for descending order)
SELECT * FROM items
WHERE created_at < '2026-05-01'
   OR (created_at = '2026-05-01' AND id < 500)
ORDER BY created_at DESC, id DESC
LIMIT 20;

// JSON response — keyset
{
  "data": [
    { "id": 101, "name": "Widget A", "created_at": "2026-04-30T12:00:00Z" },
    { "id": 102, "name": "Widget B", "created_at": "2026-04-29T09:30:00Z" }
  ],
  "meta": {
    "hasMore": true,
    "count": 20
  },
  "links": {
    "next": "/items?after_id=120&limit=20"
  }
}

Keyset pagination has 3 requirements: a stable sort order, an index on the sort column(s), and an anchor value available from the previous response. It cannot support backward navigation or arbitrary page jumps — only forward iteration. For timestamp-sorted datasets, use composite keys (created_at, id) to handle multiple items with identical timestamps: the secondary id column breaks ties deterministically. Keyset pagination is the correct choice for activity feeds, audit logs, and any dataset over 100,000 rows where consistent sub-millisecond query performance is required.

JSON Response Structure

A well-designed paginated JSON response uses a standard envelope with 3 top-level keys: data (required array of items), meta (pagination metadata), and links (navigation URLs). The exact fields in meta and links vary by strategy. The JSON:API specification standardizes this envelope, but many APIs use informal conventions that follow the same shape.

Offset response shape

{
  "data": [...],
  "meta": {
    "total":  1543,
    "count":  20,
    "page":   3,
    "limit":  20,
    "pages":  78
  },
  "links": {
    "first": "/items?page=1&limit=20",
    "prev":  "/items?page=2&limit=20",
    "next":  "/items?page=4&limit=20",
    "last":  "/items?page=78&limit=20"
  }
}

Cursor response shape

{
  "data": [...],
  "meta": {
    "hasMore": true,
    "count":   20
  },
  "links": {
    "next": "/items?cursor=eyJpZCI6MTIwfQ&limit=20",
    "prev": null
  }
}

Keyset response shape

{
  "data": [...],
  "meta": {
    "hasMore":  true,
    "count":    20,
    "lastId":   120
  },
  "links": {
    "next": "/items?after_id=120&limit=20"
  }
}

RFC 5988 defines the Link response header as an alternative to embedding navigation URLs in the JSON body. GitHub's REST API uses this pattern: Link: </items?page=4>; rel="next", </items?page=1>; rel="first". The header approach keeps the JSON body clean but requires clients to parse HTTP headers in addition to the body. Embedding navigation URLs in the JSON body (as shown above) is more accessible for JavaScript clients. For REST API JSON responses, embedding links in the body is the more common convention in modern APIs.

Implementation in Node.js and Python

Both Node.js Express and FastAPI Python follow the same pattern: parse and validate pagination parameters from the query string, cap the limit server-side, query the database with the appropriate strategy, and return the JSON envelope. Always enforce the limit cap server-side regardless of what the client sends — never trust the client-supplied value.

Node.js Express — offset pagination

import express from 'express'
import { db } from './db'

const app = express()

app.get('/items', async (req, res) => {
  const page  = Math.max(1, parseInt(req.query.page as string)  || 1)
  const limit = Math.min(parseInt(req.query.limit as string) || 20, 100)
  const offset = (page - 1) * limit

  const [items, total] = await Promise.all([
    db.query('SELECT * FROM items ORDER BY id LIMIT $1 OFFSET $2', [limit, offset]),
    db.query('SELECT COUNT(*) FROM items'),
  ])

  const count = parseInt(total.rows[0].count)
  const pages = Math.ceil(count / limit)

  res.json({
    data:  items.rows,
    meta:  { total: count, page, limit, pages },
    links: {
      first: `/items?page=1&limit=${limit}`,
      prev:  page > 1    ? `/items?page=${page - 1}&limit=${limit}` : null,
      next:  page < pages ? `/items?page=${page + 1}&limit=${limit}` : null,
      last:  `/items?page=${pages}&limit=${limit}`,
    },
  })
})

Node.js Express — cursor pagination

app.get('/items/cursor', async (req, res) => {
  const limit  = Math.min(parseInt(req.query.limit as string) || 20, 100)
  const cursor = req.query.cursor as string | undefined

  let afterId = 0
  if (cursor) {
    try {
      const decoded = JSON.parse(Buffer.from(cursor, 'base64url').toString())
      afterId = decoded.id
    } catch {
      return res.status(400).json({ error: 'invalid cursor' })
    }
  }

  // Fetch limit + 1 to check if more pages exist
  const items = await db.query(
    'SELECT * FROM items WHERE id > $1 ORDER BY id LIMIT $2',
    [afterId, limit + 1]
  )

  const hasMore = items.rows.length > limit
  const data    = hasMore ? items.rows.slice(0, limit) : items.rows
  const lastItem = data[data.length - 1]
  const nextCursor = lastItem
    ? Buffer.from(JSON.stringify({ id: lastItem.id })).toString('base64url')
    : null

  res.json({
    data,
    meta:  { hasMore, count: data.length },
    links: { next: nextCursor ? `/items/cursor?cursor=${nextCursor}&limit=${limit}` : null },
  })
})

FastAPI Python — cursor pagination

import base64
import json
from fastapi import FastAPI, Query
from typing import Optional

app = FastAPI()

@app.get("/items")
def get_items(
    cursor: Optional[str] = None,
    limit:  int            = Query(default=20, ge=1, le=100),
):
    after_id = 0
    if cursor:
        try:
            payload  = base64.urlsafe_b64decode(cursor + "==").decode()
            after_id = json.loads(payload)["id"]
        except Exception:
            return {"error": "invalid cursor"}, 400

    # Query limit + 1 to detect hasMore
    rows = db.execute(
        "SELECT * FROM items WHERE id > ? ORDER BY id LIMIT ?",
        (after_id, limit + 1)
    ).fetchall()

    has_more  = len(rows) > limit
    data      = rows[:limit]
    last_id   = data[-1]["id"] if data else None
    next_cur  = (
        base64.urlsafe_b64encode(json.dumps({"id": last_id}).encode()).decode()
        if last_id and has_more else None
    )

    return {
        "data":  data,
        "meta":  {"hasMore": has_more, "count": len(data)},
        "links": {"next": f"/items?cursor={next_cur}&limit={limit}" if next_cur else None},
    }

For FastAPI JSON APIs, the Queryhelper with ge=1, le=100 enforces the range at the framework level and returns an automatic 422 validation error for out-of-range values — no manual cap needed. For Express JSON API, always apply Math.min(limit, 100) manually since Express does not validate query parameters by default. Use Jsonic's JSON formatter to inspect paginated response shapes during development.

Key terms

Offset pagination

A pagination strategy that uses LIMIT N OFFSET M SQL clauses to return a specific page of results; simple to implement but degrades in performance as the offset grows because the database must scan all preceding rows.

Cursor

An opaque, base64url-encoded token that encodes the position of the last seen item in a paginated result set; clients pass the cursor back unchanged on subsequent requests to fetch the next page.

Keyset pagination

A pagination strategy that filters results using the last seen value of an indexed column (e.g., WHERE id > 100), achieving O(1) database lookup time regardless of how many rows precede the anchor point.

Opaque token

A string value that clients treat as a black box — passing it back to the server without decoding, modifying, or constructing new values — allowing the server to change the internal encoding without a breaking API change.

Page envelope

The top-level JSON structure wrapping paginated API responses, typically containing a data array, a meta object with counts and pagination state, and a links object with navigation URLs.

Composite keyset

A keyset pagination approach using two or more columns as the sort key (e.g., (created_at, id)) to handle ties where multiple items share the same primary sort value, ensuring deterministic ordering.

Link header

An HTTP response header defined by RFC 5988 that encodes navigation URLs for paginated resources (e.g., rel="next", rel="last") as an alternative to embedding pagination links in the JSON body.

Frequently asked questions

What is the difference between offset pagination and cursor pagination?

Offset pagination uses LIMIT N OFFSET M — simple but slow for large datasets because the database must scan M rows before returning results. Cursor pagination uses a pointer to the last seen item, allowing O(1) lookups regardless of position. Cursor pagination is more stable — no duplicate or missing items when concurrent inserts happen between page fetches — but cannot jump to arbitrary page numbers. For datasets under 50,000 rows where users need direct page navigation, offset pagination is acceptable. For feeds, timelines, and large datasets, cursor pagination is the correct choice. The JSON:API specification supports both strategies through its links.next and links.prev envelope.

Why does offset pagination get slow on large tables?

OFFSET N forces the database to read and discard N rows before returning results. For OFFSET 100000, the database reads 100,001 rows and returns only the last 20. This scales linearly with the offset value. PostgreSQL, MySQL, and SQLite all exhibit this behavior because the database engine cannot skip to an arbitrary row position without reading all preceding rows. A query with OFFSET 100000 LIMIT 20 takes roughly 5× longer than one with OFFSET 20000 LIMIT 20. Adding an index on the ORDER BY column helps but does not eliminate the fundamental O(N) scan cost. Use keyset or cursor pagination for tables over 50,000–100,000 rows to maintain consistent sub-millisecond query times.

Should I include a total count in a paginated JSON response?

It depends on the use case. Count queries (SELECT COUNT(*)) are expensive on large tables because they typically require a full table scan without a covering index. Cursor-based APIs often omit the total entirely — Twitter, Facebook, and GitHub's cursor APIs do not return totals. Offset pagination typically includes a total for UI page number rendering. If you include a total count, consider caching the result with a short TTL (e.g., 60 seconds in Redis) to avoid recalculating it on every request. PostgreSQL offers pg_class.reltuples for fast approximate row counts accurate within a few percent for large tables — sufficient for rendering approximate page counts in a UI.

What is an opaque cursor token and why use one?

A cursor is opaque when clients treat it as a black-box string — they pass it back without decoding or modifying it. Internally it can encode a position, timestamp, or composite key as a base64url-encoded JSON payload. Opacity prevents clients from constructing arbitrary cursors that could break your implementation (e.g., requesting items starting at a negative ID). It also lets you change the encoding — switching from integer IDs to UUIDs, or adding composite sort fields — without a breaking API change, since clients never parse the token. Always base64url-encode the cursor payload and reject malformed tokens with a 400 error. Never use raw integer IDs as cursor values in public APIs.

What does the JSON:API specification say about pagination?

The JSON:API specification uses links.next, links.prev, links.first, and links.last in the response root for pagination navigation. Each is a full URL string or null. The meta.total field is a non-standard but widely adopted extension for conveying total item counts. The spec intentionally leaves the pagination strategy (offset, cursor, or keyset) up to the implementer — it only standardizes the response envelope shape. Query parameter conventions for pagination are also left to the implementer; common patterns are page[number] / page[size] for offset and page[cursor] for cursor-based navigation.

What page size should I use as default for a JSON API?

20–50 items is a common default that balances latency and payload size. Set a server-side maximum of 100–500 items and silently cap or reject client requests above the limit — never trust the client-supplied limit value without validation. A malicious or buggy client could send limit=1000000 and trigger a database timeout or out-of-memory error. Smaller defaults (20 items) reduce response payload size and time-to-first-byte; larger pages (100 items) reduce round trips for bulk operations. Document both the default and maximum in your API spec. For FastAPI, use Query(default=20, le=100) to enforce the cap automatically at the framework level.