Cursor vs Offset Pagination: Why Offset Falls Apart at Scale

LIMIT 20 OFFSET 1000000. The query runs. It takes eleven seconds. The page that uses it times out. The user opens a ticket.

Offset pagination is the default in almost every web framework and breaks the same way in almost every application that grows past trivial scale. The fix is cursor pagination, and the gap between "I have heard of it" and "I am actually using it" is bigger than most teams realize.

Why Offset Falls Apart

When you ask the database for LIMIT 20 OFFSET 1000000, here is what it actually does: scan the first 1,000,020 rows in the query's order, discard the first million, return the next 20. The work is proportional to the offset, not to the page size.

SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 1000000;

For page 50,000 of a feed, this is murder. The database does a million rows of work to return 20.

There is a second, subtler problem: results shift. If a new row is inserted between the user's clicks of "Next page" and the next request, every page boundary shifts by one. The user sees the same row at the top of one page and the bottom of the next.

How Cursor Pagination Works

Cursor pagination uses values from the last row of the previous page as the starting point for the next.

-- First page
SELECT * FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Next page — using the last row's (created_at, id) as the cursor
SELECT * FROM posts
WHERE (created_at, id) < ('2026-05-10 14:32:01', 8842)
ORDER BY created_at DESC, id DESC
LIMIT 20;

The database does not scan rows it is going to discard. With an index on (created_at, id), the next-page query is as fast as the first-page query, regardless of how deep the user has paged.

The Tie-Breaker

A cursor needs to identify a unique position in the ordering. created_at alone is not enough — two rows might share a timestamp, and the cursor would be ambiguous. The pattern is to compose the cursor from the sort key plus a tie-breaker (usually the primary key).

ORDER BY created_at DESC, id DESC

The tie-breaker has to be in the index too, or you lose the performance benefit.

Cursor Encoding

The cursor is sent to the client and returned in the next request. It has to be something the server can decode reliably and clients should not be encouraged to fabricate.

A common pattern: base64-encode a JSON tuple of the cursor values.

// Generate cursor for the last row of the page
$cursor = base64_encode(json_encode([
    'created_at' => $lastRow->created_at->toIso8601String(),
    'id' => $lastRow->id,
]));

// Next-page response
return response()->json([
    'data' => $results,
    'next_cursor' => $cursor,
]);

// On the next request
$decoded = json_decode(base64_decode($request->cursor), true);
$query->where(function ($q) use ($decoded) {
    $q->where('created_at', '<', $decoded['created_at'])
      ->orWhere(function ($q2) use ($decoded) {
          $q2->where('created_at', '=', $decoded['created_at'])
             ->where('id', '<', $decoded['id']);
      });
});

This OR-with-equality clause expresses the "rows strictly after the cursor" predicate. Some databases support row-value comparison directly (WHERE (created_at, id) < (?, ?)), which is cleaner — Postgres does, MySQL does not consistently.

Sign the cursor if you want to detect tampering. For most APIs, opaque base64 is fine — clients should treat it as a black box.

Forward and Backward

Pure cursor pagination is forward-only. The cursor tells you where to start; "previous page" means going backward.

The simplest answer: keep a cursor for both directions. The response includes a next_cursor (last row of current page) and a prev_cursor (first row of current page, used with reversed sort to fetch the previous page).

{
  "data": [ ... ],
  "next_cursor": "eyJjcmVhdGVkX2F0IjoiMjAyNi0wNS0...",
  "prev_cursor": "eyJjcmVhdGVkX2F0IjoiMjAyNi0wNS0..."
}

If neither cursor is present, the client can stop paging in that direction.

The "Jump to Page N" Problem

Cursors do not give you "jump to page 47." You can only go forward or backward from where you are. This is a real UX limitation for any UI that has a "page 47" link.

Most modern UIs avoid that affordance anyway — endless scroll, "load more" buttons, and "back to top" replace the page-number UI. If you genuinely need page numbers, you have a few options:

• Total count + offset for small datasets. Acceptable when the total table size is bounded.
• Hybrid: cursor for forward paging, offset only for explicit jumps. Most users go forward; the few who jump pay the cost.
• Snapshot the result set. Save a query result with a cursor token; subsequent pages reference the snapshot. Adds complexity but allows jumps.

The first option is the most common compromise.

Consistent Sort Order

Cursor pagination requires a stable sort. The sort key plus tie-breaker must produce a deterministic order; otherwise the cursor's meaning is undefined.

Two failure modes:

Mutable sort key. If you sort by updated_at, the value of updated_at for a given row can change. A row's cursor position changes as a result, and pages skip or repeat.

Non-unique sort key without tie-breaker. If two rows have identical sort values and no tie-breaker, their relative order is database-dependent and can change between queries.

The safe pattern: sort by immutable, unique values, or by a sort key plus the row's stable primary key.

When Offset Is Still Fine

Offset pagination is not always wrong. It is fine when:

• The total dataset is small (a few thousand rows)
• Users rarely page deeply (most action is on the first few pages)
• "Jump to page N" is a hard UX requirement
• The implementation is throwaway

The performance cliff appears around 10,000+ offsets on indexed columns, much earlier on un-indexed scans. If your worst case is page 5 of 30, offset is fine. If your worst case is page 500 of 50,000, it is not.

Practical Migration

If you have an existing offset-paginated API and want to migrate without breaking clients:

1. Add cursors as an additive field. Return both cursor and next_cursor in responses. Clients ignoring cursors keep working.
2. Accept either cursor or offset in requests. The cursor parameter takes precedence; offset is supported for backward compatibility.
3. Document cursor as preferred; deprecate offset in next major version. Give clients a year to migrate.
4. Remove offset support. Eventually.

This is what Stripe, GitHub, and most large API providers have done. The transition is multi-year but the eventual state is much better.

The Anti-Pattern That Looks Right

The most common bad cursor implementation: using the primary key as the cursor.

WHERE id > ? ORDER BY id LIMIT 20

This works only when the natural sort order is by id. If you actually sort by created_at or score or anything else, this returns wrong results. The cursor's value must match the actual sort key.

Cursors are not just "the last row's ID." They are "the last row's position in the current sort." Different sorts produce different cursors. Get this wrong and pages skip rows in subtle ways nobody notices until the customer support tickets pile up.

Frameworks

The built-in implementations handle the encoding and tie-breaker boilerplate. Use them. Hand-rolled cursor pagination is a footgun unless you have a specific reason to build it yourself.

Looking at a paginated endpoint that has started timing out and dragging the dashboard with it? We help teams migrate to scaling pagination without breaking the clients that have integrated against the old shape. scopeforged.com