API — Deep OFFSET Pagination Times Out

Fix list endpoints that degrade as OFFSET grows, understand why the database still scans skipped rows, and switch to keyset pagination with stable cursors.

intermediatedebuggingapipaginationdatabases

Symptoms

GET /items?offset=500000 times out while offset=100 responds in milliseconds
Response latency grows linearly (or worse) with the offset value
Clients walking to the end of large collections trigger slow-query alarms

Possible causes

OFFSET forces the database to generate and discard all skipped rows before returning the page
The ORDER BY column lacks a supporting index, adding a sort on top of the skip
Deep crawlers (export jobs, scrapers) walk page by page to the end of millions of rows
Non-deterministic ordering (missing unique tiebreaker) makes pages drift at depth

Diagnosis steps

Measure latency versus offset

for o in 1000 100000 500000; do curl -s -o /dev/null -w "offset=$o %{time_total}s\n" "https://api.example.com/items?offset=$o"; done

Interpretation

Linear growth of latency with offset is the OFFSET scan signature; flat latency at any depth is the keyset signature.

Look at the query plan at depth

EXPLAIN ANALYZE SELECT * FROM items ORDER BY created_at DESC OFFSET 500000 LIMIT 50;

Interpretation

The plan shows rows fetched far beyond the 50 returned — the discard cost is visible as rows removed or scanned counts.

Find the crawlers

grep "offset=" access.log | awk -F'offset=' '{print $2}' | sort -n | uniq -c | tail -5

Interpretation

Which clients reach extreme depths determines whether you also need an export API instead of paginated walking.

Root cause

OFFSET does not skip work: the database must produce (or walk an index through) all preceding rows before returning the requested page. Cost therefore grows with the offset value, and deep pages on large tables exceed any reasonable request budget.

Fix

Use keyset (cursor) pagination — WHERE (sort_col, id) < (last_value, last_id) ORDER BY sort_col DESC, id DESC LIMIT n with a composite index — and expose opaque cursors to clients. For export use cases, provide a dedicated export or streaming API instead of walking pages.

Risk note

Keyset pagination cannot jump to an arbitrary page number. Products requiring jump-to-page need hybrid designs or accept the cost consciously.

Verify the fix

Latency at any depth matches page-one latency, database scans stay bounded to page size, and export jobs complete through the dedicated API.

Symptom walkthrough

The latency curve is the diagnosis. Page one: 12ms. offset=100000: 900ms. offset=500000: timeout. The reason is visible in the plan:

EXPLAIN ANALYZE SELECT * FROM items
ORDER BY created_at DESC OFFSET 500000 LIMIT 50;

Limit  (rows=50) (actual time=8412.114)
  ->  Index Scan Backward using items_created_idx
        rows=500050                <- half a million rows produced
                                   to discard 500000 of them

The database cannot skip: it must walk the index through every preceding row before it can start your page. Deep crawlers (export jobs walking to the end) convert this arithmetic into slow-query alarms.

Fix in practice

Keyset pagination — the cursor is the last row sort key, and the index does the skipping:

-- page 1
SELECT id, created_at, ... FROM items
ORDER BY created_at DESC, id DESC
LIMIT 50;

-- next page: WHERE the row sorts strictly after the cursor
SELECT id, created_at, ... FROM items
WHERE (created_at, id) < ($last_created, $last_id)
ORDER BY created_at DESC, id DESC
LIMIT 50;

-- supporting index:
CREATE INDEX items_created_id ON items (created_at DESC, id DESC);

Expose it as an opaque cursor so clients cannot forge internals:

GET /items?limit=50
{ "data": [...], "next_cursor": "eyJjIjoxNzI5NTg0MDAwLCJpZCI6ODgxMn0=" }
GET /items?cursor=eyJj...&limit=50

The id tiebreaker matters: without it, rows sharing created_at drift between pages at depth, producing duplicates and gaps that look like data loss.