API Pagination at Scale: Why OFFSET 100,000 is a Database Killer

Designing a paginated API seems simple. Standard frameworks make it trivial: just use LIMIT 20 OFFSET 100. This works perfectly during development and for the first few pages of small tables. However, once your data scales and users or web scrapers scroll deep into your dataset — reaching page 5,000 or deeper — your database performance will fall off a cliff.

OFFSET is a database killer. In high-concurrency environments, pagination design dictates whether your database survives heavy traffic or grinds to a halt.

This playbook provides a comprehensive guide to pagination architectures at MANG scale.

System Requirements and Goals

To build a high-performance paginated API, we must analyze system requirements against physical constraints.

Functional Requirements

1. Forward/Backward Traversal: Allow clients to retrieve consecutive pages of records seamlessly.
2. Deterministic Navigation: Prevent skipping or duplicating rows when new items are active inserted/deleted while the user is actively reading.
3. Complex Sorting: Support ordering data by non-unique fields (e.g. price, user_rating) in combination with unique tie-breakers.
4. Deep Pagination: Support infinite scrolling down to millions of records without increasing query latency.

Non-Functional Requirements

1. Sub-Millisecond Execution: Pagination queries must resolve within 5ms (P99) regardless of the page depth.
2. Low Storage & Compute Overhead: Avoid full table scans, sorting operations in memory, or heavy temporary files in database storage.
3. Stable URI/State: Support lightweight stateless requests (no stateful cursor sessions maintained on server memory).
4. Bandwidth Efficiency: Minimize payload size by returning lightweight cursors that are easily serialized.

High-Level Design Architecture

When a query uses OFFSET, the database engine cannot magically jump to the 100,000th row. It must perform a linear scan.

Below is a comparison diagram illustrating the execution paths of Offset-based vs. Keyset/Cursor-based pagination:

graph TD
    subgraph Offset Pagination (Linear Scan)
        O_Req[Request: OFFSET 10000 LIMIT 10] --> O_Scan[Scan first 10,010 rows from Disk]
        O_Scan --> O_Sort[Sort in DB memory / temp file]
        O_Sort --> O_Discard[Discard first 10,000 rows]
        O_Discard --> O_Result[Return 10 rows]
    end
    
    subgraph Cursor Pagination (O(log N) Index Lookup)
        C_Req[Request: AFTER cursor_value LIMIT 10] --> C_Index[Jump to cursor using B-Tree Index]
        C_Index --> C_Result[Retrieve next 10 rows from Index]
    end

Why Offset Scales Horizontally Poorly

Under Offset pagination, query execution costs scale linearly ($O(N)$) with the page depth. At OFFSET 100000, the DB reads, parses, and sorts 100,000 rows, only to throw them away. This wastes CPU cycles and fills buffer caches with useless page reads, choking other transactions.

In contrast, Cursor-based (Keyset) Pagination leverages database B-tree indexes to jump directly to the starting point of the next page in $O(\log N)$ logarithmic time, keeping query times completely flat.

API Design and Paginated Contracts

An API's pagination strategy defines its client contract. Let us compare the endpoint designs.

1. Offset-based API (Anti-Pattern at Scale)

• Endpoint: GET /v1/products?limit=20&page=500
• Response:

{
  "total_records": 100000,
  "current_page": 500,
  "limit": 20,
  "data": [ ... ]
}

2. Cursor-based API (Production Standard)

Instead of indices, the API returns an opaque string cursor indicating where the next page begins.

• Endpoint: GET /v1/products?limit=20&starting_after=eyJjcmVhdGVkX2F0IjoiMjAyNi0wNS0yMlQxNzozMDowMFoiLCJpZCI6NDU2fQ==
• Response:

{
  "data": [
    {
      "id": 456,
      "name": "Mechanical Keyboard",
      "price": 99.99,
      "created_at": "2026-05-22T17:30:00Z"
    }
  ],
  "paging": {
    "has_more": true,
    "next_cursor": "eyJjcmVhdGVkX2F0IjoiMjAyNi0wNS0yMlQxNzozMDowMFoiLCJpZCI6NDU2fQ=="
  }
}

Low-Level Design & Database Schema

Implementing cursor pagination requires designing matching database indexes. Let's look at the database implementation details.

1. Keyset Pagination SQL Queries

If sorting by a unique column like id ascending:

-- Keyset Query: Extremely fast O(log N)
SELECT id, name, price 
FROM products
WHERE id > 456
ORDER BY id ASC
LIMIT 20;

For this query to execute in $O(\log N)$, we need a simple index:

CREATE UNIQUE INDEX idx_products_id ON products(id);

2. Compound Sorting and Tie-Breakers

If sorting by a non-unique column like price ascending, we must introduce id as a unique tie-breaker to ensure a stable ordering:

-- Multi-column keyset comparison
SELECT id, name, price
FROM products
WHERE (price, id) > (99.99, 456)
ORDER BY price ASC, id ASC
LIMIT 20;

This requires a composite index to avoid sorting in memory:

CREATE INDEX idx_products_price_id ON products(price ASC, id ASC);

3. Node/TypeScript Cursor Encoding Implementation

Here is the low-level utility class in TypeScript to encode and decode keyset pagination cursors dynamically:

import { Buffer } from 'buffer';

interface KeysetCursor {
  sortValue: string | number;
  id: number;
}

export class PaginationCursor {
  /**
   * Encodes keyset values into a secure Base64 opaque cursor string
   */
  public static encode(sortValue: string | number, id: number): string {
    const payload: KeysetCursor = { sortValue, id };
    return Buffer.from(JSON.stringify(payload)).toString('base64');
  }

  /**
   * Decodes an opaque cursor back into concrete query predicates
   */
  public static decode(cursor: string): KeysetCursor {
    try {
      const decodedStr = Buffer.from(cursor, 'base64').toString('utf8');
      const parsed = JSON.parse(decodedStr);
      if (parsed.sortValue === undefined || !parsed.id) {
        throw new Error('Malformed cursor payload');
      }
      return parsed as KeysetCursor;
    } catch (err) {
      throw new Error('Invalid pagination cursor format');
    }
  }
}

Scaling Challenges & OFFSET Database Killing

Why does OFFSET kill the database? It degrades disk I/O, lock contention, and buffer memory.

1. The Buffer Pool Pollution Problem

When executing large offsets (e.g. OFFSET 500000), the database engine reads thousands of disk blocks into the database's Buffer Pool (RAM cache).

• The System Impact: This evicts active, high-value pages (such as primary index B-trees or hot user records) out of the cache.
• The Outcome: Subsequent transactions experience sudden disk I/O reads, causing cascading latency spikes across the entire application stack.

2. High-Frequency Page Drift (Unstable Reads)

If a user is reading a feed using offset-based pagination:

• Case 1: Concurrent Inserts: If a new product is added to page 1 while the user moves from page 1 to page 2, the last item on page 1 shifts to page 2. The user sees the same item twice.
• Case 2: Concurrent Deletes: If an item on page 1 is deleted, all subsequent items shift up. The user skips a record entirely.

sequenceDiagram
    autonumber
    participant Client as User Browser
    participant API as API Server
    participant DB as PostgreSQL

    Note over Client, DB: Offset Drift (Duplicate items)
    Client->>API: GET /products?limit=2&page=1
    API->>DB: LIMIT 2 OFFSET 0
    DB-->>Client: Returns [Item A, Item B]
    
    Note over DB: Background Worker inserts Item New at top
    DB->>DB: Table state shifts [Item New, Item A, Item B, Item C]
    
    Client->>API: GET /products?limit=2&page=2
    API->>DB: LIMIT 2 OFFSET 2
    DB-->>Client: Returns [Item B, Item C] (Item B seen twice!)

Cursor-based pagination is completely immune to page drift because it requests items directly after a specific anchor ID (WHERE id > B), ignoring state changes upstream.

Technical Trade-offs: Offset vs. Keyset vs. Cursor

Selecting a pagination model involves critical functional trade-offs:

Failure Scenarios and Data Mutability

Operating cursor pagination at scale requires handling specific database edge cases.

1. The Null-value Sort Failure

If sorting by a column containing nullable values (e.g., shipped_at which is NULL for active orders), a standard keyset query WHERE shipped_at > '2026-05-22' fails to handle rows with NULL correctly. Resilience Strategy:

• Configure columns used in sorting as NOT NULL, or enforce default values. If nullable fields are mandatory, use multi-column fallback queries like WHERE (shipped_at IS NOT NULL AND shipped_at > :val) OR (shipped_at IS NULL AND id > :id) and ensure matches exist in matching composite indexes.

2. Multi-tenant Scatter-Gather Overloads

If a tenant-sharded database executes cursor queries that span multiple nodes without checking routing rules, it forces the application gateway to collect pages from all shards, sort them in application memory, and construct a synthetic cursor. Resilience Strategy:

• Force the shard routing key (e.g., tenant_id or user_id) to be a mandatory query parameter, ensuring the paginated query only touches a single database shard.

Staff Engineer Perspective

Verbal Script & Mock Interview

Here is an architectural walkthrough simulating a Senior Staff Systems Architect design interview:

Interviewer: "We are experiencing severe database CPU spikes on our activity log tables. The logs page uses offset pagination. How would you solve this at scale?"

Candidate: "The CPU spikes are a direct symptom of offset-based pagination. When clients request page 10,000 using LIMIT 20 OFFSET 200000, the database engine is forced to execute a full index scan, reading and sorting 200,020 rows from disk, only to discard the first 200,000 and return the final 20. This wastes buffer pool memory, evicts hot cached blocks, and causes high disk I/O.

To solve this, I would deprecate offset pagination and migrate to Cursor-based (Keyset) Pagination. Instead of telling the database how many rows to skip, we query for records sequentially from the exact point the previous page ended.

For example, if sorting by creation time, we capture the created_at timestamp and unique id of the last record on the screen. We package these into a Base64-encoded string, which we return as an opaque next_cursor in the API response metadata. During the subsequent page request, the client transmits this cursor. The middleware decodes it, extracting the predicates to execute a keyset query: WHERE (created_at, id) > (:prev_created_at, :prev_id) ORDER BY created_at ASC, id ASC LIMIT 20.

By creating a composite B-tree index on (created_at, id), the database engine performs an $O(\log N)$ index lookup, jumping directly to the matching row without scanning prior records. This keeps page load times completely flat at sub-5 milliseconds, even if a user is browsing through hundreds of millions of activity logs.

Finally, this pattern completely eliminates page drift, preventing duplicate or skipped items caused by concurrent background inserts, which provides a significantly smoother user experience."

Interviewer: "Excellent. What if the product manager insists on supporting arbitrary page jumps, like skipping directly from page 1 to page 45?"

Candidate: "I would first challenge the product requirement using telemetry. In almost all production environments, arbitrary page jumps are a legacy UI pattern. Users rarely skip to page 45; they either search, filter, or scroll sequentially. If arbitrary page jumps are mandatory, I would propose a hybrid Bucket-based Pagination. We divide the dataset into large pre-computed buckets of 1,000 items. We allow offset-based jumps only within the bounds of a single bucket, while navigating across buckets requires cursor anchors. This caps the maximum database scan cost to a maximum of 1,000 rows, preserving query performance while satisfying the product requirement."

Production Readiness Checklist

Ensure the following criteria are verified before promoting the paginated API suite to production:

• Logarithmic Performance: Query latency verified to remain flat (<5ms) down to 1 million records.
• Composite Indexing: Matching (sort_column, tie_breaker) indexes verified to be actively used by EXPLAIN ANALYZE.
• Cursor Integrity: Base64 serialization verified with unit tests checking null, boundary, and malformed inputs.
• Drift Resilience: Cursor stability validated under heavy concurrent insert/delete simulations.
• Query Cost Analysis: Safeguards implemented blocking clients from sending requests without key-selectivity filters.

Read Next

• TLA+ for Backend Devs: Formally Verifying Distributed Systems
• Building Production Observability with OpenTelemetry and Grafana Stack
• Linearizability vs. Sequential Consistency: A Developer's Guide to Correctness