Deep OFFSET and Query Performance

Pagination with LIMIT and OFFSET is one of the most common patterns in application development — easy to understand, easy to implement, and works perfectly in the early stages. But there’s one characteristic rarely evaluated before a system goes to production: OFFSET performance isn’t linear. Every time a user advances to the next page, the database doesn’t “jump” to the desired position — it re-reads all previous rows and discards them. On page 100 with 20 items per page, the database processes 1,980 rows just to throw them away. On page 50,000, the database processes a million rows to return twenty.

How the Database Executes OFFSET

To understand why deep offsets are a problem, you need an accurate mental model of what the database actually does when it receives a query with OFFSET.

Take this query as an example:

SELECT *
FROM orders
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;

Intuitively, we expect the database to “jump” to row 100,001 and immediately fetch 20 rows from there. But that’s not what happens. The actual execution steps are as follows.

flowchart TD
    A[Query received by the database] --> B[Scan rows matching the WHERE]
    B --> C[Sort all results per the ORDER BY]
    C --> D["Read and count rows one by one\nUntil reaching the OFFSET"]
    D --> E["Discard the first 100,000 rows\n(already read, not returned)"]
    E --> F["Fetch the next 20 rows\nper the LIMIT"]
    F --> G[Return to the client]

    style E fill:#ffebee,stroke:#e53935
    style D fill:#fff3e0,stroke:#fb8c00

The key is in steps four and five: the skipped rows are still read and processed. The database has no mechanism to truly “skip over” rows — it must count its position one by one. That means OFFSET 100000 forces the database to process 100,020 rows to return 20.

There’s no load difference between OFFSET 100000 LIMIT 20 and LIMIT 100020 in terms of the number of rows read — the only difference is the first 100,000 rows are discarded before being returned to the client.

Why Performance Isn’t Linear

This is the most dangerous characteristic of offset-based pagination: the workload grows with the offset value, not with the page size. The page size stays constant, but every page gets more expensive.

flowchart LR
    subgraph Page1["Page 1 (OFFSET 0)"]
        direction TB
        R1["Read 20 rows ✓"]
    end

    subgraph Page2["Page 100 (OFFSET 1980)"]
        direction TB
        R2["Read + discard 1980 rows\nReturn 20 rows"]
    end

    subgraph Page3["Page 1000 (OFFSET 19980)"]
        direction TB
        R3["Read + discard 19,980 rows\nReturn 20 rows"]
    end

    subgraph Page4["Page 50000 (OFFSET 999980)"]
        direction TB
        R4["Read + discard 999,980 rows\nReturn 20 rows"]
    end

    style R2 fill:#fff3e0,stroke:#fb8c00
    style R3 fill:#ffe0b2,stroke:#ef6c00
    style R4 fill:#ffebee,stroke:#e53935

In more concrete numbers:

On the last page of a large dataset, the database spends almost all its energy processing rows that never reach the client.

The Impact of ORDER BY

The combination of ORDER BY and OFFSET worsens the situation further. If the sorted column has no index, the database must do a full sort of the entire result set before it can start counting the offset. This means:

• Large memory usage (or temp files on disk if the result set exceeds the buffer)
• Unstable query plans as data grows
• Performance that can change drastically when data distribution changes

Even if the ORDER BY column has an index, the index only helps the sorting process — it doesn’t eliminate the need to read the skipped rows.

Production Impacts That Often Go Undetected

Deep offset problems are almost never detected during development because of the small data volume. A database with 10,000 rows responds to OFFSET 5000 in milliseconds. The same database with 50 million rows responds to OFFSET 5,000,000 in seconds — or doesn’t respond at all due to query timeouts.

This creates a dangerous failure pattern: the system works perfectly for months, then suddenly starts showing performance symptoms whose source is unclear as data grows.

sequenceDiagram
    participant U as User
    participant API as API Server
    participant DB as Database

    Note over DB: 6 months later, data has reached 50 million rows

    U->>API: GET /orders?page=1
    API->>DB: SELECT ... OFFSET 0 LIMIT 20
    DB-->>API: ✓ 8ms
    API-->>U: 200 OK (fast)

    U->>API: GET /orders?page=50000
    API->>DB: SELECT ... OFFSET 999980 LIMIT 20
    Note over DB: Processing ~1 million rows...
    DB-->>API: ✓ 28,000ms (28 seconds)
    API-->>U: 504 Gateway Timeout

    Note over API,DB: Alert comes in: "Latency spike with unclear source"

The symptoms that appear in production usually aren’t an easily diagnosable “slow query” — but a series of confusing secondary effects: database CPU rising for no clear reason, connection pool exhaustion, other services sharing the database getting affected too, and latency alerts firing at certain hours when users tend to scroll far back through pages.

Anti-Pattern: Unlimited Offset Pagination

-- ✗ Anti-pattern 1: OFFSET without a maximum limit
-- Users are allowed to access any page via the parameter
GET /api/orders?page=50000&limit=20

SELECT *
FROM orders
ORDER BY created_at DESC
LIMIT 20 OFFSET 999980;
-- Consequence: the query can process millions of rows, high timeout risk

-- ✗ Anti-pattern 2: OFFSET for data export / bulk processing
-- Exporting all data by looping offset pagination
page = 0
while True:
    results = query(f"SELECT * FROM orders LIMIT 1000 OFFSET {page * 1000}")
    if not results: break
    process(results)
    page += 1
-- Consequence: each iteration gets slower; iteration 1000 processes 1 million rows

-- ✗ Anti-pattern 3: COUNT(*) + OFFSET for total pages
SELECT COUNT(*) FROM orders;  -- full scan to count the total
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 0;  -- second query
-- Consequence: two expensive queries on every page load; COUNT(*) alone is already slow
--        on large tables without the right index

Solution 1: Keyset Pagination (Cursor-Based)

Keyset pagination replaces the concept of “page N” with the concept of “data after this value”. Instead of saying “skip the first 100,000 rows”, you say “fetch data whose created_at is smaller than the last value you’ve already seen”.

-- ✓ First request: no cursor, fetch from the start
SELECT id, created_at, amount, status
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- The response includes a cursor from the last item:
-- { last_created_at: "2025-01-15 10:30:00", last_id: 84521 }

-- ✓ Next request: use the cursor from the previous response
SELECT id, created_at, amount, status
FROM orders
WHERE (created_at, id) < ('2025-01-15 10:30:00', 84521)
ORDER BY created_at DESC, id DESC
LIMIT 20;

The database doesn’t need to read any rows before the cursor — it directly looks up the right position using the index. This query’s performance is identical on the first page or the millionth page.

flowchart TD
    subgraph Offset["OFFSET Pagination"]
        A1[Page 1] -->|"read 20 rows"| R1[20 rows]
        A2[Page 100] -->|"read + discard 1980, fetch 20"| R2[20 rows]
        A3[Page 50000] -->|"read + discard 999980, fetch 20"| R3[20 rows]
        style A3 fill:#ffebee,stroke:#e53935
    end

    subgraph Cursor["Cursor Pagination"]
        B1[Page 1] -->|"WHERE id < MAX → read 20"| S1[20 rows]
        B2[Page 100] -->|"WHERE id < cursor → read 20"| S2[20 rows]
        B3[Page 50000] -->|"WHERE id < cursor → read 20"| S3[20 rows]
        style B1 fill:#e8f5e9,stroke:#43a047
        style B2 fill:#e8f5e9,stroke:#43a047
        style B3 fill:#e8f5e9,stroke:#43a047
    end

Cursor Pagination with a Monotonic ID

For tables whose primary key monotonically increases (integer or UUID v7), the implementation is simpler:

-- ✓ Next page after the last id = 84521
SELECT id, title, created_at
FROM products
WHERE id < 84521
ORDER BY id DESC
LIMIT 20;

-- ✓ Previous page (backward navigation)
SELECT id, title, created_at
FROM products
WHERE id > 84521
ORDER BY id ASC
LIMIT 20;

The index on id (which already exists as the primary key) is used to its full potential — no wasted rows, no pointless scans.

The Trade-offs of Cursor Pagination

Cursor pagination is indeed more efficient, but there are limitations to understand before deciding to use it.

Solution 2: Late Row Lookup

For cases where offset pagination is truly required (for example an admin panel with random page navigation), there’s an optimization technique called late row lookup or deferred join. The idea: do the scanning and sorting only on indexed columns, then join to the main table to fetch the other columns.

-- ✗ Without late row lookup: fetch all columns while scanning
SELECT id, user_id, created_at, amount, status, notes, metadata
FROM orders
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;
-- The database reads all columns (including large ones) for 100,020 rows

-- ✓ With late row lookup: scan first, then join
SELECT o.*
FROM orders o
JOIN (
    SELECT id
    FROM orders
    ORDER BY created_at DESC
    LIMIT 20 OFFSET 100000
) AS paged ON o.id = paged.id;
-- The subquery only reads the id column (index-only scan if available)
-- JOIN to the main table only for the 20 rows actually needed

This technique doesn’t eliminate the deep offset problem, but can significantly reduce I/O load because the scanning happens on a smaller index than the full table.

Solution 3: Limiting Pagination Depth

For public APIs using offset pagination, one of the most pragmatic approaches is setting a hard maximum limit.

-- Validate at the application layer before the query executes
MAX_OFFSET = 10000

if offset > MAX_OFFSET:
    return error("Use filters or search to access deeper data")

-- The query only executes if the offset is within safe limits
SELECT *
FROM orders
WHERE user_id = :user_id
ORDER BY created_at DESC
LIMIT :limit OFFSET :offset;

If users need to access data far back, direct them to search or date-range filter features — which are far more efficient because they can use indexes directly.

Solution 4: Snapshots for Reporting

For reporting needs or large historical data exports, paginating over raw data is simply not the right approach. Use a materialized view or a snapshot table refreshed periodically.

-- Create a daily snapshot for reporting
CREATE MATERIALIZED VIEW orders_daily_summary AS
SELECT
    DATE(created_at) AS order_date,
    status,
    COUNT(*) AS total_orders,
    SUM(amount) AS total_amount
FROM orders
GROUP BY DATE(created_at), status;

-- Fast reporting queries that don't depend on the main table's size
SELECT *
FROM orders_daily_summary
WHERE order_date BETWEEN '2025-01-01' AND '2025-01-31'
ORDER BY order_date DESC;

With this approach, reporting queries never touch the ever-growing orders table — they only read a much smaller, pre-aggregated snapshot.

Decision Tree: Choosing a Pagination Strategy

flowchart TD
    A{Does the user need to\njump to random pages?} -- Yes --> B{What's the estimated\nmaximum offset?}
    A -- No --> C["✓ Use cursor pagination\n(ideal for infinite scroll,\nfeeds, or sequential browsing)"]

    B -- "< 10,000 rows" --> D["✓ Offset pagination with\na maximum limit\n+ late row lookup if needed"]
    B -- "> 10,000 rows" --> E{What's the purpose of\ndeep data access?}

    E -- "Reporting / analytics" --> F["✓ Use a materialized view\nor snapshot table"]
    E -- "Search / filter" --> G["✓ Add mandatory filters\n(date ranges, status, etc.)\nbefore pagination"]
    E -- "Data export" --> H["✓ Use streaming\nor async batch jobs\nnot pagination"]

Pagination Implementation Checklist

BEFORE IMPLEMENTATION:
  □ Identify whether users need random page navigation
    or just prev/next (sequential browsing)
  □ Estimate data volume in 1 and 3 years ahead
  □ Determine whether this is a public API (high traffic) or
    an internal tool (low traffic, can tolerate more)

IF USING OFFSET PAGINATION:
  □ A maximum offset limit enforced at the application layer
  □ The ORDER BY column has an index
  □ Late row lookup applied for tables with large columns
  □ Per-page query time monitoring (not just averages)

IF USING CURSOR PAGINATION:
  □ Cursor is encoded (base64 or signed token) so it can't be manipulated
  □ The column used as the cursor has an index
  □ The cursor column combination is unique (add id as a tie-breaker)
  □ API response includes next_cursor and has_more
  □ Cursor has a reasonable expiry time

FOR REPORTING / EXPORT:
  □ No offset pagination for datasets > 100,000 rows
  □ Materialized view or snapshot refreshed per the need frequency
  □ Large exports use background jobs, not synchronous requests

Summary

• OFFSET doesn’t “jump” — it reads and discards. Every row before the OFFSET value is still processed by the database, even though it never reaches the client. OFFSET 1,000,000 processes more than a million rows to return twenty.
• Offset pagination performance isn’t linear — every page costs more. On page 50,000 with 20 items per page, the database discards 99.998% of all the work it does.
• This problem isn’t detected in development because data volumes are small — it only appears in production after data grows, in the form of latency spikes, high CPU, and confusing query timeouts.
• Cursor/keyset pagination is the main solution: instead of “skip N rows”, use WHERE id < cursor. Performance is identical on the first page or the millionth.
• Late row lookup can reduce the impact of deep offsets if offset pagination is truly required — scan only the index, join the main table only for the returned rows.
• Limit pagination depth for public APIs with a hard cap (for example a maximum offset of 10,000), and direct deeper needs toward filter or search features.
• Reporting and export must not use offset pagination on large data — use materialized views, periodic snapshots, or streaming background jobs.