A Practical Guide to API Pagination

Return every row from a growing table in a single response and you've built a feature that works fine in development and falls over in production the day a customer has 50,000 records. Pagination is the fix, but the naive approach (LIMIT/OFFSET) has a correctness bug that only shows up under concurrent writes — which is exactly the condition your API is under in production and probably isn't under in your tests.

Offset pagination: simple, and quietly wrong

The obvious approach: pass a page number, compute an offset, and let the database skip rows.

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

app.get('/posts', async (req, res) => {
  const page = Number(req.query.page ?? 1);
  const pageSize = 20;
  const offset = (page - 1) * pageSize;

  const posts = await db.query(
    'SELECT * FROM posts ORDER BY created_at DESC LIMIT $1 OFFSET $2',
    [pageSize, offset],
  );
  res.json(posts);
});

This works, and it's the right choice for small, mostly-static tables with a UI that needs actual page numbers (1, 2, 3 …). It has two real problems once the table is large and actively written to.

Problem 1: performance degrades with the offset

OFFSET 100000 doesn't jump straight to row 100,000 — the database still has to scan and discard the first 100,000 matching rows before it can return the next page. Page 1 is fast; page 5,000 can be dramatically slower, on the exact same query shape.

Problem 2: items shift under concurrent writes

This is the correctness bug, not just a performance one. Say a client is paging through results ordered by created_at DESC, and a new row is inserted between page 1 and page 2 requests. Every row shifts down by one position — the last item of page 1 gets pushed onto page 2, and the client sees it twice. Delete a row instead, and everything shifts up, and the client silently skips one. Neither shows up as an error; the client just gets subtly wrong data.

Cursor pagination: fixes both

Instead of "skip N rows," a cursor says "give me rows after this specific one." The cursor is typically the last row's sort key from the previous page, not a row count.

SELECT * FROM posts
WHERE created_at < $1
ORDER BY created_at DESC
LIMIT 20;

app.get('/posts', async (req, res) => {
  const pageSize = 20;
  const cursor = req.query.cursor; // ISO timestamp of the last item seen, or undefined

  const posts = await db.query(
    cursor
      ? 'SELECT * FROM posts WHERE created_at < $1 ORDER BY created_at DESC LIMIT $2'
      : 'SELECT * FROM posts ORDER BY created_at DESC LIMIT $1',
    cursor ? [cursor, pageSize] : [pageSize],
  );

  const nextCursor = posts.length === pageSize ? posts[posts.length - 1].created_at : null;

  res.json({ items: posts, nextCursor });
});

Because the query filters on created_at < $1 rather than skipping a row count, a new row inserted at the top doesn't shift anything below it — every subsequent cursor still points to the same actual row. And with an index on created_at, this query is a fast range scan regardless of how deep into the results you are — no accumulating scan cost like OFFSET has.

The cursor needs a unique tiebreaker

created_at alone breaks if two rows share the exact same timestamp — you could skip or duplicate one of them. Add the primary key as a tiebreaker, and encode both into the cursor:

SELECT * FROM posts
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;

function encodeCursor(row) {
  return Buffer.from(`${row.created_at.toISOString()}|${row.id}`).toString('base64');
}

function decodeCursor(cursor) {
  const [createdAt, id] = Buffer.from(cursor, 'base64').toString().split('|');
  return { createdAt, id };
}

Base64-encoding the cursor isn't for security — it's so the value is an opaque token from the client's point of view, which means you're free to change what's inside it later (add a field, switch sort columns) without breaking clients that just pass it back verbatim.

What clients actually see

A cursor-paginated response typically looks like this — no total count, no page number, just "here's the next token":

{
  "items": [ { "id": 512, "title": "..." }, { "id": 511, "title": "..." } ],
  "nextCursor": "MjAyNi0wNy0xN1QwOTozMDowMFp8NTEx"
}

The client stores nextCursor and sends it back as ?cursor=... for the next page. It's opaque to the client — they shouldn't parse it or construct one by hand, and this contract is what lets your backend change the pagination internals later without a breaking API change.

When to still use offset pagination

Cursor pagination gives up the ability to jump to an arbitrary page number ("show me page 47") because there's no way to compute a cursor for a page you haven't fetched yet. If your UI genuinely needs numbered pages with direct jumps — an admin table with a page selector, for instance — offset pagination is still the right tool, especially on tables small enough that the performance cliff never matters in practice. Reach for cursor pagination when the table is large, is written to concurrently, and the client only needs "next" and "previous," which describes most infinite-scroll feeds and public APIs.

Takeaway

Offset pagination is simple and fine for small, page-numbered UIs. Once a table is large and being written to concurrently, OFFSET gets both slower and quietly incorrect as pages shift under moving data. Cursor pagination — filtering on "after this row" instead of "skip N rows," with a unique tiebreaker — fixes both, at the cost of giving up arbitrary page jumps.