Currently Available: Need a skilled Software Developer for your next project?
Categories
APIs

Backend Interview Prep: Cursor vs. Offset Pagination

When a backend engineer builds a REST API endpoint that returns many records, pagination determines which records each request returns. Offset pagination skips a set number of records. Cursor pagination continues from a marker showing where the previous response ended.

Offset pagination fits numbered pages and direct jumps. Cursor pagination often fits forward traversal through large or frequently changing collections because it avoids counting past every earlier row. Neither approach is universally better, and neither guarantees that multiple requests see an unchanged collection. In an interview, explain how the choice affects database work, ordering, concurrent writes, and the client’s navigation needs.

How the two methods select a page

An offset request identifies a page by its position in the ordered result set:

GET /orders?limit=25&offset=50

The server skips the requested number of matching records and returns up to the requested limit. A client can use the same pattern to jump to a numbered page, but the database still has to find and pass over the skipped rows. As the offset grows, that work can become more expensive. PostgreSQL documents this behavior in its guidance on LIMIT and OFFSET.

A cursor request identifies where to continue:

GET /orders?limit=25&after=opaque-token

The token identifies a position in the ordered collection. The server can use that position to build a query condition, such as “return records after the last record in the previous response.” An API can expose a token without revealing its contents. Keyset pagination is a common implementation: the database compares ordered column values to continue from the last row.

These terms describe different parts of pagination. A cursor is part of the API contract, which defines how clients use the endpoint. Keyset pagination is one way the database can use that cursor to find the next records. For example, Stripe’s Report Runs endpoint uses object IDs with starting_after and ending_before, while GitHub documents pagination through response links and endpoint-specific parameters. Those examples show that REST APIs do not share one required pagination format.

Make the ordering unique

Both methods need a defined order. Without ORDER BY, a database does not promise to return results in a stable order. If two records have the same value in the sort column, their relative order is unclear. Add a unique value, such as each record’s primary key, to determine their order.

For an orders list sorted newest first, the query might use:

ORDER BY created_at DESC, id DESC

The matching keyset query can then continue after the last row:

SELECT id, created_at, status
FROM orders
WHERE account_id = $1
  AND (created_at, id) < ($2, $3)
ORDER BY created_at DESC, id DESC
LIMIT $4;

Here, $2 and $3 are the last row’s created_at and id values. Because both columns sort in descending order, the less-than comparison selects lower values in that same order. When sort directions are mixed, predicates must match the database’s actual ordering behavior. They must also account for nullable columns and text collation rules.

An index that matches the account filter and ordering is a reasonable candidate:

CREATE INDEX ON orders (account_id, created_at DESC, id DESC);

A matching B-tree index, which stores values in an ordered structure, can let PostgreSQL return rows in order without sorting the entire result. This is especially useful when a query uses ORDER BY with LIMIT. The database might choose a different plan, so inspect the query plan and test with data that represents your workload. See PostgreSQL’s explanation of indexes and ORDER BY.

Explain what concurrent writes change

Offset boundaries can shift between requests. Suppose a descending list returns records 100 and 90 on the first page. A new record, 95, arrives before the client requests the next page with an offset of two. The second request can return 90 and 80, repeating record 90 because the insertion shifted the positions.

A keyset request after record 90 asks for records with lower values in the sort order. The new record does not shift that continuation point, so this request avoids the duplicate caused by the insertion. It does not freeze the collection. If a record’s sort key changes, the record can move across the cursor boundary; deletions can also remove records from later pages.

Separate HTTP requests also do not automatically share one database snapshot. PostgreSQL’s default Read Committed isolation gives each command a view of data committed before that command began, so later requests can see newer changes. Explain the distinction in an interview: keyset pagination improves continuation through an ordered result, while snapshot consistency is a separate requirement with separate design costs. PostgreSQL describes this behavior in its documentation on transaction isolation.

Match the API contract to client navigation

Choose offset pagination when clients need numbered pages or direct jumps and the database can handle the work of skipping many records. Choose cursor pagination when clients mainly move forward through a large or frequently changing collection, such as a feed or event list. The choice depends on the product’s navigation needs and the database’s measured query plans, not on a universal dataset-size threshold.

The response should tell clients how to continue and how to recognize the end. An API might return a next-page link, a continuation token, or a has_more field. GitHub uses Link response headers; Stripe’s Report Runs endpoint returns has_more. Clients should follow the format the API documents rather than assume a universal convention.

Document a default page size, validate each requested value, and enforce a maximum suited to the workload. When a client follows a continuation link, preserve relevant filters and sort order so the next request continues the same query. For opaque tokens, avoid exposing internal query details and authorize every request independently; possession of a continuation token should not grant access. Google’s AIP-158 pagination guidance offers useful token design advice, though it addresses RPC collection methods rather than establishing a REST standard.

A clear interview answer

A strong answer connects the API choice to the database behavior:

Offset pagination is simpler and supports direct page-number navigation, but deep offsets make the database pass over earlier rows, and inserts can shift page boundaries. Cursor pagination continues from a position in a deterministic order, often through a keyset query backed by a matching index. It suits forward traversal through large or changing collections, but it makes arbitrary page jumps harder and does not guarantee a snapshot across requests. I would choose based on the client’s navigation needs, define a unique sort order, cap page sizes, and verify the query plan.

Expect follow-up questions about duplicate or missing records, what a cursor contains, exact totals, and index design. Tie your answers to the implementation. A unique tiebreaker makes the continuation position clear. A record can still move when its sort key changes. Returning an exact count has its own API and database costs. An index helps only when its columns and order match the query.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com

Email updates

Usually a new article and a few links I found interesting.

No spam. Unsubscribe with one click.

Leave a Reply

Your email address will not be published. Required fields are marked *