Aivorab
Editorial illustration: Cursor Pagination vs Offset Pagination: When Each REST API Pattern Makes Sense

API Design · Pagination · REST · Performance · Best Practices

Cursor Pagination vs Offset Pagination: When Each REST API Pattern Makes Sense

A practical comparison of cursor-based, offset-based, and keyset pagination for REST APIs—trade-offs, implementation guidance, and when to choose each approach for growing datasets.

Published:

The Short Answer

Offset pagination is fine for small, static datasets where users need to jump to arbitrary pages. Cursor pagination is the right default for growing or frequently updated collections because it avoids the performance degradation and data-drift problems that offset-based approaches introduce at scale. Keyset pagination is a middle ground that gives you stable navigation without the cursor encoding complexity.

Why Pagination Choice Matters

When your API serves thousands or millions of records, returning everything in one response is not an option. Pagination breaks results into manageable chunks, but the method you choose directly affects query performance, client complexity, and data consistency as your dataset grows.

The three patterns you will encounter in REST API design are offset-based pagination, cursor-based pagination, and keyset pagination. Each has distinct trade-offs.

Offset-Based Pagination

Offset pagination uses two parameters: an offset (the starting row number) and a limit (how many rows to return). This maps directly to the SQL LIMIT and OFFSET clauses that most developers already know.

How it works:

GET /api/products?offset=0&limit=10
GET /api/products?offset=10&limit=10
GET /api/products?offset=20&limit=10

Where it works well:

Where it breaks down:

The fundamental problem is that the database must scan and discard every row before the offset. If you request offset 50,000 with a limit of 10, the database reads 50,010 rows and discards the first 50,000. As the offset grows, query time grows proportionally. This is not a theoretical concern—it is a direct consequence of how B-tree and heap scans work in relational databases.

A second problem is data drift. If rows are inserted or deleted between paginated requests, the user sees duplicate records or gaps. Contentful documented this exact issue when they migrated their content APIs away from offset pagination: content added or removed between page requests caused results to shift unpredictably.

Cursor-Based Pagination

Cursor pagination replaces the numeric offset with a cursor value—typically a opaque token that encodes the position in the result set. The client passes the cursor from the previous response to fetch the next page.

How it works:

GET /api/products?limit=10
→ response includes next_cursor: "eyJpZCI6MTIzNH0="

GET /api/products?limit=10&cursor=eyJpZCI6MTIzNH0=
→ response includes next_cursor: "eyJpZCI6MTIzNX0="

Where it works well:

How to implement it correctly:

The cursor should encode the values used in the ORDER BY clause, not just a row ID. A cursor based only on an auto-incrementing ID fails when rows share the same sort key or when the sort column is not unique. A robust implementation encodes the ordering columns plus the ID as a tiebreaker:

SELECT * FROM products
WHERE (created_at, id) > ('2024-01-15T10:30:00Z', 1234)
ORDER BY created_at ASC, id ASC
LIMIT 10;

The cursor token encodes created_at and id from the last row. This ensures deterministic ordering even when multiple rows share the same timestamp.

The trade-off:

Cursor pagination does not support random access. You cannot jump to page 50. You must walk the cursor sequentially. This is a deliberate design choice, not a limitation to work around. If your use case requires page numbers, offset pagination or keyset pagination is more appropriate.

Including a total count:

A common client request is to show “Page 3 of 47” with cursor pagination. This requires a separate COUNT(*) query, which adds latency. The fastapi-pagination library addresses this by allowing a custom cursor page type that includes the total, but it comes at the cost of an additional database query on every response. Decide whether the UX benefit justifies the performance cost for your specific API.

Keyset Pagination

Keyset pagination is closely related to cursor pagination but uses explicit column values instead of an opaque token. The client passes the last seen value of the sort column directly.

How it works:

GET /api/products?limit=10&after_id=1234
GET /api/products?limit=10&after_id=1235

Where it works well:

The trade-off:

Keyset pagination exposes your data model to the client. The sort column becomes part of the public API contract. If you need to change the sort order or column names, you break existing clients. Cursor pagination hides this detail behind an encoded token, making schema changes safer.

Comparison Summary

Concern Offset Cursor Keyset
Random page access Yes No No
Performance at deep offsets Degrades Stable Stable
Data drift resistance Poor Strong Strong
Implementation complexity Low Medium Low
Schema change resilience N/A Good Poor
Total count support Native Requires extra query Requires extra query

When to Choose Each Pattern

Choose offset pagination when:

Choose cursor pagination when:

Choose keyset pagination when:

Implementation Checklist

Regardless of which pattern you choose, these practices apply:

  1. Always sort explicitly. Never rely on implicit database ordering. An ORDER BY clause without an index will scan the entire table.

  2. Index your sort columns. Offset pagination at depth requires an index on the offset column. Cursor and keyset pagination require an index on the sort key plus any tiebreaker columns.

  3. Return navigation metadata. Include next_cursor or equivalent in every response. GitHub’s REST API uses Link headers with rel="next" and rel="prev" for this purpose. Cursor-based APIs should include both next and previous cursors when applicable.

  4. Document the cursor format. Whether opaque or explicit, clients need to know how to extract and pass the cursor value. Document whether the cursor is URL-safe, base64-encoded, or a raw value.

  5. Set a maximum page size. Allow clients to request a limit, but enforce an upper bound. This prevents a single request from overwhelming your database or your client’s memory.

  6. Test with concurrent writes. If your data changes while a client paginates, verify that the results are consistent. Cursor and keyset approaches handle this better than offset, but you should still validate the behavior for your specific workload.

FAQ

Can I combine offset and cursor pagination in the same API?

Yes. Some APIs offer offset pagination for simple use cases and cursor pagination for large datasets. The GitHub REST API uses offset-based pagination with page numbers. If you offer both, document which endpoints use which pattern and make the distinction clear in your OpenAPI specification.

Does cursor pagination work with filtering?

Yes, but the cursor must encode the filter conditions as well as the sort key. A cursor that only encodes the sort position will produce incorrect results when filters change the result set. Include filter parameters in the cursor token or require clients to pass the same filters with every paginated request.

What about GraphQL pagination?

GraphQL’s Relay connection spec uses cursor-based pagination by design. The cursor in GraphQL typically encodes the node ID and position, making it compatible with the cursor approach described here. If you are building a GraphQL API, follow the Relay spec rather than inventing a custom pagination model.

Is keyset pagination the same as cursor pagination?

They solve the same problem with different client-facing contracts. Keyset pagination exposes the sort value; cursor pagination hides it behind a token. From a database performance perspective, they are equivalent when implemented correctly.

Sources