Cursor vs Offset Pagination in a REST API

Pagination looks like the most boring decision in an API design, right up to the point where a client reports that a row they already processed showed up a second time, or that a row they never saw is missing entirely. Both of those are the expected behaviour of offset pagination over a table that is still being written to, and neither is a bug in the client. Choosing between offset and cursor pagination is really a choice about which failure mode you are willing to accept.

What offset pagination actually promises

Offset pagination is the one almost every API starts with, because it maps directly onto SQL: LIMIT 50 OFFSET 100 asks the database to produce an ordered result set, throw away the first hundred rows, and return the next fifty. The interface is easy to reason about and easy to document, and it gives clients something genuinely useful that cursors do not — the ability to jump to an arbitrary page, and, if you also run a COUNT, a total number of pages to render in a UI.

-- page 3, 50 per page
SELECT id, origin, destination, created_at
FROM loads
WHERE broker_id = %s
ORDER BY created_at DESC
LIMIT 50 OFFSET 100;

What it does not promise is that page 3 contains the same rows on two consecutive requests. The offset is a position in a result set that is recomputed from scratch every time. If two new loads are inserted between the client's request for page 2 and its request for page 3, every row shifts two positions later, and the two rows that were at the bottom of page 2 reappear at the top of page 3. Delete rows instead of inserting them and the shift goes the other way: rows slide up past the boundary and are never returned to the client at all.

The second problem: offset gets slower the deeper you go

The performance cost is easy to miss in development, because it only shows up at depth. OFFSET 100 is cheap. OFFSET 500000 is not, and the reason is that the database has no way to skip rows without first producing them: it walks the ordered result set, counts off half a million rows, discards every one of them, and only then starts collecting the fifty you asked for. The work is proportional to the offset, not to the page size, so the last page of a large table is dramatically more expensive to serve than the first — and it is the pages nobody looks at that cost the most.

This is also why adding an index does not fix it. An index on the sort column makes the ordering free, but the rows still have to be traversed and discarded one by one — the same class of problem covered in SQL joins and indexes, where the query plan does far more work than the returned row count suggests.

Cursor pagination: paginate by value, not by position

Cursor pagination — sometimes called keyset or seek pagination — replaces "skip the first N rows" with "give me rows after this specific one." Instead of a page number, the client sends back an opaque token encoding the sort values of the last row it received, and the server turns that into a WHERE clause:

SELECT id, origin, destination, created_at
FROM loads
WHERE broker_id = %s
  AND (created_at, id) < (%s, %s)   -- the cursor
ORDER BY created_at DESC, id DESC
LIMIT 50;

Two things change as a result. First, the query is now a range scan that starts at a known point in the index, so it costs the same whether the client is on its first page or its ten-thousandth — the depth of the pagination has stopped mattering. Second, and more importantly for correctness, newly inserted rows no longer shift the boundary. The cursor names a row, and "everything after that row in this ordering" is a stable answer even while the table is being written to.

Why the cursor needs a tiebreaker

The (created_at, id) tuple in that query is the part most easily got wrong, and the reason it matters is worth being explicit about. If the cursor were just created_at < %s, then any two rows sharing the same timestamp would be ordered arbitrarily relative to each other, and different from one query to the next. A page boundary falling in the middle of such a group means rows get skipped or repeated — the exact problem cursors were supposed to solve, reintroduced by a non-unique sort key. Timestamps collide far more often than people expect, especially for rows written by the same batch job in the same transaction.

The fix is to make the sort key unique by appending a column that already is, usually the primary key, and to compare the whole tuple rather than the columns separately. (created_at, id) < (:ts, :id) is not the same condition as created_at <= :ts AND id < :id, and writing the second one by hand is a common source of rows quietly vanishing at page boundaries. Databases that support row-value comparison will use a composite index on (created_at, id) for the tuple form directly, which is what makes the range scan efficient.

Encoding the cursor so it stays opaque

A cursor should be opaque to the client, not because the values are secret, but because making it opaque is what preserves your freedom to change the sort key later without breaking every stored cursor in the wild. Base64-encoding a small JSON payload is enough:

import base64, json

def encode_cursor(row) -> str:
    payload = {"created_at": row["created_at"].isoformat(), "id": row["id"]}
    return base64.urlsafe_b64encode(json.dumps(payload).encode()).decode()

def decode_cursor(token: str) -> dict | None:
    if not token:
        return None
    try:
        return json.loads(base64.urlsafe_b64decode(token.encode()))
    except (ValueError, TypeError):
        raise ValueError("malformed cursor")

Note that decode_cursor raises rather than silently returning None on a malformed token. Treating an unparseable cursor as "start from the beginning" is the friendlier-looking choice and the wrong one: a client with a corrupted token would silently restart its pass over the data and reprocess everything, which is much harder to notice than a 400 response. This is the same principle as the explicit-failure argument in integrating third-party APIs — an error you can see beats a default that quietly does something expensive.

Telling the client whether more pages exist

Offset pagination can report a total count; cursor pagination generally should not, because computing one requires the full scan you just eliminated. What clients need is not a total but an answer to "is there more?" — cheap to provide by requesting one row more than the page size and using its presence as the signal:

rows = fetch(limit=page_size + 1, cursor=cursor)
has_more = len(rows) > page_size
rows = rows[:page_size]

return {
    "data": rows,
    "next_cursor": encode_cursor(rows[-1]) if has_more and rows else None,
    "has_more": has_more,
}

Returning next_cursor: null at the end of the data, rather than omitting the field, gives client authors an unambiguous loop-termination condition. A client that pages until next_cursor is null does the right thing; a client that pages until it receives fewer rows than it asked for is relying on an assumption you may not want to guarantee.

Which one to pick

Use offset for admin tables and internal dashboards where a human is clicking through pages, the dataset is small, arbitrary page jumps are genuinely useful, and a duplicated row on a page boundary is a cosmetic annoyance rather than a correctness problem.
Use cursors for anything a program consumes: sync endpoints, export jobs, webhook backfills, event feeds, and infinite-scroll clients. Anywhere a caller will page through the whole collection and act on each row exactly once, a stable boundary is not a nicety.
Do not let a client choose an unbounded page size. Clamp it server-side and document the ceiling, whichever scheme you use.
Version the cursor format if the sort key might change. A version prefix inside the encoded payload costs nothing now and saves a migration later.

The deeper point is that pagination is part of an API's contract about consistency, not just about page size. Offset says "here is a window onto the data as it is right now." Cursors say "here is where you left off." For anything automated, the second promise is the one that keeps a client's copy of the data correct — and the same reasoning about making retries safe rather than merely possible drives the design in idempotency keys in FastAPI.