Pagination at Scale: Why OFFSET and SKIP Will Eventually Break Your API

Pagination looks solved when you first ship: LIMIT and OFFSET (or skip() in MongoDB), and the frontend shows ten items per page. Page 1 is fast. Page 2 is fast. Then the table grows to millions of rows, and deep pages start timing out. The index is fine. The problem is that OFFSET is fundamentally wrong for scale. This log explains what the database actually does when you skip rows, and how cursor-based pagination fixes it.

What OFFSET and SKIP actually do

When you run something like:

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

you might assume the engine “jumps” to row 100,000 and returns the next 10. It does not. Relational and document stores do not have a direct “row number” index. To satisfy that query, the engine has to:

1. Fetch at least 100,010 rows (offset + limit).
2. Sort them (if not already ordered by the index).
3. Load that result set (or a large chunk of it) into memory.
4. Drop the first 100,000 rows.
5. Return the remaining 10.

So every “next page” does more work. Page 1 reads 10 rows. Page 10,000 reads 100,000 rows. Cost grows with the offset. In practice that is O(N) in the offset: as N grows, latency and memory use grow until the database runs out of resources or times out.

OFFSET vs cursor: one query, two execution worlds

graph LR
  subgraph Offset["OFFSET pagination"]
    A1[Page 5 request] --> B1[Scan 50 rows, drop 40]
    A2[Page 5000 request] --> B2[Scan 50000 rows, drop 49990]
  end

  subgraph Cursor["Cursor pagination"]
    C1[last_id = 12345] --> D1[B+ tree seek to 12345, read next 10]
    C2[last_id = 9999999] --> D2[B+ tree seek to 9999999, read next 10]
  end

With OFFSET, the amount of work scales with the page depth. With cursor-based pagination, you pass a “where to start” value (e.g. last seen id), and the database uses the index to seek and then read a fixed number of rows. Work stays constant regardless of how far “down” the list the user is.

The fix: cursor-based (keyset) pagination

Stop asking the database to skip N rows. Ask it to start after this key.

• The client sends the last seen identifier (e.g. id or a unique sort key), not a page number.
• The server uses it in a range condition and a fixed LIMIT.

Example:

-- Cursor: "give me the next 10 orders after id 12345"
SELECT * FROM orders
WHERE id < 12345
ORDER BY id DESC
LIMIT 10;

If id is the primary key (or part of the sort), the planner uses the B+ tree:

1. Seek to the leaf where id = 12345 (or the predecessor) in O(log N).
2. Read the next 10 leaf nodes in order.
3. Return those 10 rows.

No scanning 100,000 rows. No giant sort buffer. Fetch cost is effectively O(1) in the depth of the list. Latency stays flat whether the user is on “page” 1 or “page” 1,000,000.

Comparison

Takeaway: Use cursor-based pagination for any list that can grow large. Reserve OFFSET for small, admin-style result sets where you truly need random access by page number.

Trade-offs of cursor pagination

1. No absolute page numbers
   You cannot reliably show “Page 45 of 100” without counting the whole set (expensive). You can offer “Next” / “Previous” or infinite scroll, and optionally an approximate count (e.g. “about 1.2M results”) if your storage supports it cheaply.

2. Strict sort key
   The cursor column (or composite key) must be unique and ordered. If you sort by a non-unique column (e.g. status), two rows can share the same value and the cursor can skip or duplicate rows. Use something like (created_at, id) or id so every row has a distinct position.

3. Direction
   ”Previous” is the same idea in reverse: pass the first seen key and use WHERE id > first_seen_id ORDER BY id ASC LIMIT 10.

At scale, these trade-offs are usually acceptable. Users rarely need to jump to page 454; they do need the API to respond in under a few hundred milliseconds.

The Google Search compromise: pre-calculated cursors

When you propose cursor-based pagination, product or design may push back. They want “Page 1 of 10” at the bottom of the screen and the illusion of absolute navigation. You do not have to go back to OFFSET to give them that. You can use the same idea Google Search uses: they rarely let you jump to page 150; they only expose the next handful of pages. You can do the same with pre-calculated cursors.

How it works

1. First page (items 1–10)
   Do not fetch only 10 rows. Run a single, cheap query that fetches the next 50 IDs (or 5 pages worth) using an index-only scan. No full row reads yet.

2. Return to the client

   • The first 10 full records (for the current page).
   • An array of cursors: the IDs at positions 10, 20, 30, 40, 50. The frontend maps these to buttons for Pages 2, 3, 4, 5, and 6.

3. When the user clicks “Page 4”
   The frontend sends the cursor for page 4 (e.g. the 30th ID) back to the API. The API runs a normal cursor query: WHERE id < :cursor ORDER BY id DESC LIMIT 10. No OFFSET.

You avoid the O(N) cost of OFFSET, keep database latency flat, and still give the product team numbered pagination for the next few pages. It is a practical compromise between pure cursor semantics and the “pages 1–10” UI.

What to do in code and in review

• New APIs: Prefer cursor-based pagination for any list that might grow large. Use a stable, unique sort key (e.g. id, or (created_at, id)).
• Existing APIs: If you still use OFFSET/SKIP, add metrics (latency, offset value, row count). When you see latency or CPU climb with offset, plan a migration to cursor-based pagination.
• Code review: Treat OFFSET and skip() on large tables as a scalability risk. Flag them and switch to keyset pagination before production load makes the problem critical.

Key takeaways

• OFFSET/SKIP forces the database to scan and discard N rows before returning a page; cost is O(N) in the offset, so deep pages get slow and heavy.
• Cursor-based (keyset) pagination uses a “start after this key” condition so the engine can use the index: seek + read next K rows. Cost stays effectively constant.
• Use a unique, sequential sort key for the cursor (e.g. id or (created_at, id)). Avoid non-unique columns to prevent skipped or duplicated rows.
• For large lists, prefer cursor pagination and accept no random-access page numbers. Reserve OFFSET for small, bounded datasets where you truly need page numbers.
• Pre-calculated cursors (fetch the next 50 IDs, return cursors for pages 2–6) give you numbered pagination for the next few pages without OFFSET and without giving up flat latency.