Page 1 Loads in 8ms, Page 5,000 Takes 4 Seconds — The OFFSET Trap and How Cursors Fix It

Tags: Pagination · SQL · Query Optimization · Keyset Pagination · Cursors · MySQL · PostgreSQL · API Design · Reading time: ~13 min · Category: Database → Query Optimization

Every list endpoint you have ever written probably ends the same way: LIMIT 20 OFFSET ?. It works. It ships. It passes review, because the query has an index and page one comes back in 8 ms. Then one Tuesday someone opens page 4,000 of the orders table, or a nightly export walks the whole list page by page. The same query — the same table, the same index, the same twenty rows returned — takes four seconds and pins a CPU core. Nothing changed except a number in the URL.

Here is the thread the whole post hangs on. OFFSET does not mean "start at row 100,000." It means this: read 100,020 rows in order, then throw the first 100,000 away. The work grows with how deep you are in the list, not with how much you asked for. Once you see that, three problems become one bug: the slow page, the dying export job, and the mysterious duplicate rows in your CSV.

The feel: the librarian who can't count to 4,000

Imagine a library where the books sit on one very long shelf, in order. The librarian may not use the shelf labels — she can only walk and count. You ask for books 1 through 20. She walks in the door, counts twenty books off the front, and gives them to you. Fast.

You come back and ask for books 4,001 through 4,020. She does not teleport to book 4,001, because she has no way to know where it is. She starts at the door and counts: one, two, three… four thousand. Then she takes the next twenty and hands them to you. She did four thousand units of work to deliver twenty books.

Now the part that should make you wince: you come back for books 4,021–4,040, and she starts at the door again. Every page pays for every page before it. Paging through the whole shelf twenty books at a time does not cost you one walk down the shelf. It costs you a walk for every page. On average each walk covers half the shelf, so multiply that by the number of pages. That is the shape of an O(n²) job hiding inside a loop that looks completely innocent.

What the database actually does

Take the query nearly everyone writes:

SELECT id, user_id, total, created_at
FROM orders
WHERE status = 'shipped'
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;

Even with a perfect index on (status, created_at), the engine has no way to jump to the 100,001st matching row. An index tells it where a value lives, not where an ordinal position lives. B-tree nodes do not store "there are 100,000 rows to my left." So the plan is simple. The engine seeks to the start of status = 'shipped', then walks the index forward, counting and discarding. It skips 100,000 entries, and only then starts collecting. If the index is not covering, each of those 100,020 entries may also mean a lookup back into the table heap to check a column. You then pay random I/O for rows the user will never see.

You can watch it happen. In PostgreSQL, EXPLAIN ANALYZE shows it in one line:

Limit  (cost=... rows=20) (actual time=4183.2..4183.4 rows=20 loops=1)
  ->  Index Scan using idx_orders_status_created on orders
        (actual time=0.05..4160.8 rows=100020 loops=1)
        Filter: (status = 'shipped'::text)
        Rows Removed by Filter: ...

The scaling is depressingly linear in the offset:

(Exact numbers depend on your row width, buffer pool and whether the index is covering — but the shape is always this. Work is proportional to OFFSET + LIMIT, and LIMIT is the part that does not matter.)

The production version: the export job that got slower every night

The slow admin page is the annoying version of this bug. The expensive version is a background job, and it always looks like this:

let page = 0;
while (true) {
  const rows = await db.query(
    `SELECT * FROM orders ORDER BY created_at DESC LIMIT 500 OFFSET ?`,
    [page * 500]
  );
  if (rows.length === 0) break;
  await writeToS3(rows);
  page++;
}

This is the single most common shape of "walk the whole table" code in the world, and it is quadratic. With 5 million rows and a page size of 500 you get 10,000 iterations, and iteration i makes the database read 500 × i rows. Total rows read: roughly n² / (2 × pageSize) — about 25 billion row reads to export 5 million rows. The first thousand pages run fast. That is exactly why it passed testing on a small dataset, and why nobody suspects it. Then the job crosses some threshold and the tail pages start taking seconds each.

What the on-call engineer actually sees is never "pagination is slow." It's:

• A job whose runtime grows faster than the data. The table doubled. The export now takes four times as long. Next quarter it stops finishing inside its window and overlaps with the following night's run.
• Database CPU pinned at 3 AM by one connection doing sequential reads, evicting the working set from the buffer pool. Now the morning traffic is slow too. Your team escalates that graph, and the export job never appears in it.
• Statement timeouts on the last pages. The job's retry logic dutifully retries them, re-reads a million rows each time, and makes the problem worse.
• A search page that is fine for users and lethal for crawlers. Real users never go past page 3. Googlebot happily walks to page 900, and a scraper will walk to page 20,000. That turns your paginated list into an unauthenticated way to burn your database, no exploit required.

The second bug: pages that lie

Even if you had infinite CPU, offset pagination is still wrong on data that changes. Offsets are positions in a list, and the list moves under you.

You fetch page 1 (rows 1–20, newest first). While the user reads it, three new orders arrive. They go to the top. You fetch page 2 with OFFSET 20. But everything shifted down by three, so rows 18, 19 and 20 from page 1 now sit at positions 21, 22 and 23. The user sees three rows twice. Delete three rows instead and the reverse happens. Three rows move past the boundary, and the user never sees them at all.

There is a subtler version too. Your ORDER BY may not be fully deterministic. Take ORDER BY created_at DESC where a hundred rows share the same timestamp. The database is free to return tied rows in any order, and it may choose differently on each query. Your page boundary lands in the middle of the tie. Rows duplicate or vanish even on a table where nobody writes anything. Every ordering you use for pagination must end in a unique tiebreaker.

The fix: stop counting, start seeking

Keyset pagination replaces "skip 100,000 rows" with "start after this specific row." People also call it seek pagination, or cursor pagination in APIs. Instead of a position, you remember the sort key of the last row you returned. You put that key in the WHERE clause, where the index can actually use it.

-- Page 1: no cursor yet
SELECT id, user_id, total, created_at
FROM orders
WHERE status = 'shipped'
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Every page after: "give me what comes after the last row I saw"
SELECT id, user_id, total, created_at
FROM orders
WHERE status = 'shipped'
  AND (created_at, id) < (:last_created_at, :last_id)   -- row-value comparison
ORDER BY created_at DESC, id DESC
LIMIT 20;

That (created_at, id) < (?, ?) is a row-value comparison, and it is the piece people get wrong. It is not the same as created_at <= ? AND id < ?. That expression is wrong at timestamp boundaries. Worse, it is often not a clean index range, so the optimizer cannot seek with it. The tuple form maps exactly onto the composite index (status, created_at DESC, id DESC). The engine descends the B-tree straight to that key and starts reading. PostgreSQL and MySQL 8 both support the tuple syntax directly. On engines that do not, write it as created_at < ? OR (created_at = ? AND id < ?) and check the plan.

The id in the sort key does two jobs, and both matter. It makes the ordering total, so ties in created_at can never shuffle between queries. It also gives the cursor a unique resume point. A page boundary that lands inside a group of identical timestamps still resumes exactly where it stopped. Drop it and you return to duplicates and skips — quietly.

In an API you do not expose the raw key. You encode it into an opaque cursor. That is exactly what Stripe's starting_after, GitHub's Link: rel="next", Slack's next_cursor, and every GraphQL Relay connection already do:

// Server: hand back a cursor, never an offset
const rows = await db.query(sql, [cursor?.createdAt, cursor?.id]);
const last = rows[rows.length - 1];

return {
  data: rows,
  next_cursor: last
    ? Buffer.from(JSON.stringify({ createdAt: last.created_at, id: last.id }))
        .toString("base64url")
    : null,          // null ⇒ genuinely the end
};

And the loop that ate your database becomes an ordinary linear walk — one pass over the index, total:

let cursor = null;
while (true) {
  const rows = await db.query(
    cursor
      ? `SELECT * FROM orders WHERE (created_at, id) < (?, ?)
         ORDER BY created_at DESC, id DESC LIMIT 500`
      : `SELECT * FROM orders ORDER BY created_at DESC, id DESC LIMIT 500`,
    cursor ? [cursor.created_at, cursor.id] : []
  );
  if (rows.length === 0) break;
  await writeToS3(rows);
  cursor = rows[rows.length - 1];        // resume point, not a position
}

Same 5 million rows, same 10,000 iterations. But now you get 5 million row reads instead of 25 billion, and the runtime grows linearly with your data instead of quadratically. It also survives a crash. The cursor is a durable resume point, so you can checkpoint it and restart exactly where you stopped. An offset can never do that, because by the time you restart, the offset means something different.

What you lose (and what to do about it)

Keyset pagination is not strictly better. It is a different deal, and you should be honest about the cost.

• No jumping to page 47. A cursor only knows "after this row," so you get next/previous, not a numbered pager. For infinite scroll, feeds, mobile apps and APIs, that is exactly right. For an admin table where someone genuinely wants page 47, it is not. Most teams take the pragmatic answer. They keep offsets for the first N pages where offsets are cheap, and they cap the depth. They reject OFFSET beyond, say, 10,000 with "please narrow your filters." That one cap turns an unbounded scan into a bounded one. It also ends the crawler problem.
• Previous page needs a reversed query. Flip the comparison and the ORDER BY, take 20, then reverse the rows in your app. It is mechanical, but it is extra code.
• You still can't cheaply say "page 3 of 8,412." That total is a separate problem, and it is usually just as expensive. COUNT(*) with a filter scans the whole matching set. Four options work. An approximate count from table statistics (reltuples in Postgres) gives you "about 8,000 results". A bounded count (SELECT COUNT(*) FROM (SELECT 1 FROM orders WHERE ... LIMIT 1001) t) lets you render "1,000+". A maintained counter serves the handful of filters that truly need an exact number. Or you give the honest answer Google itself gives you: no total at all, and a "next" button that disappears when nothing remains. Fetch LIMIT + 1 rows and you know whether a next page exists without counting anything.
• The sort key has to be indexed and total. Sorting by something mutable (an editable updated_at, a computed relevance score) means rows can move across your cursor while you page. If your sort key can change under you, keyset gives you consistency of traversal, not a snapshot. For a true snapshot you need a repeatable-read transaction or a versioned/as-of query.

The takeaway

Offset pagination is the rare bug that is invisible in every place you would normally look. The index exists, the plan is clean, the p50 is great, and the code is four lines long. It appears only as a slow admin page, a batch job that outgrows its window, and a CSV with mysterious duplicates. Three things are worth carrying out of this:

1. "Skip" is not a database primitive. A B-tree can find a value instantly. It can never find a position at all. So the engine must simulate any pagination you express as a position. It reads and discards rows. Any pagination you express as a value is a single seek. Phrase the question in terms the index can answer and the cost disappears.
2. Positions are not stable identities. "Row 100,001" means something different every time the table changes. "The row after (2026-07-14T10:03:22, 88412)" means the same thing forever. That is why the cursor fixes the correctness bug and the performance bug at once — they were always the same bug.
3. Bound anything a stranger can make deep. If you keep offsets for a numbered pager, cap the depth and mean it. An uncapped OFFSET on a public endpoint is a free, authentication-less way for anyone to make your database read a million rows per request.

The fix is usually an afternoon. Add the composite index ending in a unique tiebreaker, and change the WHERE clause to a row-value comparison. Return an opaque cursor instead of a page number, and put a depth cap on whatever offsets you keep. Then find the batch job that walks a table in a while loop. It is the one whose runtime chart bends upward. Give it a cursor. That one probably costs you more than every slow admin page combined.