Cursor-Based vs Offset Pagination — Why page=2 Silently Drops Records

You’re building a feed. Page 1 loads, the user scrolls down, you fetch page 2. But some items that existed when page 1 loaded don’t appear on page 2. No error, no warning — they just don’t show up.

This is the offset pagination bug, and it’s not a race condition. It’s deterministic. Every system using LIMIT x OFFSET y against a live, write-heavy table will hit it eventually.

The SQL that causes it

Offset pagination translates directly to:

-- Page 1: rows 1–10
SELECT * FROM posts ORDER BY created_at DESC LIMIT 10 OFFSET 0;

-- Page 2: rows 11–20
SELECT * FROM posts ORDER BY created_at DESC LIMIT 10 OFFSET 10;

The database has no memory of what was on page 1 when you request page 2. It counts from position 0 every time, against whatever rows exist at that moment.

The scenario that breaks it

Start with 20 posts, IDs 1–20, newest first. The user loads page 1 — gets IDs 20–11. Between that request and the next, three new posts are inserted: IDs 21, 22, 23.

Now the user scrolls and fetches page 2:

SELECT * FROM posts ORDER BY created_at DESC LIMIT 10 OFFSET 10;
-- Returns: IDs 13–4, not 10–1

Posts 10, 9, and 8 — which the user hasn’t seen yet — just got pushed past the offset window. Three new inserts caused exactly three skipped records. The math is always exact.

The user sees nothing wrong. Your logs show nothing wrong. The records exist in the database. They’re just invisible to that session.

Cursor pagination fixes this

Instead of counting positions, you anchor the next page to the last record you actually returned. The query becomes “give me the next 10 rows after this specific row” rather than “give me rows 11–20.”

-- Page 1: no cursor, start from top
SELECT * FROM posts ORDER BY created_at DESC, id DESC LIMIT 10;
-- Last row returned: created_at = '2024-01-10 12:00:00', id = 11

-- Page 2: anchor to that exact row
SELECT * FROM posts
WHERE (created_at < '2024-01-10 12:00:00')
   OR (created_at = '2024-01-10 12:00:00' AND id < 11)
ORDER BY created_at DESC, id DESC
LIMIT 10;

New inserts don't affect this at all. They land at the top of the sort order, before your cursor position. Everything from the cursor onward is stable regardless of concurrent writes.

What the cursor actually is

A cursor is the encoded position of the last record returned. In practice, it's usually one of:

• An encoded timestamp + ID pair — the most common approach. Serialize {"created_at": "...", "id": 11} as a base64 string and return it as next_cursor.
• A UUID or opaque row ID — some systems use the primary key directly when it's time-ordered (ULIDs, UUID v7). A UUID generator is useful when you're deciding between UUID formats for your schema — v4 is random and doesn't sort chronologically, v7 is timestamp-based and does.
• A signed token — Stripe's approach, preventing clients from forging cursor values that jump to arbitrary positions.

When offset pagination is still fine

Not every use case needs cursors. Offset pagination is fine when:

• The dataset is static or append-only-from-the-bottom. Archive pages, documentation, export queues. If nothing gets inserted at the top of your sort order, the offset stays stable.
• Admin dashboards with explicit page numbers. Users expect to jump to "page 5" and see that specific slice. Cursors can't do this — there's no concept of "page N" when your anchor is a row, not a count.
• Small datasets under your control. Paginating 200 records in an internal tool where new data arrives via daily batch jobs? The complexity of cursors isn't worth it.
• Inserts only happen at the end of the sort order. If your pagination is ascending by ID and new records get incrementally higher IDs, earlier pages are unaffected by new inserts.

The deciding question: can new rows be inserted before the current cursor position between requests? If yes, offset pagination will silently drop records. If no, it's fine.

Comparison

	Offset pagination	Cursor pagination
SQL	LIMIT x OFFSET y	WHERE (created_at, id) < (cursor) LIMIT x
Consistent on concurrent inserts	No — silently skips records	Yes — position is stable
Jump to arbitrary page	Yes (OFFSET = page * size)	No — forward-only traversal
Total page count	Easy (COUNT(*) / size)	Not possible without full scan
Performance at large offsets	Degrades — OFFSET 100000 scans 100k rows to discard them	Stable — always uses an index range scan
Client complexity	Low — just a page number	Higher — must store and forward cursor token

How GitHub and Stripe handle it

Both GitHub and Stripe use cursor-based pagination in their APIs — and for good reasons that are specific to each.

GitHub

GitHub's REST API still uses page and per_page on most endpoints, but the GraphQL API uses proper cursor pagination with after and before:

query {
  repository(owner: "vercel", name: "next.js") {
    issues(first: 10, after: "Y3Vyc29yOnYyOpHOAABGPQ==") {
      pageInfo {
        endCursor
        hasNextPage
      }
      nodes {
        title
        number
      }
    }
  }
}

That cursor (Y3Vyc29yOnYyOpHOAABGPQ==) is base64-encoded — decode it and you get an internal row reference, not a page number. On a repo like next.js, dozens of issues can be filed between your page 1 and page 2 requests. With offset pagination, you'd silently skip some of them.

Stripe

Stripe's list APIs use starting_after and ending_before — you pass the ID of the last object you received:

# First page
curl https://api.stripe.com/v1/charges   -u sk_test_xxx:   -d limit=10

# Next page — pass the last charge ID from the previous response
curl https://api.stripe.com/v1/charges   -u sk_test_xxx:   -d limit=10   -d starting_after=ch_1ABC123def456

Stripe's objects have time-ordered IDs (ch_, cus_, pi_), so the ID itself functions as the cursor. Their API has never offered offset-based pagination because billing data is exactly where silent skips become a serious problem — a missed charge isn't just a UX issue.

Implementing cursors: a minimal pattern

Here's a minimal Node.js/PostgreSQL implementation using a compound timestamp + ID cursor:

function encodeCursor(row) {
  return Buffer.from(JSON.stringify({
    created_at: row.created_at,
    id: row.id
  })).toString('base64url');
}

function decodeCursor(cursor) {
  return JSON.parse(Buffer.from(cursor, 'base64url').toString('utf8'));
}

async function fetchPage(db, cursor = null, limit = 10) {
  let rows;

  if (!cursor) {
    rows = await db.query(
      'SELECT * FROM posts ORDER BY created_at DESC, id DESC LIMIT $1',
      [limit]
    );
  } else {
    const { created_at, id } = decodeCursor(cursor);
    // PostgreSQL supports tuple comparison natively.
    // MySQL requires: WHERE created_at < $1 OR (created_at = $1 AND id < $2)
    rows = await db.query(
      `SELECT * FROM posts
       WHERE (created_at, id) < ($1, $2)
       ORDER BY created_at DESC, id DESC
       LIMIT $3`,
      [created_at, id, limit]
    );
  }

  const nextCursor = rows.length === limit
    ? encodeCursor(rows[rows.length - 1])
    : null;

  return { rows, nextCursor };
}

One thing to be careful about: if you're using UUIDs as primary keys instead of auto-incrementing integers, the compound sort only works if your UUIDs are time-ordered. UUID v4 is random — sorting by (created_at, uuid_v4) works, but you lose the tiebreaker guarantee that a sequential ID gives you. UUID v7 or ULIDs are time-ordered and avoid this problem entirely.

The short version

Offset pagination is simple to implement and works fine until your data is live and write-heavy. The moment users can trigger inserts between pagination requests — posts, charges, issues, anything — offset silently drops records. The skip count is exactly equal to the number of new inserts at the top of your sort order. No error, no warning.

Cursor pagination trades "jump to page N" for position stability. For user-facing feeds and any API that others will build on, that's the right trade. OFFSET is fine for admin tools and static exports — don't add cursor complexity where the bug can't occur.

You may also like