Designing Production-Grade API Pagination: Patterns, Tools, and Anti-Patterns

Modern APIs must efficiently serve datasets ranging from hundreds to millions of records without breaking client performance, reliability, or cost constraints. Poor pagination design leads to slow queries, unpredictable user experience, and even security risks—especially at scale. Let’s break down the patterns, anti-patterns, tools, and real configs I use to implement production-grade pagination in cloud-native APIs.

What Is API Pagination? (With Real Config Example)

Pagination in APIs means splitting large result sets into smaller, manageable chunks that a client can request page-by-page. This is critical for REST, GraphQL, and gRPC interfaces that expose collection endpoints (e.g., /users, /orders).

A common REST API pagination scheme uses limit and offset query parameters:

# OpenAPI 3.0 snippet for paginated endpoint
paths:
  /users:
    get:
      summary: Get paginated users
      parameters:
        - in: query
          name: limit
          schema:
            type: integer
            default: 50
            maximum: 200
        - in: query
          name: offset
          schema:
            type: integer
            default: 0
      responses:
        '200':
          description: Paginated user results
          content:
            application/json:
              schema:
                type: object
                properties:
                  items:
                    type: array
                    items:
                      $ref: '#/components/schemas/User'
                  total:
                    type: integer
                  nextOffset:
                    type: integer

Many production APIs (Stripe, GitHub, Twitter) use cursor- or keyset-based approaches for efficiency and consistency. Pagination isn’t just a frontend concern: it deeply impacts database design, cache effectiveness, and even security posture.

Key insight: Pagination is not just a UX feature—it’s a fundamental scalability and security primitive in API architecture.

Why Offset Pagination Fails at Scale

Offset Pagination: The Classic Pattern

Offset pagination uses integers to skip a number of rows before returning a limited page. For example, GET /users?limit=100&offset=200 returns users 201-300.

Major Problems at Scale

1. Query performance: SQL queries like SELECT * FROM users ORDER BY id LIMIT 100 OFFSET 100000 require the database to scan and discard the first 100,000 rows, causing query latency (often >1 second beyond 100k rows on RDS/Postgres/MySQL).
2. Inconsistent results: If records are inserted or deleted between paged requests, items can be skipped or duplicated.
3. Inefficient caching: Offset pagination keys are not stable across data changes, reducing cache hit rates in Redis, Varnish, or CDN layers.

Example: MySQL Query Plan

EXPLAIN SELECT * FROM users ORDER BY id LIMIT 100 OFFSET 100000;

This returns rows_examined: 100100, meaning the DB engine reads and skips 100,000 rows before serving the page—devastating for large tables.

Key insight: Offset-based pagination is simple but doesn’t scale for high-volume, frequently updated datasets.

Step 1: Choose the Right Pagination Pattern For Your API

1. Offset Pagination

• Best for: Small datasets (<10,000 rows), static or rarely changing collections.
• Drawbacks: Slow on large tables, inconsistent with updates, poor cacheability.

2. Cursor Pagination

• Best for: Medium to large datasets, high consistency requirements, event or time-ordered records.
• How it works: Use a stable, opaque cursor (e.g. an encoded record ID or timestamp). Example: GET /orders?limit=100&cursor=eyJpZCI6MTAwMH0
• Benefits: Fast DB queries (WHERE id > ?), stable ordering, good cacheability.

3. Keyset Pagination

• Best for: Large, append-only or immutable datasets, real-time feeds.
• How it works: Paginate based on the value of a unique, indexed column (e.g. created_at, id).
• Drawbacks: Harder for arbitrary sorting or jumping to a specific page.

4. Seek Pagination (for Complex Sort Orders)

• Best for: Compound indexes, multi-column sorts (e.g. ORDER BY created_at, id).
• How it works: Encode the last tuple of the previous page as the cursor.

Key insight: Cursor or keyset pagination is almost always superior for production APIs—especially for mutable, high-traffic data.

Step 2: Implementing Cursor Pagination (with Examples)

1. Encode Cursors Securely

Cursors should be opaque and tamper-proof. I use base64-encoded JSON objects, optionally signed (e.g. with HMAC-SHA256 and a server secret). Example in Node.js (v18+):

const crypto = require('crypto');
function encodeCursor(obj, secret) {
  const json = JSON.stringify(obj);
  const sig = crypto.createHmac('sha256', secret).update(json).digest('hex');
  return Buffer.from(`${json}|${sig}`).toString('base64');
}
function decodeCursor(b64, secret) {
  const [json, sig] = Buffer.from(b64, 'base64').toString().split('|');
  const expected = crypto.createHmac('sha256', secret).update(json).digest('hex');
  if (sig !== expected) throw new Error('Invalid cursor');
  return JSON.parse(json);
}

2. Querying with Cursor in SQL (PostgreSQL 14+)

-- Fetch next page after id=5000
SELECT * FROM users WHERE id > 5000 ORDER BY id ASC LIMIT 100;

3. RESTful Response Example

{
  "items": [ ... ],
  "next_cursor": "eyJpZCI6NTAwMH0=",
  "has_more": true
}

4. Handling Edge Cases

• If the client provides an invalid/expired cursor, respond with HTTP 400.
• For backwards pagination, store both before and after cursors.
• Document response shape and cursor format in OpenAPI/Swagger.

Key insight: Secure, opaque cursors minimize client errors and mitigate data tampering or scraping attacks.

Step 3: Designing API Contracts for Pagination (REST and GraphQL)

RESTful Pagination Contracts

• Always return next_cursor (or previous_cursor) in the response.
• Include a has_more or total_count field only if it’s efficient (total counts are expensive in large tables—cache or avoid if possible).
• Set a maximum limit (e.g., 200) to avoid abuse.
• Document error cases for invalid cursors or exceeded limits.

OpenAPI 3.1 Example (with Cursor)

paths:
  /orders:
    get:
      summary: List orders with cursor pagination
      parameters:
        - in: query
          name: limit
          schema:
            type: integer
            default: 50
            maximum: 200
        - in: query
          name: cursor
          schema:
            type: string
      responses:
        '200':
          description: Paginated list of orders
          content:
            application/json:
              schema:
                type: object
                properties:
                  items:
                    type: array
                    items:
                      $ref: '#/components/schemas/Order'
                  next_cursor:
                    type: string
                  has_more:
                    type: boolean

GraphQL Relay-Style Pagination (Apollo v4+)

Relay’s connection model is the de facto standard: edges, pageInfo, and opaque cursors.

type UserConnection {
  edges: [UserEdge!]!
  pageInfo: PageInfo!
}
type UserEdge {
  node: User!
  cursor: String!
}
type PageInfo {
  endCursor: String
  hasNextPage: Boolean!
}

In Apollo Server, use @connection directive or implement cursor logic in resolvers.

Key insight: Well-documented, consistent API contracts reduce integration bugs and client confusion.

Step 4: Preventing Pagination Abuse and Security Risks

1. Enforce Limits and Rate Limits

• Always set a hard maximum on limit (e.g., 100–500 depending on use case).
• Combine pagination with API rate limiting (e.g., with Envoy, Kong, or NGINX).

2. Scraping and Data Leakage

• Opaque, signed cursors make it harder to enumerate or scrape records sequentially.
• For sensitive data, add secondary authorization checks per page.

3. Denial of Service (DoS) Mitigation

• Reject requests with invalid or expired cursors (HTTP 400/422).
• Throttle repeated requests for the same page/cursor.
• Use caching (CloudFront, Fastly, or Redis) for hot paginated queries.

4. Audit and Monitor Pagination Usage

• Log cursor usage and pagination parameters in your observability stack (Datadog, Grafana Loki, or ELK).
• Set up alerts for anomalous paging patterns (e.g., repeated requests for all first pages, unusual offsets).

Key insight: Pagination is a common vector for abuse; enforce limits and apply standard API security controls to every paginated endpoint.

Step 5: Testing and Monitoring Paginated APIs in Production

1. Load Testing Pagination Endpoints

• Use k6 or Artillery (v2+) to simulate high-concurrency pagination requests.
• Test with both valid and malformed cursors/offsets.

2. Synthetic Monitoring

• Monitor latency and error rates for paginated endpoints (e.g., SLO: 95th percentile latency <250ms for /users page requests).
• Use synthetics (Datadog Synthetics, Pingdom) to detect regressions.

3. Observability

• Instrument endpoints with request/response metrics (Prometheus, OpenTelemetry).
• Track median and tail latencies for each pagination type.
• Monitor DB query plans for slow scans or table scans on paginated queries.

4. Regression Testing

• Write integration tests that walk through forward and backward pagination, validate duplicate/skip handling, and cursor encoding/decoding.

Key insight: Production-grade pagination requires both functional and performance testing—especially as dataset size and access frequency grow.

Comparison Table: Offset vs. Cursor vs. Keyset Pagination

Pattern	Pros	Cons	When to Use
Offset	Simple, easy for clients	Slow for large tables, inconsistent	Small/static datasets
Cursor	Fast, consistent, scalable	Harder for random access, opaque to client	Large/mutable datasets
Keyset/Seek	Best for big/append-only data	Complex for multi-field sort, no jumping	Feeds, real-time, big event logs

Key insight: Most production APIs should default to cursor or keyset pagination unless you have strong reasons for offset.

Frequently Asked Questions

Q: Which pagination method is best for large, frequently changing datasets? A: Cursor pagination is usually best for large, dynamic datasets because it avoids slow offset scans and provides consistency even when data is updated between requests.

Q: How can I prevent API pagination abuse or scraping? A: Use opaque, signed cursors, enforce hard limit caps, and combine pagination with standard API rate limiting and monitoring for unusual access patterns.

Q: Should my API response always include a total_count field? A: Only include total_count if it’s cheap to compute or can be efficiently cached; on large datasets, counting can cause performance bottlenecks.

Key Takeaways

• Offset pagination is easy, but cursor/keyset pagination is vastly superior for scale and consistency.
• Always make cursors opaque and tamper-proof using encoding and cryptographic signatures.
• Document pagination contracts clearly for clients, including error cases and parameter limits.
• Enforce strict API and pagination limits to prevent abuse or scraping.
• Monitor paginated endpoints for latency, errors, and unusual patterns as your data grows.
• Test both functionality and performance for every paginated endpoint, especially under load.