Offset vs Cursor Pagination

Selecting the wrong pagination strategy is a contract decision that cannot be reversed without a breaking change. Offset pagination (LIMIT/OFFSET) positions results by absolute row count; cursor pagination positions them by an opaque token that encodes the last-seen record boundary. The two models produce different OpenAPI schemas, different database query shapes, and different client iteration contracts — and mixing them after launch forces a versioned deprecation cycle. Part of the Query Patterns & Data Shaping Strategies reference.

This page defines both contracts precisely, walks through server-side and client-side implementation, and shows how to enforce the choice in CI so downstream SDK consumers are never surprised.

Strategy Selection: What Goes Wrong Without It

The failure mode for choosing offset pagination on a high-throughput feed is concrete: as OFFSET grows, the database must scan and discard an ever-larger prefix of the result set — OFFSET 10000 LIMIT 20 reads 10,020 rows to return 20. Simultaneously, concurrent writes shift row positions, so page 3 can include a record already returned on page 2 or silently skip one.

Cursor pagination avoids both problems. The server encodes the boundary of the last returned record (typically a composite of the sort key and primary key) into an opaque token. The next query uses a WHERE keyset predicate that the database resolves with an index seek, independent of how many earlier records exist. The trade-off is that the client loses random access to an arbitrary page number.

Before writing any OpenAPI, map the decision to your dataset characteristics. Align this with the Sorting & Multi-Field Ordering contract: cursor tokens encode the active sort keys, so changing the sort contract invalidates all outstanding cursors.

Visual: How Each Strategy Traverses a Result Set

Spec Definition

Define the two strategies as mutually exclusive schemas in OpenAPI 3.1. Using oneOf with a discriminator enforces the contract at the gateway without requiring runtime branching logic in application code.

# openapi/components/pagination.yaml — OpenAPI 3.1.0
components:
  parameters:
    PaginationStrategy:
      in: query
      name: strategy
      required: true
      schema:
        oneOf:
          - $ref: '#/components/schemas/OffsetParams'
          - $ref: '#/components/schemas/CursorParams'
        discriminator:
          propertyName: strategy
          mapping:
            offset: '#/components/schemas/OffsetParams'
            cursor: '#/components/schemas/CursorParams'

  schemas:
    OffsetParams:
      type: object
      required: [strategy, offset, limit]
      properties:
        strategy:
          type: string
          enum: [offset]
        offset:
          type: integer
          minimum: 0
          description: Zero-based row offset. Avoid values above 10000 in production.
        limit:
          type: integer
          minimum: 1
          maximum: 100
          default: 20

    CursorParams:
      type: object
      required: [strategy, limit]
      properties:
        strategy:
          type: string
          enum: [cursor]
        cursor:
          type: string
          pattern: '^[A-Za-z0-9_-]+$'
          description: >
            URL-safe Base64-encoded keyset token. Omit on the first request.
        limit:
          type: integer
          minimum: 1
          maximum: 100
          default: 20

    PaginatedResponse:
      type: object
      required: [items, has_more]
      additionalProperties: false
      properties:
        items:
          type: array
          items: {}
        next_cursor:
          type: string
          nullable: true
          description: Present and non-null when has_more is true.
        has_more:
          type: boolean
        total_count:
          type: integer
          nullable: true
          description: >
            Omit for cursor strategy. Only include for offset on small datasets
            where COUNT(*) is cheap.

RFC and Standard Alignment

Implementation Walkthrough: Server Side

Step 1 — Offset endpoint (Node.js / Express)

Offset is straightforward. The critical discipline is capping limit, rejecting negative offset, and not returning total_count when the table is large.

// src/handlers/list-resources.ts
import { Request, Response } from 'express';
import { z } from 'zod';
import { db } from '../db';

const OffsetQuery = z.object({
  strategy: z.literal('offset'),
  offset: z.coerce.number().int().min(0).max(50_000),
  limit: z.coerce.number().int().min(1).max(100).default(20),
});

export async function listResourcesOffset(req: Request, res: Response) {
  const query = OffsetQuery.parse(req.query);

  const rows = await db.query<Resource>(
    `SELECT id, name, created_at
       FROM resources
      ORDER BY created_at DESC, id DESC
      LIMIT $1 OFFSET $2`,
    [query.limit + 1, query.offset],   // fetch one extra to detect has_more
  );

  const has_more = rows.length > query.limit;
  const items = rows.slice(0, query.limit);

  res.json({ items, has_more });
}

Step 2 — Cursor endpoint (Node.js / Express + PostgreSQL keyset)

Cursor generation encodes the sort key and primary key into a URL-safe Base64 token. The next query uses a row-value predicate that PostgreSQL resolves with an index scan.

// src/handlers/list-resources-cursor.ts
import { Request, Response } from 'express';
import { z } from 'zod';
import { db } from '../db';

const CursorQuery = z.object({
  strategy: z.literal('cursor'),
  cursor: z.string().regex(/^[A-Za-z0-9_-]+$/).optional(),
  limit: z.coerce.number().int().min(1).max(100).default(20),
});

interface CursorPayload {
  created_at: string;   // ISO-8601 UTC
  id: string;           // UUID tiebreaker
}

function encodeCursor(payload: CursorPayload): string {
  return Buffer.from(JSON.stringify(payload)).toString('base64url');
}

function decodeCursor(raw: string): CursorPayload {
  return JSON.parse(Buffer.from(raw, 'base64url').toString('utf8'));
}

export async function listResourcesCursor(req: Request, res: Response) {
  const query = CursorQuery.parse(req.query);
  const limit = query.limit;

  let rows: Resource[];

  if (query.cursor) {
    const pos = decodeCursor(query.cursor);
    // Keyset predicate: uses composite index on (created_at DESC, id DESC)
    rows = await db.query<Resource>(
      `SELECT id, name, created_at
         FROM resources
        WHERE (created_at, id) < ($1, $2)
        ORDER BY created_at DESC, id DESC
        LIMIT $3`,
      [pos.created_at, pos.id, limit + 1],
    );
  } else {
    rows = await db.query<Resource>(
      `SELECT id, name, created_at
         FROM resources
        ORDER BY created_at DESC, id DESC
        LIMIT $1`,
      [limit + 1],
    );
  }

  const has_more = rows.length > limit;
  const items = rows.slice(0, limit);
  const last = items[items.length - 1];

  const next_cursor = has_more && last
    ? encodeCursor({ created_at: last.created_at, id: last.id })
    : null;

  res.json({ items, has_more, next_cursor });
}

The (created_at DESC, id DESC) composite index must exist for this to be an index seek rather than a sequential scan. Add it during schema migration:

CREATE INDEX CONCURRENTLY idx_resources_cursor
  ON resources (created_at DESC, id DESC);

For a complete PostgreSQL walkthrough including covering indexes, null handling, and multi-tenant isolation, see Implementing Cursor-Based Pagination with PostgreSQL.

Implementation Walkthrough: Client Side

TypeScript — async iterator wrapper

// src/sdk/paginate.ts
import { z } from 'zod';

const PageSchema = z.object({
  items: z.array(z.unknown()),
  next_cursor: z.string().nullable(),
  has_more: z.boolean(),
});

/**
 * Iterates a cursor-paginated endpoint, yielding individual items.
 * Stops automatically when has_more is false or next_cursor is null.
 */
export async function* paginate<T>(
  fetchPage: (cursor?: string) => Promise<unknown>,
  initialCursor?: string,
): AsyncIterableIterator<T> {
  let cursor = initialCursor;

  while (true) {
    const raw = await fetchPage(cursor);
    const page = PageSchema.parse(raw);

    yield* page.items as T[];

    if (!page.has_more || !page.next_cursor) break;
    cursor = page.next_cursor;
  }
}

// Usage
for await (const resource of paginate<Resource>(
  (cursor) => apiFetch('/v1/resources', { strategy: 'cursor', cursor }),
)) {
  console.log(resource.id);
}

Python — generator with Pydantic v2 validation

# sdk/paginate.py
from __future__ import annotations
from typing import Any, Callable, Generator
import base64, json
from pydantic import BaseModel, computed_field

class CursorPage(BaseModel):
    items: list[dict[str, Any]]
    next_cursor: str | None = None
    has_more: bool

    @computed_field
    @property
    def has_next_page(self) -> bool:
        return self.has_more and self.next_cursor is not None

    @classmethod
    def decode_cursor(cls, raw: str) -> dict[str, Any]:
        # Add padding stripped during encoding
        padded = raw + "=" * (-len(raw) % 4)
        return json.loads(base64.urlsafe_b64decode(padded))


def paginate(
    fetch_page: Callable[[str | None], dict],
    initial_cursor: str | None = None,
) -> Generator[dict, None, None]:
    cursor = initial_cursor
    while True:
        page = CursorPage.model_validate(fetch_page(cursor))
        yield from page.items
        if not page.has_next_page:
            break
        cursor = page.next_cursor


# Usage
for resource in paginate(lambda c: api_get("/v1/resources", cursor=c)):
    print(resource["id"])

Edge-Case Handling

Concurrent deletes between pages. If a record is deleted after the cursor was issued, the keyset query simply skips it without error — the result set shrinks by one item. This is usually acceptable; document it in the API contract so clients do not assume total item counts are stable.

Timestamp collisions. When created_at is a millisecond-precision timestamp, multiple records inserted in the same millisecond share the same sort key value. Without the id tiebreaker, the keyset predicate is ambiguous and can skip or repeat records. Always include a unique column as the final sort key.

Timezone normalization. Store and compare timestamps in UTC. A cursor that encodes created_at in a local timezone will produce wrong results if the server’s locale changes or spans a DST boundary.

Backward pagination. The keyset predicate above only supports forward traversal (WHERE (created_at, id) < cursor). Reverse pagination requires flipping the comparison operator and the sort direction, then reversing the result slice before returning it. Define both after and before cursor parameters in the OpenAPI spec if the client needs bidirectional traversal.

Maximum offset cap. Even when offering offset mode for admin UIs, cap OFFSET at a hard limit (e.g., 50,000) enforced in both the OpenAPI schema and server middleware. An unbounded offset is a denial-of-service vector on large tables.

Interplay with Advanced Filtering Operators. When a filter narrows the result set, the cursor must encode the sort key values of the last record within that filtered set, not the global table. If the filter changes between requests, the cursor is invalid — return a 400 Bad Request with a machine-readable error body following the RFC 7807 problem+JSON standard.

Validation and Testing

Spectral rules

# .spectral.yaml
rules:
  cursor-encoding-format:
    description: "Cursor tokens must match URL-safe Base64 pattern."
    given: "$.components.schemas.CursorParams.properties.cursor"
    severity: error
    then:
      field: pattern
      function: pattern
      functionOptions:
        match: "^[A-Za-z0-9_-]+$"

  limit-maximum-present:
    description: "Limit parameter must declare a maximum of 100 or less."
    given: "$.components.schemas.*.properties.limit"
    severity: error
    then:
      field: maximum
      function: schema
      functionOptions:
        schema:
          type: integer
          maximum: 100

  pagination-response-required-fields:
    description: "Pagination responses must include items and has_more."
    given: "$.components.schemas.PaginatedResponse"
    severity: error
    then:
      field: required
      function: schema
      functionOptions:
        schema:
          type: array
          contains: { enum: ["items", "has_more"] }

GitHub Actions contract gate

# .github/workflows/pagination-contract.yaml
name: Pagination Contract Validation
on:
  pull_request:
    paths: ['openapi/**', '.spectral.yaml']

jobs:
  validate:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - name: Install tooling
        run: npm install -g @stoplight/spectral-cli openapi-diff @stoplight/prism-cli

      - name: Lint pagination schema
        run: spectral lint openapi/components/pagination.yaml --ruleset .spectral.yaml

      - name: Check for breaking changes
        run: openapi-diff openapi/base.yaml openapi/components/pagination.yaml --fail-on-breaking

      - name: Start mock server and run contract tests
        run: |
          prism mock openapi/components/pagination.yaml &
          sleep 2
          # Cursor: first page (no cursor)
          curl -sf "http://localhost:4010/v1/resources?strategy=cursor&limit=10" | \
            jq -e '.has_more != null and .items != null'
          # Cursor: second page (dummy cursor token)
          curl -sf "http://localhost:4010/v1/resources?strategy=cursor&limit=10&cursor=dGVzdA" | \
            jq -e '.has_more != null'
          # Offset
          curl -sf "http://localhost:4010/v1/resources?strategy=offset&offset=0&limit=10" | \
            jq -e '.items | length <= 10'

Pagination stability test (Python + pytest)

This test detects cursor drift under concurrent inserts:

# tests/test_pagination_stability.py
import pytest, uuid, threading
from sdk.paginate import paginate
from tests.fixtures import api_client, db_session

def test_cursor_pagination_no_duplicates_under_concurrent_inserts(api_client, db_session):
    """Ensure concurrent inserts do not produce duplicate or skipped items."""
    seen_ids: set[str] = set()
    duplicates: list[str] = []

    # Start background writer
    stop = threading.Event()
    def writer():
        while not stop.is_set():
            db_session.execute(
                "INSERT INTO resources (id, name) VALUES ($1, $2)",
                [str(uuid.uuid4()), "concurrent-write"],
            )

    t = threading.Thread(target=writer, daemon=True)
    t.start()

    try:
        for item in paginate(lambda c: api_client.get("/v1/resources", cursor=c)):
            if item["id"] in seen_ids:
                duplicates.append(item["id"])
            seen_ids.add(item["id"])
    finally:
        stop.set()

    assert not duplicates, f"Cursor drift produced duplicate records: {duplicates}"

SDK Generation Impact

How the pagination schema surfaces in generated clients depends critically on the OpenAPI definitions above.

TypeScript (openapi-typescript + Zod): The oneOf discriminator generates a union type OffsetParams | CursorParams. Client code must narrow the union before passing parameters. The next_cursor: string | null field generates correctly as a nullable string — avoid nullable: true in OpenAPI 2.0, which some generators emit as any.

// Generated type (excerpt)
export type PaginatedResponse = {
  items: unknown[];
  next_cursor: string | null;
  has_more: boolean;
  total_count?: number | null;
};

Python (openapi-generator): additionalProperties: false on PaginatedResponse prevents generated Pydantic models from silently accepting undocumented fields — important for catching server-side response drift early.

Go (oapi-codegen): The pattern constraint on cursor generates a validation call in the request struct. Without it, the generated client sends arbitrary strings to the server, bypassing the encoding contract.

// Generated validation (excerpt)
var cursorPattern = regexp.MustCompile(`^[A-Za-z0-9_-]+$`)

func (p *CursorParams) Validate() error {
  if p.Cursor != nil && !cursorPattern.MatchString(*p.Cursor) {
    return fmt.Errorf("cursor: invalid format")
  }
  return nil
}

When upgrading from offset to cursor pagination, bump the API minor version and emit a Deprecation header on offset endpoints to give SDK consumers a migration window before removal.

Anti-Patterns

Frequently Asked Questions

Related

• Up: Query Patterns & Data Shaping Strategies
• Implementing Cursor-Based Pagination with PostgreSQL — keyset queries, covering indexes, and multi-tenant isolation in depth
• Sorting & Multi-Field Ordering — sort contracts that cursor tokens must encode
• Advanced Filtering Operators — combining filters with pagination without invalidating cursors
• RFC 7807 problem+json Implementation — structured error responses for invalid or expired cursor tokens