Cursor-Based Pagination: Why OFFSET Is Killing Your API (and How to Fix It)

Cursor-based pagination is one of those API design decisions that seems trivial until your dataset grows, users start scrolling fast, and your OFFSET 100000 query brings the database to its knees. If you're still paginating with ?page=2&limit=20, this post is for you.

Why offset pagination breaks

The classic approach looks like this:

SELECT * FROM events
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;

Two problems. First, performance: the database still has to walk and discard those 100,000 rows before returning your 20. Offset scans get linearly slower the deeper you page. Second, correctness: if a new row is inserted while a user paginates, every subsequent page shifts by one. Users see duplicates or skip records entirely.

Cursor-based pagination fixes both. Instead of "skip N rows," you say "give me rows after this specific point."

How cursors work

A cursor is an opaque pointer to a position in an ordered result set — usually encoding the sort key of the last item you saw. The query becomes a WHERE clause instead of an OFFSET:

SELECT * FROM events
WHERE created_at < '2026-05-29T10:00:00Z'
ORDER BY created_at DESC
LIMIT 20;

This uses the index on created_at directly. It's fast at any depth because there's no scanning-and-discarding.

The one gotcha: your sort column must be unique and stable, or you'll skip/duplicate rows that share a timestamp. The fix is a composite cursor — sort by (created_at, id) and tie-break on id.

A working implementation

Here's a Node.js endpoint using a composite cursor, with the cursor base64-encoded so clients treat it as opaque:

import express from "express";
const app = express();

function encodeCursor(row) {
  const raw = `${row.created_at.toISOString()}|${row.id}`;
  return Buffer.from(raw).toString("base64url");
}

function decodeCursor(cursor) {
  const raw = Buffer.from(cursor, "base64url").toString("utf8");
  const [createdAt, id] = raw.split("|");
  return { createdAt, id: Number(id) };
}

app.get("/events", async (req, res) => {
  const limit = Math.min(Number(req.query.limit) || 20, 100);
  const cursor = req.query.cursor ? decodeCursor(req.query.cursor) : null;

  // Fetch limit + 1 to detect whether another page exists
  const rows = await db.query(
    `SELECT id, created_at, payload FROM events
     WHERE ($1::timestamptz IS NULL)
        OR (created_at, id) < ($1, $2)
     ORDER BY created_at DESC, id DESC
     LIMIT $3`,
    [cursor?.createdAt ?? null, cursor?.id ?? null, limit + 1]
  );

  const hasMore = rows.length > limit;
  const page = hasMore ? rows.slice(0, limit) : rows;

  res.json({
    data: page,
    next_cursor: hasMore ? encodeCursor(page[page.length - 1]) : null,
  });
});

Two details that matter in production. Fetching limit + 1 rows is a cheap trick to know whether a next_cursor should be returned — no separate COUNT(*) needed. And the row comparison (created_at, id) < ($1, $2) is PostgreSQL row-value syntax, which compares tuples lexicographically and maps cleanly to a composite index.

Consuming it from the client

The client never builds cursors — it just follows them:

async function fetchAllEvents() {
  let cursor = null;
  const all = [];

  do {
    const url = new URL("https://api.example.com/events");
    url.searchParams.set("limit", "100");
    if (cursor) url.searchParams.set("cursor", cursor);

    const res = await fetch(url);
    const { data, next_cursor } = await res.json();
    all.push(...data);
    cursor = next_cursor;
  } while (cursor);

  return all;
}

When offset is still fine

Cursor pagination has a tradeoff: you can't jump to "page 47" because cursors are sequential. If your UI genuinely needs numbered page links over a small, stable dataset, offset is fine. But for infinite scroll, feeds, syncing, and any large or fast-changing table, cursors win on both speed and consistency.

Closing

Getting pagination right is one of those quiet markers of a well-designed API — and it's a lot easier to get right when you can test cursors against real responses before shipping. APIKumo lets you build, document, and exercise endpoints like these directly in a shared workspace, so you can verify your next_cursor behaves across edge cases instead of finding out in production. Design the contract, paginate with confidence.