Cursor Pagination vs Offset Pagination: Production API Guide

Cursor pagination vs offset pagination: the practical decision

Offset pagination asks the database to skip a number of rows before returning the page. It maps naturally to numbered pages and is easy to explain. Cursor pagination asks for rows after or before a known position in a stable ordering. The database can seek from that boundary instead of walking through every preceding result.

PostgreSQL explicitly notes that rows skipped by OFFSET still have to be computed and that LIMIT needs a predictable ORDER BY. See the current PostgreSQL LIMIT and OFFSET documentation.

Offset remains a valid tool

A production design does not ban offsets. They work well when result sets are bounded, writes are infrequent, page numbers are a genuine requirement, and a realistic worst-case offset performs acceptably. Always include a unique ordering:

SELECT id, created_at, total_cents
FROM orders
WHERE tenant_id = $1 AND status = $2
ORDER BY created_at DESC, id DESC
LIMIT $3 OFFSET $4;

Start with a total, stable order

Pagination fails when ordering is ambiguous. Sorting only by created_at is not enough because multiple records can share the same timestamp. Add an immutable, unique tiebreaker such as the primary key: ORDER BY created_at DESC, id DESC. PostgreSQL does not guarantee row order without an explicit sort, as its ORDER BY documentation explains.

• Keep cursor keys non-null. Row comparisons involving nulls have special semantics. Prefer non-null sort fields or define and test explicit null ordering.
• Prefer immutable cursor keys. If a record's sort key changes during traversal, it can move across the boundary and appear twice or be missed.
• Make every supported sort mode explicit. “Newest”, “highest value”, and “name” are different contracts and normally need different cursor payloads and indexes.
• Keep collation consistent. Text ordering must not change between the encoded cursor and the query that consumes it.

PostgreSQL compares row constructors from left to right, which makes (created_at, id) < (cursor_time, cursor_id) a concise keyset boundary. The behavior and null caveats are documented under row constructor comparisons.

Design the PostgreSQL query and index together

Assume an orders endpoint always scopes results to the authenticated tenant and a status filter, then sorts newest first. Put equality predicates first in the B-tree index and the ordered cursor keys after them:

CREATE TABLE orders (
  id uuid PRIMARY KEY,
  tenant_id uuid NOT NULL,
  status text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  total_cents bigint NOT NULL CHECK (total_cents >= 0)
);

CREATE INDEX CONCURRENTLY orders_tenant_status_created_id_idx
  ON orders (tenant_id, status, created_at DESC, id DESC);

The query and index must agree on tenant, filter, ordering and tiebreaker. PostgreSQL can use a matching B-tree to serve ORDER BY ... LIMIT without sorting the full eligible set; the official indexes and ordering guide describes this behavior. Leading-column constraints also determine how effectively a multicolumn B-tree narrows a scan, so read the multicolumn index guidance before adding permutations for every filter.

Use the last returned row as the next boundary

-- First page
SELECT id, created_at, total_cents
FROM orders
WHERE tenant_id = $1 AND status = $2
ORDER BY created_at DESC, id DESC
LIMIT $3;

-- Later pages: values come from the last row already returned
SELECT id, created_at, total_cents
FROM orders
WHERE tenant_id = $1
  AND status = $2
  AND (created_at, id) < ($3::timestamptz, $4::uuid)
ORDER BY created_at DESC, id DESC
LIMIT $5;

Fetch pageSize + 1 rows. Return only pageSize; the extra row tells the API whether a next page exists without a separate count. The cursor should describe the last item actually returned, not the look-ahead row.

Make the cursor opaque, versioned and tamper-evident

A cursor is an API token, not merely a base64-encoded row ID. Include only what the server needs to resume the exact query: a schema version, ordered key values, a normalized-filter fingerprint, and—when appropriate—an expiry. Base64url is encoding, not encryption; never put secrets or unnecessary personal data in the payload.

Signing prevents clients from changing cursor fields unnoticed. It does not replace authorization and it does not hide the payload. Node.js provides HMAC and constant-time comparison primitives in its current crypto documentation.

import { createHmac, timingSafeEqual } from "node:crypto";

type CursorPayload = {
  v: 1;
  createdAt: string;
  id: string;
  filterHash: string;
  expiresAt: number;
};

const key = Buffer.from(
  process.env.CURSOR_SIGNING_KEY_BASE64 ?? "",
  "base64"
);

if (key.length < 32) {
  throw new Error("CURSOR_SIGNING_KEY_BASE64 must decode to at least 32 bytes");
}

export function encodeCursor(payload: CursorPayload): string {
  const body = Buffer.from(JSON.stringify(payload)).toString("base64url");
  const signature = createHmac("sha256", key)
    .update(body)
    .digest("base64url");

  return body + "." + signature;
}

export function decodeCursor(token: string): CursorPayload {
  const [body, signature, extra] = token.split(".");
  if (!body || !signature || extra || !/^[A-Za-z0-9_-]+$/.test(signature)) {
    throw new Error("Invalid cursor");
  }

  const supplied = Buffer.from(signature, "base64url");
  const expected = createHmac("sha256", key).update(body).digest();
  if (supplied.length !== expected.length || !timingSafeEqual(supplied, expected)) {
    throw new Error("Invalid cursor");
  }

  const value: unknown = JSON.parse(
    Buffer.from(body, "base64url").toString("utf8")
  );
  if (!isCursorPayload(value) || value.expiresAt < Date.now()) {
    throw new Error("Invalid or expired cursor");
  }

  return value;
}

The omitted isCursorPayload validator should reject unknown versions, malformed UUIDs, invalid timestamps, unexpected fields, and oversized input before the cursor reaches SQL. Compute filterHash from a canonical representation of every query option that affects membership or ordering. If the current request's fingerprint differs, reject the cursor instead of silently changing its meaning.

Implement the typed Node.js endpoint

Resolve tenantId from the authenticated server-side identity, not from the cursor or a freely editable query parameter. Parameterize all SQL values, cap the page size, return a consistent response shape, and generate the next cursor only when another row exists.

type OrderRow = {
  id: string;
  created_at: Date;
  total_cents: string;
};

const pageSize = Math.min(Math.max(requestedSize, 1), 100);
const cursor = token ? decodeCursor(token) : undefined;

// FIRST_PAGE_SQL and NEXT_PAGE_SQL are the parameterized queries above.
const result = cursor
  ? await pool.query<OrderRow>(NEXT_PAGE_SQL, [
      tenantId,
      status,
      cursor.createdAt,
      cursor.id,
      pageSize + 1,
    ])
  : await pool.query<OrderRow>(FIRST_PAGE_SQL, [
      tenantId,
      status,
      pageSize + 1,
    ]);

const hasNextPage = result.rows.length > pageSize;
const items = result.rows.slice(0, pageSize);
const last = items.at(-1);

return {
  items,
  pageInfo: {
    hasNextPage,
    nextCursor: hasNextPage && last
      ? encodeCursor({
          v: 1,
          createdAt: last.created_at.toISOString(),
          id: last.id,
          filterHash,
          expiresAt: Date.now() + CURSOR_TTL_MS,
        })
      : null,
  },
};

For a REST API, a compact response can expose items and pageInfo. A GraphQL API can use the same storage logic behind the standardized edges, cursors, and pageInfo shape in the GraphQL Cursor Connections Specification. Keep transport vocabulary separate from the database implementation so the storage layer can evolve.

If you are implementing this in Next.js, apply the validation, error handling and observability practices in our production guide to Next.js API route handlers. For a broader backend engagement, Endurance Softwares supports Node.js API development and integration and full-stack application engineering.

Define what happens while data changes

Cursor pagination does not freeze the dataset. It changes the traversal boundary. With newest-first pagination, records inserted ahead of the first page normally will not disrupt later pages, but deletes can shorten a page and updates to ordered fields can still cause duplicates or omissions.

A high-watermark limits membership but does not solve every mutation. If an ordered value itself changes, the row can move. For business-critical traversal, either sort by immutable fields or define versioned membership explicitly.

Backward pagination

For a “previous page”, invert the boundary and query direction, take pageSize + 1, then reverse the returned slice so the public order stays consistent. Do not simply reverse the client array while reusing the forward comparator. Test the first, middle, and last boundaries with tied timestamps.

Total counts

Do not make every cursor page pay for an exact count unless the product needs it. Counts and page contents can also describe different moments under concurrent writes. Consider a separate count endpoint, an explicitly approximate count, or no count for sequential feeds. Label the semantics honestly.

Security boundaries for paginated APIs

• Authorize every page query. A valid cursor is not proof that the caller may read its referenced records.
• Bind the boundary to filters. Prevent a cursor issued for one status, sort mode or search from being replayed against another.
• Never trust tenant identity from the token. Derive it from the authenticated principal and keep it in the SQL predicate.
• Cap token and page sizes. Reject oversized cursor strings and excessive limits before expensive parsing or database work.
• Return minimal fields. Pagination must not become a route around field-level authorization.
• Rotate signing keys deliberately. Support an explicit key identifier or a short overlap window if cursors must survive rotation.
• Avoid cursor logging. Log a safe error category and version, not reusable tokens or sensitive filter contents.

OWASP classifies missing per-object authorization as a leading API risk. Its Broken Object Level Authorization guidance is directly relevant when cursors carry record identifiers.

Validate the query plan, contract and failure behavior

Do not infer performance from SQL shape alone. Use representative data distributions and realistic filters in a safe environment. Compare shallow and deep offsets with the keyset query, and inspect the actual plan:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, total_cents
FROM orders
WHERE tenant_id = '00000000-0000-0000-0000-000000000001'
  AND status = 'paid'
  AND (created_at, id) < (
    '2026-08-20T08:00:00Z'::timestamptz,
    '00000000-0000-0000-0000-000000000099'::uuid
  )
ORDER BY created_at DESC, id DESC
LIMIT 51;

Look for the intended index, bounded rows read for the page, an accurate tenant and filter condition, and no unexpected full sort. If a filter combination does not justify a composite index, measure whether a different query path or product constraint is better than adding another write-amplifying index.

Contract tests that catch pagination bugs

• Insert many rows with identical timestamps and verify every ID appears exactly once.
• Traverse while inserting newer rows; confirm the documented live-feed or high-watermark behavior.
• Delete the boundary row after page one and verify page two remains valid.
• Change a mutable sort key and verify the documented limitation or versioned behavior.
• Replay a cursor with different filters, sort order, user and tenant.
• Tamper with each payload field and signature; return a stable client error without leaking internals.
• Test expired, truncated, oversized, unsupported-version and non-base64url tokens.
• Test page sizes of zero, one, the maximum and above the maximum.
• Verify forward and backward traversal around the same boundary.

Observe the endpoint in production

Track request latency, database time, page size, direction, cursor-validation failures, empty-page rate and query timeouts by route and normalized filter family. Do not use raw cursor tokens or high-cardinality IDs as metric labels. Pair telemetry with slow-query evidence before changing indexes.

Migrate from offsets without breaking clients

1. Add the supporting index using the safe production process for your PostgreSQL environment.
2. Introduce cursor fields additively while keeping the existing offset contract.
3. Document ordering, expiry, invalid-cursor errors, maximum page size and consistency semantics.
4. Shadow or sample both query paths and compare ordered IDs for stable datasets.
5. Move selected clients to cursors behind an API version or capability flag.
6. Monitor query plans, latency, duplicates, invalid-token errors and support feedback.
7. Deprecate offsets only if the product no longer needs numbered navigation.

Schema and index rollout need their own operational plan. Our PostgreSQL zero-downtime migration guide covers expand-contract changes, concurrent indexes and validation. If the broader API contract is evolving, use the compatibility practices in the Node.js API versioning guide.

Common cursor pagination mistakes

• Using a non-unique sort such as ORDER BY created_at without a tiebreaker.
• Encoding only an ID when the actual order depends on several fields.
• Treating base64 as encryption or trusting an unsigned cursor.
• Leaving filters and sort mode out of the signed cursor contract.
• Putting the cursor predicate after data retrieval instead of in SQL.
• Using a single-column index that cannot support the tenant, filter and ordering pattern.
• Claiming snapshot consistency without a high-watermark, materialized result or versioned dataset.
• Running an exact total count on every page without a product requirement.
• Exposing database errors for malformed or expired cursors.
• Forcing cursor pagination onto a UI whose core task is jumping to numbered pages.

Cursor pagination FAQ

Is keyset pagination the same as cursor pagination?

Keyset pagination is the database technique of seeking from ordered values. Cursor pagination is the API contract that packages the resume position into an opaque token. A cursor can use keyset pagination underneath; not every opaque cursor necessarily does.

Are UUIDs enough for a cursor?

Only when the UUID itself defines the complete public ordering, which is uncommon. If the endpoint sorts by creation time, priority or score, the cursor must represent that ordered tuple plus a unique tiebreaker.

Can a cursor be stored in a URL?

Yes, when it uses a URL-safe encoding and does not contain sensitive data. Keep it reasonably short, treat it as opaque client state, and avoid exposing reusable tokens to analytics or logs.

Should cursors expire?

Expiry is a product and operational decision. Short-lived feeds can reject old query state; long-running exports may need durable checkpoints. If cursors expire, document the error and a clear restart path.

Production API pagination checklist