Offset vs cursor pagination: where each one loses rows and time

Page 2 shows a row from page 1. Page 4 skips a row entirely. The bug report says the list is broken; the list is fine, the pagination is lying. OFFSET counts positions in a result set that no longer exists, and positions are the one thing a changing table cannot guarantee. The fix is a cursor, which remembers a row instead of a position. The cost is real too, and worth pricing before you switch.

The constraint

Pagination has two jobs: return a stable subset, and return it fast. OFFSET fails the first job the moment another writer touches the table. Every query sees its own snapshot: Postgres documents that "a SELECT query sees a snapshot of the database as of the instant the query begins to run," and that "two successive SELECT commands can see different data" between them. Position 100 on the second request is not necessarily the row that was at position 100 on the first. It fails the second job more slowly: "The rows skipped by an OFFSET clause still have to be computed inside the server; therefore a large OFFSET might be inefficient." Page 9,000 computes 90,000 rows to return 10.

The options

OFFSET + LIMIT. One line of SQL, works everywhere, gives you random access ("jump to page 47") and a natural total count. Its two failure modes are baked in: deep pages cost O(offset) because skipped rows are computed and discarded, and concurrent writes shift positions so rows repeat or vanish.

SQL cursors (DECLARE CURSOR). The database holds your position server-side. Real cursors are transaction-scoped state: they die when the request ends, which makes them useless for stateless HTTP APIs. This is not what Stripe means by "cursor."

Keyset pagination. The API convention Stripe, GitHub, and Slack use: the client stores the sort value of the last row it saw, and the next query is WHERE (sort_key, id) > (last_key, last_id) ORDER BY sort_key, id LIMIT n. Postgres's row-constructor comparison evaluates (a, b) > (x, y) lexicographically, and a B-tree index "matching the ORDER BY" can retrieve "the first n rows... directly, without scanning the remainder at all." Stability comes free: the cursor names a row, not a position, so inserts ahead of it cannot shift the reader.

The mechanism

The demo below simulates a B-tree index over 100,000 ordered rows. It pages four times while a writer inserts ahead of the reader, then measures how many rows each strategy scans to serve one page at three depths. Run it with node pagination.mjs:

// offset vs keyset over a table that keeps changing
// model: a b-tree index returns rows in (created_at, id) order.
// OFFSET k reads and discards k rows first; a keyset cursor seeks
// to the last seen (created_at, id) and reads only the page.

const N = 100000;
const table = [];
for (let i = 0; i < N; i++) table.push({ id: i + 1, created_at: 1000 + i });

function offsetPage(offset, size) {
  let scanned = 0;
  const rows = [];
  for (let i = 0; i < table.length && rows.length < size; i++) {
    scanned++;                        // every row the index touches
    if (i >= offset) rows.push(table[i]);
  }
  return { rows, scanned };
}

function keysetPage(last, size) {
  let scanned = 0;
  const rows = [];
  for (const r of table) {
    if (r.created_at > last.created_at ||
        (r.created_at === last.created_at && r.id > last.id)) {
      scanned++;                      // after an O(log n) seek to `last`
      rows.push(r);
      if (rows.length === size) break;
    }
  }
  return { rows, scanned };
}

// 1. drift: fetch 4 pages by offset while a writer inserts at the front
const PAGE = 10;
const seen = new Set();
let dup = 0;
for (let p = 0; p < 4; p++) {
  const { rows } = offsetPage(p * PAGE, PAGE);
  for (const r of rows) {
    if (seen.has(r.id)) dup++;
    seen.add(r.id);
  }
  // between page fetches, a new row lands ahead of the reader's position
  table.unshift({ id: -100 - p, created_at: 500 - p });
}
console.log(`offset: ${seen.size} unique rows in 4 pages, ${dup} delivered twice`);

// keyset pass over the same mutated table: cursor = a row value
const seen2 = new Set();
let dup2 = 0;
let last = { created_at: -Infinity, id: -Infinity };
for (let p = 0; p < 4; p++) {
  const { rows } = keysetPage(last, PAGE);
  for (const r of rows) {
    if (seen2.has(r.id)) dup2++;
    seen2.add(r.id);
  }
  last = rows[rows.length - 1];
}
console.log(`keyset: ${seen2.size} unique rows in 4 pages, ${dup2} delivered twice`);

// 2. deep-page cost: rows scanned to serve one page of 10
table.sort((a, b) => a.created_at - b.created_at || a.id - b.id);
for (const depth of [0, 10_000, 90_000]) {
  const o = offsetPage(depth, PAGE).scanned;
  const k = keysetPage(table[depth], PAGE).scanned;
  console.log(`page ${depth}: offset scans ${o} rows, keyset scans ${k}`);
}

offset: 37 unique rows in 4 pages, 3 delivered twice
keyset: 40 unique rows in 4 pages, 0 delivered twice
page 0: offset scans 10 rows, keyset scans 10
page 10000: offset scans 10010 rows, keyset scans 10
page 90000: offset scans 90010 rows, keyset scans 10

Three duplicates in four pages, exactly the bug report. The keyset reader is immune because the inserted rows sort behind its cursor. And the scan counts are why production APIs settled on this pattern: Stripe paginates with starting_after object IDs and a has_more flag, GitHub's GraphQL API traverses "a specific position in the data set" via opaque cursors, and Slack returns a next_cursor in response_metadata. None of them count positions.

Where it bites

Keyset's price is the page-number UI. GitHub's REST docs ↗ state the tradeoff plainly: "With cursor-based pagination, there is no fixed concept of 'pages' in the result set, so you can't navigate to a specific page." No "jump to page 47." The matching casualty is "page 7 of 342": the total requires COUNT(*), and Postgres's wiki explains why that is expensive under MVCC, "PostgreSQL must walk through all rows to determine visibility." This is why Stripe returns has_more, a boolean, instead of a total.

The second bite is shared by both strategies and easier to miss: a non-unique sort key silently corrupts pages. MySQL's own doc warns that with identical ORDER BY values "the server is free to return those rows in any order," page boundaries included. A cursor on created_at alone breaks the same way whenever two rows share a timestamp. The tiebreaker column is not optional on either strategy.

The third bite is MySQL-specific. Postgres uses a composite B-tree index for the (a, b) > (x, y) row comparison; MySQL's optimizer is less dependable there: its own manual shows the OR-expanded equivalent matching more index parts than the tuple form, so the portable spelling expands it by hand:

WHERE created_at > :last_ts
   OR (created_at = :last_ts AND id > :last_id)
ORDER BY created_at, id
LIMIT 10

The numbers

• Demo run above: offset delivered 3 rows twice in 4 pages under front-inserts; keyset delivered 0 twice. Deep-page scans: 10 vs 10,010 vs 90,010 rows for one page of 10.
• Stripe's list API: limit default 10, range 1-100, starting_after/ending_before object IDs, has_more boolean.
• Slack: next_cursor in response_metadata, limit up to 1,000, recommended 100-200, and fewer-than-limit results are legal.
• Postgres docs: skipped OFFSET rows "still have to be computed inside the server"; a matching B-tree index serves ORDER BY ... LIMIT n "without scanning the remainder."

Our position

Keyset for anything a user scrolls: feeds, lists, tables that grow, tables under write. The cost (no random pages, no cheap total) is a UI decision, not an engineering one, and "page 7 of 342" is almost never worth an O(table) count per request. Keep OFFSET for admin tables and exports: small, stable, and actually browsed by page number. On either strategy, sort by a unique composite, created_at, id or equivalent, and in MySQL spell the cursor predicate as an OR expansion, not a tuple.

The takeaway

This is the same call we make inside the API layers we build for apps like CashTaq in our product engineering work, and it pairs with the write-side problem in webhooks arrive twice and out of order: distributed consistency is a decision, not a default. If your list endpoint serves duplicates, send us the query.

← the journal