Cursor vs. offset pagination for APIs: how to choose without duplicate rows

The choice between OFFSET and a cursor depends on how the client walks the list. If it needs to open page 37, offset is simple and direct. If it needs the next part of a feed, export rows, or sync a table that keeps changing, an ordering-based cursor usually protects continuity better.

There is no universally better option. OFFSET expresses a numeric position. A cursor, also called keyset or seek pagination, expresses a position relative to the last row received. The decision is safe when the API keeps the same ordering, uses a unique tie-breaker, and treats the cursor as an opaque contract.

This page fills a gap beside the guide to a scalable GraphQL API with Node.js. That older guide mentions cursor pagination as a performance choice, but it does not cover the contract between the endpoint, database, and client. Here the question is narrower: which model should you choose, and how do you keep a changing list from repeating or skipping rows?

Short answer

• Use OFFSET for small or stable lists and interfaces that need to jump to a specific page.
• Use a cursor for feeds, exports, syncs, and lists that change while they are being read.
• Order by a unique key such as (created_at, id), not by a timestamp that can tie.
• Generate opaque cursors on the server and bind them to the filter, ordering, and contract version.
• Test pagination with inserts and deletes between requests. A short page does not prove that the list ended.

The practical difference between offset and cursor

With offset pagination, a client asks for something like page=3&limit=20, and the server turns it into OFFSET 40 LIMIT 20. The database has to consider the first 40 rows before returning the next 20. PostgreSQL documents that rows skipped by OFFSET still have to be computed and that a large offset can be inefficient (PostgreSQL, "LIMIT and OFFSET").

With cursor pagination, the client sends the position it received from the previous page:

GET /posts?limit=20&after=eyJ2IjoxLCJjcmVhdGVkQXQiOiIyMDI2LTExLTA3VDEwOjAwOjAwWiIsImlkIjo0MjF9

The server translates that cursor into an ordering condition. For a list ordered by date and ID in descending order, the query can be:

CREATE INDEX posts_feed_order_idx
  ON posts (created_at DESC, id DESC);

SELECT id, title, created_at
FROM posts
WHERE (created_at, id) < ($1::timestamptz, $2::bigint)
ORDER BY created_at DESC, id DESC
LIMIT $3;

The < operator is not universal. It matches this descending order. If the query is ascending, the condition and index must follow that direction. The point is to replace "skip N rows" with "continue after this ordered position."

When is offset the right choice?

Offset is still a good choice when the use case needs random access and the collection is small or changes infrequently. An admin table that lets someone jump directly to page 10 may prefer page and limit because the page number is part of the interface and a total can be useful there.

Use offset when these properties are true:

Even in this case, define ORDER BY with a unique ordering. PostgreSQL warns that LIMIT without a predictable order can return inconsistent subsets because SQL does not promise an implicit order. Offset does not create an order. It only skips part of the ordered result.

When is a cursor better?

A cursor is stronger when the client walks forward or backward and does not need random access by page number. Microsoft Learn describes keyset pagination as an alternative that uses the values from the last row in a WHERE clause. The same documentation points out two important limits: keyset does not support arbitrary jumps, and it needs a fully unique ordering.

Typical cases include feeds, event histories, exports, incremental syncs, and workers that resume a read. The question is not "what is page 37?" It is "which row comes after the last one I confirmed?" If the table receives an insert while the client is working, the cursor continues from the received position while an offset can shift the window.

A cursor also lets an API change its database implementation without exposing internal details to clients. The GraphQL pagination guide recommends opaque cursors and notes that a cursor can hide whether the implementation uses an ID, an offset, or another representation. Opacity does not solve consistency by itself, but it keeps clients from depending on internal format.

The ordering must be unique

The most common cursor mistake is ordering only by created_at. Two rows can share the same timestamp. If the cursor stores only that value, the next query cannot reliably separate the row already seen from the row that still needs to appear.

Use a composite key that ends with a unique field:

SELECT id, created_at, title
FROM posts
WHERE (created_at, id) < ($1::timestamptz, $2::bigint)
ORDER BY created_at DESC, id DESC
LIMIT 20;

The pair (created_at, id) defines a total order for the set. EF Core's pagination documentation uses the same idea by combining date and ID, and the JSON:API cursor profile requires the server to add ordering constraints until the order is unique (JSON:API, "Cursor Pagination").

The index must follow the query. Otherwise replacing offset with a more complex WHERE clause may only move the cost elsewhere. Check with EXPLAIN (ANALYZE, BUFFERS) against the database and traffic the endpoint actually receives. This page does not provide a benchmark for a particular database.

A cursor is an API contract, not an exposed ID

A cursor can contain the contract version, ordering values, and a fingerprint of the filters used by the first request:

type PostCursor = {
  v: 1;
  sort: "created_at_desc_id_desc";
  createdAt: string;
  id: number;
  filterHash: string;
};

export function encodeCursor(cursor: PostCursor): string {
  return Buffer.from(JSON.stringify(cursor)).toString("base64url");
}

export function decodeCursor(value: string): PostCursor {
  let parsed: unknown;

  try {
    parsed = JSON.parse(Buffer.from(value, "base64url").toString("utf8"));
  } catch {
    throw new Error("invalid cursor");
  }

  if (
    !parsed ||
    typeof parsed !== "object" ||
    (parsed as Record<string, unknown>).v !== 1 ||
    (parsed as Record<string, unknown>).sort !== "created_at_desc_id_desc" ||
    typeof (parsed as Record<string, unknown>).createdAt !== "string" ||
    !Number.isSafeInteger((parsed as Record<string, unknown>).id) ||
    typeof (parsed as Record<string, unknown>).filterHash !== "string"
  ) {
    throw new Error("incompatible cursor");
  }

  return parsed as PostCursor;
}

The example is illustrative. It still needs date validation, a maximum size, a signature, or another protection appropriate to the endpoint's risk. Do not treat the cursor's id as authorization. It is only a position. The query still needs to apply tenant, permission, and current-filter checks.

The filter hash prevents a client from using a cursor for status=published in a request for status=draft. Another option is to reject incompatible filters explicitly. When ordering changes, increment the cursor version instead of silently interpreting the old format.

Test the window between two pages

An implementation is not validated because two consecutive requests returned the expected rows. Insert and delete rows between requests, then check the property the product needs: no duplicate, no skip, or simply no repeated effects in an exporter.

This JavaScript fixture runs the core rule without a database. It uses the same (createdAt, id) ordering as the SQL:

import test from "node:test";
import assert from "node:assert/strict";

function compareDesc(a, b) {
  return b.createdAt.localeCompare(a.createdAt) || b.id - a.id;
}

function pageAfter(rows, cursor, limit) {
  const ordered = [...rows].sort(compareDesc);
  const start = cursor
    ? ordered.findIndex(
        (row) =>
          row.createdAt === cursor.createdAt && row.id === cursor.id,
      ) + 1
    : 0;
  const items = ordered.slice(start, start + limit);
  const last = items.at(-1);

  return {
    items,
    nextCursor: last
      ? { createdAt: last.createdAt, id: last.id }
      : null,
  };
}

test("does not repeat a row when a newer row arrives", () => {
  const rows = [
    { id: 4, createdAt: "2026-11-07T10:04:00.000Z" },
    { id: 3, createdAt: "2026-11-07T10:03:00.000Z" },
    { id: 2, createdAt: "2026-11-07T10:02:00.000Z" },
    { id: 1, createdAt: "2026-11-07T10:01:00.000Z" },
  ];

  const first = pageAfter(rows, null, 2);
  const second = pageAfter(
    [...rows, { id: 5, createdAt: "2026-11-07T10:05:00.000Z" }],
    first.nextCursor,
    2,
  );

  assert.deepEqual(first.items.map((row) => row.id), [4, 3]);
  assert.deepEqual(second.items.map((row) => row.id), [2, 1]);
});

Save the block as pagination.test.mjs and run node --test pagination.test.mjs. The test demonstrates the in-memory ordering property, not PostgreSQL execution, transaction concurrency, or a provider's guarantee. For an integration proof, run the same matrix against the real database and endpoint.

Failures that appear after the first deploy

Some problems survive a happy-path demo:

• Timestamp without a tie-breaker: two rows with the same visible precision can fall around the cursor ambiguously. Store a unique key as well and preserve its precision in the cursor.
• Changed filter or ordering: a cursor is valid only for the query that produced it. Reject incompatibility instead of returning a plausible-looking page.
• Client-created cursor: base64 is not a signature. If the cursor carries tenant, filter, or permission state, validate integrity and re-check authorization on the server.
• Inferring the end from a short page: filters, provider limits, and concurrent reads can return fewer rows without meaning the list ended. Prefer a server-defined hasMore or nextCursor: null.
• Counting every response: COUNT(*) may cost more than reading the page. Include it only when the interface needs it.
• Deletion between pages: a cursor should keep representing a position even when the row that created it was deleted. Do not require a lookup of that row before continuing.

If the product needs a consistent snapshot of a collection, a normal cursor is not enough. You need a snapshot strategy, a versioned window, or a dedicated export mechanism. The JSON:API cursor profile allows a server to encode a session identity when the list cannot change between requests, but that adds its own retention and operational cost.

A checklist for the decision

Before publishing the endpoint, answer these questions:

1. Does the client need arbitrary page jumps, or only next and previous navigation?
2. Does the collection change while the client reads it?
3. Does the ordering end in a unique, indexed key?
4. Does the cursor preserve version, filter, and direction without exposing details the client can edit?
5. Does the response reliably say whether another page exists?
6. Does the fixture force inserts, deletes, timestamp ties, invalid cursors, and changed filters?

If the answers point to sequential navigation over a changing list, start with cursor/keyset pagination. If the main requirement is random access over a small, stable list, offset may be the simpler interface. In either case, unique ordering and tests for changes between pages matter more than the parameter name.

This page is a technical synthesis with local examples, not a benchmark. The JavaScript test was run with node --test; the SQL must be checked against your database version, index plan, and consistency policy. For a related proof about data arriving out of order, see the eventual consistency tests without fixed sleeps. For an API that must resume an export after failure, durable execution with queues and checkpoints helps separate confirmed position from pending work.

Sources consulted

• PostgreSQL, "LIMIT and OFFSET", accessed 2026-11-07.
• Microsoft Learn, "Pagination", accessed 2026-11-07.
• GraphQL, "Pagination", accessed 2026-11-07.
• JSON:API, "Cursor Pagination Profile", accessed 2026-11-07.
• Zendesk Developer Docs, "Comparing cursor pagination and offset pagination", accessed 2026-11-07.