Cursor Pagination for Real APIs: Ordering Guarantees, Opaque Tokens, and Edge Cases

You will build cursor pagination that stays correct while the underlying data changes underneath it. By the end you will know why offset pagination quietly corrupts results on a live dataset, how to pick an ordering that can actually guarantee stability, how to encode an opaque cursor that survives your own schema changes, and how to handle the edge cases that turn a tidy demo into a production bug report.

Pagination looks like the simplest feature in an API. It is not. It is a promise that a client can walk a large, changing collection in order, seeing each item once, without holding a server-side snapshot open. Every part of that sentence is a trap.

Why offset pagination breaks on real data

Offset pagination asks for rows by position: skip this many, take that many. It works perfectly on a dataset that never changes, which is to say on no dataset your API actually serves.

Say a client reads the first page of a feed ordered by creation time, newest first. Before they request the second page, another user inserts a new item at the front. Every existing item shifts down one position. The client’s second page now repeats the last item from the first page, because that item now sits at the position where the second page begins. An insert causes a duplicate. A delete causes the opposite: an item shifts into the window the client already passed and is never seen at all.

You can wave this away for an admin table. You cannot wave it away for a billing history, an audit log, an activity feed, or a sync protocol, which is precisely where clients paginate programmatically and notice missing or repeated records. Offset pagination also gets slower as the offset grows, because the database still has to visit and discard the rows you told it to skip. Deep pages on a large table are a self-inflicted performance problem.

Cursor pagination fixes both by addressing position by value instead of by count. The client returns a pointer to the last item it saw, and you return the items strictly after that value in a stable ordering. Inserts ahead of the cursor do not shift what comes after it. The query also uses an index range instead of a growing skip, so page cost stays roughly constant however deep the client walks.

The ordering guarantee is the whole design

A cursor is only as good as the ordering behind it, and that ordering must be total. Every pair of rows must compare as before or after, with no ties left unresolved.

Ordering by a timestamp alone is not total. Two rows created in the same transaction, or within the resolution of your timestamp column, share the same value. A cursor pointing at that value cannot say which of the tied rows the client has seen, so you will skip or repeat the ones on the boundary. This bug is subtle because it needs a tie exactly at a page edge to manifest, which means it survives casual testing and appears in production at the worst scale.

The standard fix is a tiebreaker. Order by your primary key secondarily, or rather, order by the pair: the sort column first, then a unique, immutable identifier. That pair is total, stable, and indexable. Your cursor stores both values, and your query compares both.

Two more properties matter. The sort values must be immutable for the life of the row’s presence in the collection. If a row’s sort key can change, for example ordering by a last-updated timestamp that updates on every edit, a row can move from behind the cursor to ahead of it mid-walk and be missed or revisited. If your product genuinely needs ordering by a mutable field, accept and document that a walk is a best-effort view, or snapshot the ordering for the walk in a way your design controls.

And the ordering must be backed by an index matching the sort and your filters. A cursor query that sorts without an index will scan and sort on every page, which defeats the performance reason you chose cursors at all.

Implement the keyset query

This pattern is called keyset pagination. Here it is in SQL for a collection ordered by creation time descending, with the identifier as tiebreaker:

SELECT id, created_at, actor, action
FROM events
WHERE (created_at, id) < ($cursor_created_at, $cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT $page_size_plus_one;

The row-value comparison does exactly the right thing: it selects rows strictly after the cursor position in the defined ordering, handling the tie on the timestamp by falling through to the identifier. If your database or query builder cannot express row-value comparisons cleanly, expand it manually into the equivalent condition with an OR of the primary comparison and the equal-primary-plus-tiebreaker comparison. Write that expansion carefully and test it with tied values, because hand-expanded keyset conditions are a reliable source of off-by-one errors at page boundaries.

Fetch one row more than the page size. That extra row is how you know whether another page exists without a separate count query, which on a large or heavily filtered table is exactly the expensive operation you were avoiding. Return the requested number of rows, and build the next cursor from the last row you returned, not from the extra row. If no extra row came back, there is no next page, and you should say so by returning a null next cursor rather than a cursor that yields an empty page.

Here is the same logic behind an HTTP handler in TypeScript:

type CursorPayload = {
  v: 1;
  createdAt: string;
  id: string;
};
 
function encodeCursor(payload: CursorPayload): string {
  return Buffer.from(JSON.stringify(payload), "utf8").toString("base64url");
}
 
function decodeCursor(raw: string): CursorPayload {
  let parsed: unknown;
  try {
    parsed = JSON.parse(Buffer.from(raw, "base64url").toString("utf8"));
  } catch {
    throw new Error("invalid_cursor");
  }
  const p = parsed as Partial<CursorPayload>;
  if (p.v !== 1 || typeof p.createdAt !== "string" || typeof p.id !== "string") {
    throw new Error("invalid_cursor");
  }
  return p as CursorPayload;
}
 
export async function listEvents(req: any, res: any) {
  const pageSize = clampPageSize(req.query.limit);
  let cursor: CursorPayload | null = null;
 
  if (typeof req.query.cursor === "string" && req.query.cursor.length > 0) {
    try {
      cursor = decodeCursor(req.query.cursor);
    } catch {
      return res.status(400).json({
        error: { code: "invalid_cursor", message: "The cursor is not valid." },
      });
    }
  }
 
  const rows = await queryEvents(cursor, pageSize + 1);
  const hasMore = rows.length > pageSize;
  const page = hasMore ? rows.slice(0, pageSize) : rows;
  const last = page[page.length - 1];
 
  return res.json({
    data: page,
    pagination: {
      nextCursor:
        hasMore && last
          ? encodeCursor({ v: 1, createdAt: last.created_at, id: last.id })
          : null,
      hasMore,
    },
  });
}

Clamp the page size on the server. Accept a client’s requested size only within bounds you set, with a documented default when none is given. An unbounded limit parameter is an invitation to request the entire table in one response, which converts your pagination work into a denial-of-service feature.

Make the cursor opaque, and keep it that way

A cursor is not a public identifier and it is not a query language. Encode it, include a version field, and treat its contents as your private implementation detail.

Opacity buys you three things. First, clients cannot construct cursors by hand, so they cannot probe positions in the collection they were not handed during a walk. Second, you can change the payload later, adding a field or switching the sort representation, by bumping the version and deciding whether old versions still decode. Third, clients stop depending on internals. The moment a client parses your cursor and builds logic on its contents, your encoding becomes a public contract you never agreed to.

Do not put sensitive data in the cursor. Encoding is not encryption. Anyone can decode a base64url string, so the payload should contain only the sort values needed to resume the walk, never personal data, permissions, or anything you would not print in a log. If you must prevent tampering, for example because the cursor also encodes a filter context you need to trust, sign it and verify the signature on decode. For an ordinary position cursor, tampering gains an attacker nothing they could not get by simply requesting pages, so validation of the decoded shape, as in the handler above, is usually sufficient.

On an invalid cursor, return a clear client error with a stable code. Do not silently restart from the beginning. A client with a corrupted or expired cursor that silently receives the first page will write duplicate data downstream and blame your API, and in fairness, your API made that outcome easy.

Edge cases that decide whether this works in production

Empty results. A filter matching nothing returns an empty list, a null next cursor, and a success status. Not an error, and not a cursor pointing at nothing.

The first page. No cursor means start at the beginning of the ordering. Do not require a special initial cursor value. Clients should begin by omitting the parameter entirely.

Backward pagination. Some clients need to walk in reverse, for example a chat history loading older messages after starting from a recent point. Support it deliberately with a before cursor and a reversed comparison, or do not support it at all. Reversing the results of a forward page on the client produces the wrong order and a cursor pointing the wrong way. Half-supported backward pagination is worse than none.

Counting. Clients will ask for a total count, and cursor pagination deliberately avoids one. A total over a changing collection is approximate the moment it is computed, and computing it exactly on every page reintroduces the cost you removed. Offer a separate count endpoint if the product needs one, labelled as an estimate where it is one, and keep it out of the page response so nobody builds a progress bar on a number that moves mid-walk.

Concurrent modification of the boundary row. If the exact row a cursor points at is deleted before the next request, a value-based cursor still works, because you compare values, not row existence. This is a genuine advantage over any scheme that needs to look the cursor row up. Preserve it by never implementing the next page as “find this row, then take the following rows” in application code.

Identifier choice. Use an identifier whose ordering is meaningful relative to your sort, or at least stable and unique. An identifier type with no useful ordering, handled only as an opaque tiebreaker in the comparison, still works, because the comparison treats it as a value, not a sequence. What does not work is a tiebreaker that is not unique. Uniqueness is the entire job.

That last row of the first table is the honest tradeoff. Cursor pagination cannot jump to page forty. If your interface is a human clicking numbered pages in an admin panel, offset is the right tool and its instability rarely matters. If your interface is a program walking a collection that must not miss or repeat records, numbered pages were never really on offer.

Document the walk like a contract

Your documentation should state the ordering in plain language, including the tiebreaker, because clients build reconciliation logic on it. It should state that cursors are opaque and expire with the query that produced them, that page size has a server-enforced maximum, and that a null next cursor is the only signal that a walk is complete. Show one full walk in an example, first request through final page, because most pagination bugs in client code come from mishandling that final page rather than the first.

Verdict

Choose cursor pagination whenever a client walks a changing collection programmatically and correctness matters more than random access. Build it on a total ordering with a unique tiebreaker, back it with a matching index, fetch one extra row instead of counting, and ship an opaque, versioned cursor that you validate on every request and bind to the query that produced it.

Keep offset pagination for small, human-browsed, page-numbered interfaces where its simplicity is worth more than its guarantees. The mistake is not picking either one. The mistake is picking offset by habit for a sync, export, or feed, and discovering its failure mode from a client’s reconciliation report instead of from your own tests.