REST API Pagination: Cursor vs Offset—How to Choose and Implement

Overview

Pagination is how a REST API returns large result sets in manageable chunks. Two dominant styles exist: offset-based (page/offset + limit) and cursor-based (a.k.a. keyset) pagination. Choosing the right approach affects performance, correctness under concurrent writes, and developer experience.

This article explains how each method works, their trade‑offs, gotchas you’ll hit in production, and practical implementation patterns with examples.

Quick definitions

• Offset pagination: Fetch the Nth page by skipping a number of rows, then taking a fixed amount.
• Cursor pagination: Fetch the “next chunk” by providing a position (cursor) that tells the server where to resume scanning.

Offset example

HTTP

GET /items?limit=20&offset=40

SQL (typical)

SELECT *
FROM items
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 40;

Cursor example

HTTP (opaque token)

GET /items?limit=20&cursor=eyJjcmVhdGVkX2F0IjoiMjAyNi0wMy0xNFQxMDozMjo1OVoiLCJpZCI6IjEyMzQifQ

SQL (keyset seek)

SELECT *
FROM items
WHERE (created_at, id) < ($last_created_at, $last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

The cursor encodes the last seen sort keys (here: created_at, id). The server returns the next slice just “after” those keys.

When offset is the right fit

Offset pagination is simple, ubiquitous, and easy to cache.

• Small datasets or low offsets: Admin dashboards, short lists, or development tools.
• Random access to “page N”: Users can jump directly to page 37.
• Straightforward CDNs and static caching: URLs with fixed parameters are cacheable.
• Familiar semantics: Works with SQL OFFSET/LIMIT without special indexes.

If your dataset remains small or you rarely exceed a few thousand rows of offset, offset pagination can be perfectly adequate.

The hidden costs of offset

Offset pagination degrades as data grows and as writes happen concurrently.

• Performance: OFFSET K means the database must scan and discard K rows before returning results. Latency becomes O(offset + limit), and query plans often become less efficient at high offsets.
• Skips and duplicates: Inserts/deletes between requests shift rows. A user may see items twice or miss items entirely when they move to the next page.
• Unstable ordering: If ORDER BY is not unique or deterministic, small timing differences produce inconsistent pages.
• Large “last pages” are slow: Getting the “end” of a large list requires skipping millions of rows.

Why cursor pagination scales better

Cursor (keyset) pagination uses stable sort keys to resume scanning from a known position, avoiding row-skips.

• Performance: Latency is O(limit). The database uses an index on the sort keys and continues the scan from a predicate like “(created_at, id) < (?, ?)”.
• Better consistency under writes: New inserts don’t reshuffle previously delivered pages when the cursor uses a deterministic order and exclusive bounds.
• Natural for infinite scroll: “Load more” UIs and API consumers simply follow next cursors.

Trade‑offs exist, though: you can’t jump to arbitrary page numbers easily, computing total counts is expensive, and implementing prev cursors requires care.

Design principles for reliable cursor pagination

1. Deterministic ordering

• Always sort by a unique, immutable tie‑break key.
• Common pattern: ORDER BY created_at DESC, id DESC (id is the tie‑break).
• Avoid sorting by non-unique, frequently updated columns without a stable tiebreaker.

2. Exclusive bounds

• To prevent duplicates, use strict comparisons:
  • Descending: WHERE (created_at, id) < ($last_created_at, $last_id)
  • Ascending: WHERE (created_at, id) > ($last_created_at, $last_id)

3. Opaque cursors

• Treat cursors as implementation details. Encode the last sort keys and any relevant query context.
• Base64url-encode a compact JSON, protobuf, or binary struct. Example payload:

{
  "v": 1,
  "sk": {"created_at": "2026-03-14T10:32:59Z", "id": "1234"},
  "dir": "desc",
  "flt": "hash-of-filter-and-sort" ,
  "exp": 1741970000
}

• Sign or encrypt the token to prevent tampering (e.g., HMAC-SHA256 over the payload). Reject tokens whose filter-hash mismatches current parameters.

4. Include filters in the cursor context

• A cursor generated for one filter/sort must not be reused with different parameters. Embed a fingerprint of filters/sort and validate on read.

5. Provide both “next” and “prev” when helpful

• To build a prev cursor, seek in the opposite direction using the first item of the current page, then reverse the results:

-- Building a prev page for DESC order
SELECT *
FROM items
WHERE (created_at, id) > ($first_created_at, $first_id)
ORDER BY created_at ASC, id ASC
LIMIT $limit; -- then reverse in application code

6. Make cursors short‑lived

• Optional but helpful for evolving schemas. Use expirations to limit long‑lived tokens that reference stale sort keys.

Response shape and HTTP details

A clear, predictable envelope improves DX.

{
  "items": [/* ... */],
  "page": {
    "limit": 20,
    "next": "eyJ2IjoxLCJzayI6eyJjcmVhdGVkX2F0IjoiMjAyNi0wMy0xNFQxMDozMjo1OVoiLCJpZCI6IjEyMzQifX0",
    "prev": null
  }
}

• Include a top-level items array and a page object with limit and opaque cursors.
• Optionally, add HTTP Link headers for discoverability:

Link: <.../items?limit=20&cursor=...>; rel="next"

• Avoid returning a “total count” on every request for very large tables. If needed, provide an approximate count endpoint or an eventually consistent metric.

Database and storage patterns

• PostgreSQL/MySQL: Create a composite index matching your ORDER BY, e.g. (created_at DESC, id DESC). Use keyset predicates (< or >) with exclusive bounds.
• MongoDB: Use the natural _id (which embeds time in ObjectId) or a dedicated indexed field. Seek with {_id: {$lt: last_id}} for descending order.
• Elasticsearch/OpenSearch: Prefer search_after over from/size for deep pagination.
• DynamoDB/Cassandra: Use the native paging/cursor primitives or maintain a synthetic sort key.

Common pitfalls and how to avoid them

• Non-unique ordering: ORDER BY updated_at DESC without an id tie-breaker can create duplicates or omissions between pages. Always add a unique key to the ORDER BY.
• Random ordering: ORDER BY RANDOM() is incompatible with stable cursors.
• Mutable sort keys: If you sort by a field that frequently changes (e.g., score), items can “move backward.” Prefer created_at for stable browsing; if you must sort by score, accept that items may repeat and document the behavior.
• Missing indexes: Keyset pagination needs a covering index. Without it, you’ll still scan.
• Changing filters midstream: If the client changes filters, ignore any provided cursor and start from the first page.
• Time zones: Store and compare timestamps in UTC. Serialize ISO-8601 with Z.

Security and privacy considerations

• Do not trust client-provided cursors. Validate signatures and context-hashes. Reject malformed/expired tokens.
• Avoid leaking internal identifiers if sensitive. Either encrypt the cursor or include a one-way mapping.
• Enforce maximum limit caps server-side to prevent abuse (e.g., max 500 records per page).

Choosing between cursor and offset

Use this quick decision guide:

• Choose offset when:

  • You need random access to page N (e.g., PDF-like browsing).
  • Data volumes and offsets are small.
  • Simplicity and CDN cacheability outweigh occasional inconsistencies.

• Choose cursor when:

  • Lists can grow large or “infinite.”
  • You expect heavy writes while users/page-scrapers read.
  • You care about fast, consistent forward/backward traversal.

Often, APIs support both. Example: offset for small admin UIs; cursor for public, high-scale endpoints.

Implementation sketch (Node.js + SQL)

Simplified example using Express and PostgreSQL with keyset pagination.

import { Request, Response } from "express";
import { Pool } from "pg";
import { decodeCursor, encodeCursor, hmacVerify } from "./cursor";

const pool = new Pool();

export async function listItems(req: Request, res: Response) {
  const limit = Math.min(Number(req.query.limit) || 20, 200);
  const cursorB64 = (req.query.cursor as string) || null;

  let where = "";
  let params: any[] = [];
  let order = "ORDER BY created_at DESC, id DESC";

  if (cursorB64) {
    const c = decodeCursor(cursorB64);
    if (!hmacVerify(c) || c.exp < Date.now() / 1000) {
      return res.status(400).json({ error: "Invalid cursor" });
    }
    where = "WHERE (created_at, id) < ($1, $2)";
    params = [c.sk.created_at, c.sk.id];
  }

  const sql = `
    SELECT id, created_at, title
    FROM items
    ${where}
    ${order}
    LIMIT ${limit + 1}; -- fetch one extra to detect next
  `;

  const { rows } = await pool.query(sql, params);

  const hasNext = rows.length > limit;
  const slice = rows.slice(0, limit);
  const next = hasNext
    ? encodeCursor({ sk: { created_at: slice[slice.length - 1].created_at, id: slice[slice.length - 1].id }, exp: Math.floor(Date.now()/1000) + 3600 })
    : null;

  res.json({ items: slice, page: { limit, next, prev: null } });
}

Key points

• Fetch limit + 1 rows to detect whether a next page exists without an extra COUNT.
• Encode only the last visible row’s sort keys.
• Sign and expire the cursor to prevent tampering and reduce long-lived coupling.

Handling updates, deletes, and soft-deletes

• Soft-deletes: Add a filter (is_deleted = false) and include it in the cursor’s filter-hash.
• Updates that change sort keys: If you sort by updated_at, items may reappear. Document that behavior, or sort by created_at for stable browsing and expose separate endpoints for “recently updated.”
• Late arrivals (clock skew): When relying on client-supplied timestamps, store server-side created_at to ensure consistent ordering.

Counting and “last page” semantics

Total counts are expensive at scale. Consider:

• Provide a separate count endpoint with caching or approximation.
• Return a has_more boolean inferred from limit + 1 fetches.
• For “go to last page,” expose a reverse-sorted endpoint and a first-page cursor in that direction instead of computing an exact last page.

Caching and rate limits

• Offset URLs are ideal for shared caches; cursor URLs are less cache-friendly because tokens change per user and per traversal.
• You can still cache item blobs by ID and build pages dynamically.
• Enforce rate limits on page fetches; small limits reduce per-request work and memory pressure.

Testing checklist

• Large offset performance: Verify latency and query plans as offsets grow.
• Concurrency: Simulate inserts/deletes between sequential page fetches. Ensure no duplicates or gaps with exclusive bounds.
• Determinism: Confirm ORDER BY includes a unique tiebreaker and matches your index.
• Filter drift: Ensure cursors are rejected if filters/sort differ.
• Backward navigation: Validate prev logic with reversed queries and result reversal.
• Edge rows: Test pages with exactly limit rows and with zero results.

Migration strategy (offset → cursor)

• Add cursor pagination alongside existing offset params. If both are provided, prefer cursor and return a warning header.
• Document deprecation timelines and provide examples.
• Keep offset for small, internal endpoints; mandate cursor for public, high-volume ones.

Summary

• Offset pagination is simple and fine for small datasets and random access.
• Cursor pagination scales better, avoids O(offset) scans, and is more consistent under writes.
• The keys to robust cursors are deterministic ordering, exclusive bounds, opaque signed tokens, and indexes that match your ORDER BY.

Pick the model that matches your scale and UX, and don’t hesitate to offer both when your audience spans dashboards and consumer feeds alike.