Cursor-Based Pagination for REST APIs

I had a client's product listing API that returned duplicate rows mid-scroll once their catalog crossed around 200k SKUs. Support tickets started coming in about "the same shoe showing up twice" on page 4, then page 9, completely inconsistently. The endpoint wasn't buggy in any obvious way — it was doing exactly what OFFSET and LIMIT are supposed to do. That's the actual problem with offset pagination: it works fine in a demo and quietly breaks in production once rows are large in number and changing while users page through them.

This post covers why that happens and how to replace it with cursor-based (keyset) pagination, which fixes both the performance cliff and the correctness bug at the same time.

Why OFFSET Pagination Falls Apart at Scale

OFFSET 10000 LIMIT 20 looks innocent, but most relational databases can't just "jump" to row 10,000 — they have to scan and discard the first 10,000 rows every single time before returning your 20. As the offset grows, the query gets slower in a straight line. On a table with a few thousand rows you'll never notice. On a table with a few million, page 500 of your API can take seconds instead of milliseconds, and that's before anyone's running concurrent requests against it.

The slowness is the obvious problem. The one that actually generates support tickets is subtler: offset pagination assumes the underlying result set holds still between requests. It doesn't. If a row gets inserted or deleted while a user is paging through sorted-by-created_at results, every row after that point shifts by one position. The client's next "page 5" request lands on a different offset than it meant to, and they either see a duplicate row they already saw on page 4, or skip one entirely. Nobody's code is wrong here — the pagination model itself can't guarantee stability once the data moves.

The Naive Approach: OFFSET and LIMIT

Here's the version almost everyone starts with, because it maps directly onto how you'd think about "page 3 of results":

// GET /api/products?page=3&limit=20
app.get('/api/products', async (req, res) => {
  const page = parseInt(req.query.page) || 1;
  const limit = parseInt(req.query.limit) || 20;
  const offset = (page - 1) * limit;

  const { rows } = await db.query(
    `SELECT id, name, price, created_at
     FROM products
     ORDER BY created_at DESC
     LIMIT $1 OFFSET $2`,
    [limit, offset]
  );

  res.json({ page, limit, data: rows });
});

It's simple, it's intuitive for an API consumer to reason about ("give me page 3"), and it's genuinely fine for small, mostly-static tables — I still use it for things like an admin dashboard listing 500 configuration rows where someone wants to jump straight to page 12. The problem only shows up once the table is both large and actively being written to while people read from it, which describes most product catalogs, feeds, and transaction lists.

The Actual Fix: Keyset (Cursor) Pagination

Instead of asking the database "skip N rows," keyset pagination asks it "give me everything after this specific row" — which is a query the database's index can answer directly, without scanning anything it's going to throw away:

// GET /api/products?cursor=eyJjcmVhdGVkX2F0IjoiMjAyNi0wOS0yOCIsImlkIjo0NTEyfQ==&limit=20
app.get('/api/products', async (req, res) => {
  const limit = parseInt(req.query.limit) || 20;
  let cursor = null;

  if (req.query.cursor) {
    cursor = JSON.parse(
      Buffer.from(req.query.cursor, 'base64').toString('utf8')
    );
  }

  const { rows } = await db.query(
    `SELECT id, name, price, created_at
     FROM products
     WHERE (created_at, id) < ($1, $2) OR $1 IS NULL
     ORDER BY created_at DESC, id DESC
     LIMIT $3`,
    [cursor?.created_at ?? null, cursor?.id ?? null, limit]
  );

  const last = rows[rows.length - 1];
  const nextCursor = last
    ? Buffer.from(JSON.stringify({ created_at: last.created_at, id: last.id })).toString('base64')
    : null;

  res.json({ data: rows, next_cursor: nextCursor });
});

A few things that matter here and that I've seen get skipped in quick implementations: the (created_at, id) tuple comparison, not just created_at alone. created_at on its own isn't unique — two products inserted in the same millisecond will tie, and a plain created_at < $1 comparison can silently drop or duplicate one of them. Tacking on the primary key as a tiebreaker makes the sort order fully deterministic, which is the entire point of doing this. You also need a composite index on (created_at, id) to get the performance benefit — without it, you've just traded one slow query pattern for a different one.

When Offset Pagination Is Still Fine

I'd push back a little on the instinct to rip out every OFFSET query the moment you read a post like this one. Cursor pagination gives up something real: the ability to jump to an arbitrary page number, which some admin UIs genuinely need ("show me page 40"). If your dataset is small, rarely changes mid-session, or the consumer is an internal tool rather than a public API serving infinite-scroll results, offset pagination's simplicity is still the right trade. I'd reserve cursor pagination for anything public-facing, anything backing infinite scroll or a mobile feed, and anything where the table is going to keep growing past the point where a full table scan is cheap. Forcing cursor pagination onto a 2,000-row internal reporting table is solving a problem you don't have yet.

Conclusion

Offset pagination breaks down for two separate reasons — query cost that scales with page depth, and result-set instability when rows change between requests — and keyset pagination fixes both by querying off the last row's sort key instead of a row count. Default to cursor-based pagination for anything large or public, keep offset pagination for small or internal tools where jump-to-page actually matters, and always pair the cursor comparison with a tiebreaker column and a matching composite index.