Pagination That Doesn't Break

Ask for "an endpoint that returns paginated results" and the default answer — from an AI assistant or from most tutorials — is OFFSET/LIMIT:

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

It reads correctly, it's short, and it works perfectly in every manual test you'll run against a table that isn't changing. The bug only shows up under the condition that manual testing never reproduces: new rows arriving while a user is paging through.

Why offset breaks under writes

OFFSET 40 means "skip the first 40 rows of the current result set, as computed right now." If a new row is inserted at the top of the sort order between the user loading page one and requesting page two, everything shifts down by one. Page two now starts one row earlier than it should — the row that was previously last on page one gets shown again, and a row that should have appeared gets pushed past the offset and silently skipped.

This isn't a rare race — it's the normal case for any feed, comment list, or activity log ordered by recency, which is precisely the kind of table users paginate through because it's actively being written to. The bug is invisible in development (nobody's inserting rows while you click "next") and shows up in production as "sometimes I see a duplicate post" or "I feel like I'm missing some items," which is one of the hardest categories of bug report to act on because it never reproduces on demand.

OFFSET has a second problem independent of concurrency: it gets slower as the offset grows. OFFSET 50000 still requires the database to scan and discard 50,000 rows before it can return anything — there's no way to jump directly to a position in a B-tree ordered by an unrelated column.

Cursor-based pagination

The fix is to stop asking "give me rows 40–60" and instead ask "give me the next 20 rows after this specific row I already have." The cursor is a pointer to a row, not a position in a list:

SELECT * FROM posts
WHERE created_at < $1        -- cursor: created_at of the last row on the previous page
ORDER BY created_at DESC
LIMIT 20;

A row inserted above the cursor doesn't shift anything — the query is still "everything older than this specific timestamp," which is a stable definition regardless of what's been inserted since. No skipped rows, no duplicates, and the query is a direct index lookup instead of a scan-and-discard, so it doesn't degrade as users page deeper.

The tie-break you'll need eventually

created_at alone breaks the moment two rows share a timestamp — which happens more than you'd expect (bulk inserts, seed data, a column with second-level precision under real traffic). If row A and row B both have created_at = 2026-08-24T10:00:00, and A happened to land on page one while B is still waiting, WHERE created_at < cursor might exclude B entirely, or include it twice, depending on which side of the boundary it lands on.

The fix is a composite cursor — timestamp plus a strictly-unique tiebreaker, typically the primary key:

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

(created_at, id) < ($1, $2) is row-wise comparison: it means "earlier timestamp, OR same timestamp with a smaller id" — a total order with no ties possible, because id is unique. This is worth doing from the start; retrofitting a tiebreaker onto a pagination system already in production means every existing cursor (every bookmarked "load more" link, every client with a cached cursor) is now a different shape.

Encoding the cursor

Don't hand the client raw column values to echo back — it works, but it exposes your schema and invites someone to construct a cursor by hand and probe around it. Encode the cursor as an opaque token instead:

function encodeCursor(createdAt: Date, id: string): string {
  return Buffer.from(JSON.stringify({ createdAt: createdAt.toISOString(), id })).toString("base64url");
}

function decodeCursor(token: string): { createdAt: Date; id: string } {
  const { createdAt, id } = JSON.parse(Buffer.from(token, "base64url").toString());
  return { createdAt: new Date(createdAt), id };
}

base64url (not plain base64) matters if the token ever ends up in a URL query string — plain base64's + and / characters need escaping there and cause bugs that only show up for specific cursor values.

// app/api/posts/route.ts
export async function GET(req: Request) {
  const cursorParam = new URL(req.url).searchParams.get("cursor");
  const cursor = cursorParam ? decodeCursor(cursorParam) : null;

  const rows = await db.post.findMany({
    where: cursor
      ? { OR: [
          { createdAt: { lt: cursor.createdAt } },
          { createdAt: cursor.createdAt, id: { lt: cursor.id } },
        ] }
      : {},
    orderBy: [{ createdAt: "desc" }, { id: "desc" }],
    take: 20,
  });

  const nextCursor = rows.length === 20
    ? encodeCursor(rows[rows.length - 1].createdAt, rows[rows.length - 1].id)
    : null;

  return Response.json({ items: rows, nextCursor });
}

nextCursor comes back null when the page came back short — that's your signal there's no next page, without a separate count query.

What you give up, and when that's fine

Cursor pagination can't jump to "page 7" — there's no way to compute the cursor for an arbitrary page without walking through every page before it, because the cursor only makes sense relative to a row you've already fetched. If the product genuinely needs numbered pages with direct jumping (an admin table, a paginated export, anything where the dataset is small and mostly static), that's a real reason to keep offset pagination — it's not automatically wrong, it's wrong specifically for actively-written, infinite-scroll-style lists.

The same goes for total counts. SELECT COUNT(*) alongside a cursor query is expensive on a large table and answers a question ("how many total") that a "Load more" UI usually doesn't need to display. If the design calls for "showing 1–20 of 4,213," that's a signal the page wants offset semantics, or a separate, deliberately-approximate count query — not a reason to force cursors into a UI shape they don't fit.

Rule of thumb: cursor pagination for anything ordered by recency that users actively add to (feeds, comments, logs, notifications). Offset pagination for anything small, mostly-static, or where jumping to an arbitrary page is a real requirement, not just what a table component happened to render by default.

Related: Working with APIs · Performance optimization · Database design with AI