Cursor vs Offset Pagination: Choosing What Fits Your API

Pagination is easy to underestimate. The first page looks correct, the response is small, and the API has a limit parameter. The awkward cases arrive later: someone inserts a record between requests, two rows share a timestamp, or page ten thousand becomes much slower than page one.

Choosing a pagination strategy starts with the way people navigate the data. A numbered administration screen and an activity feed do not need the same contract.

Offset pagination fits page numbers

Offset pagination skips a number of rows before returning the next set. For twenty items per page, the third page uses an offset of forty.

SELECT id, created_at, title
FROM posts
WHERE published = true
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 40;

It is straightforward to expose as ?page=3, and it supports jumping to a known page number. That makes it a reasonable choice for small lists, occasional reports, and interfaces where direct navigation matters more than continuous scrolling.

The ordering still needs to be explicit and deterministic. PostgreSQL warns that skipped rows still need to be computed and that inconsistent ordering makes subsets unpredictable in its LIMIT and OFFSET documentation.

Moving data changes page boundaries

Suppose the first request returns the newest twenty posts. Before the next request, a new post is inserted at the top. The position of older rows shifts, so offset twenty can now include something the reader already saw.

Deletions can produce the opposite problem: an unseen item moves into the skipped portion. This does not make offsets unusable, but the interface should tolerate the behavior or use a stable snapshot when stronger consistency is required.

For a short blog archive, that tradeoff may be perfectly acceptable. For a fast-moving event feed, it can be distracting.

Cursor pagination continues from a position

A cursor records the ordering position of the last item returned. The next query asks for rows after that position rather than counting how many rows to skip.

For descending timestamps with a descending ID tie-breaker, a PostgreSQL query can look like this:

SELECT id, created_at, title
FROM posts
WHERE published = true
  AND (created_at, id) < ($1::timestamptz, $2::bigint)
ORDER BY created_at DESC, id DESC
LIMIT 21;

Return the first twenty rows. The extra row tells the application whether another page exists. Build the next cursor from the last returned row, not from that extra row.

Use non-null ordering fields for this design and an index that supports the filter and order. The PostgreSQL ordering guide explains why matching an access path to ORDER BY matters.

Treat the cursor as an API contract

A cursor can contain a version, timestamp, and ID encoded as a token. Encoding makes transport convenient; it does not make the contents secret or trustworthy.

Validate the decoded fields and reject malformed tokens. If you sign the token, verify the signature before using its values. Always derive authorization filters from the authenticated caller rather than accepting an account ID inside a cursor as proof of access.

Bind the cursor to the active filter and sort order. A position created for newest-first published posts should not silently become a position in oldest-first drafts.

Document the response clearly: items, next cursor, and whether more results exist. Decide whether an absent cursor and an invalid cursor mean different things so clients can recover predictably.

Stable ordering is necessary, not magical

A timestamp alone is not unique. Without a tie-breaker, records sharing the same timestamp can be skipped or repeated at the boundary.

Even a well-designed cursor does not freeze the database. If the ordering field changes after a row has been visited, that row can move. New records and deletions can still affect what is available. Immutable ordering fields help; a true snapshot requires an additional consistency strategy.

Test ties, concurrent inserts, deletion of the boundary row, an empty result, and the final page. Also test a cursor reused with another account or filter.

Choose the navigation you need

Use offsets when page numbers and simple navigation are valuable and the expected dataset makes the cost acceptable. Use cursors when traversing a changing collection efficiently is more important than jumping to an arbitrary numbered page.

Neither design automatically provides an exact total count. Treat counts as a separate product requirement, and measure their cost instead of attaching them to every request by habit.