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 for a Growing API

For developer entrepreneurs shipping a paid API or powering a product with one, pagination is an architecture decision that quietly drives three things you care about: customer-visible latency, infrastructure cost, and incident frequency. Get it wrong, and your support inbox fills up with slow responses and duplicated or missing rows. Get it right, and your API stays fast, predictable, and cheap to operate as usage scales.

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:

  • Small datasets (hundreds to low thousands of rows)
  • Static or rarely-changing data
  • Admin interfaces where jumping to page 50 is a real requirement
  • Simple implementations where developer time is the bottleneck

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. The cost is paid in compute time on every request, which directly translates to higher database spend and slower responses for the customer waiting on the other end.

A second problem is data drift. If rows are inserted or deleted between paginated requests, the user sees duplicate records or gaps. This is not just a UX annoyance; it can break downstream integrations that assume stable row identity, triggering support tickets and eroding trust in your API.

Cursor-Based Pagination

Cursor pagination replaces the numeric offset with a cursor value—typically an 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:

  • Large or growing datasets
  • Frequently updated or real-time data
  • Infinite scroll interfaces
  • APIs where consistent ordering is more important than random access

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. On small tables this is negligible, but on large tables the count query itself becomes expensive and can dominate response time as the dataset grows. Where an exact total is not necessary, totals can also be approximated (for example, with cached estimates updated on a schedule or computed over a sampled subset) rather than recomputed on every request. Decide whether the UX benefit justifies the performance cost for your specific API, and qualify that decision by table size rather than treating the count as universally cheap.

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:

  • When you want cursor-like performance without opaque tokens
  • When the sort column is unique or you can add a tiebreaker column
  • When debugging and manual API exploration matter

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

On “Equivalence” Between Keyset and Cursor

It is common to hear that keyset and cursor pagination are “equivalent when implemented correctly” from a database perspective. That is true at the level of the query plan: both translate to a seek on an indexed sort key plus tiebreaker, and both avoid the deep-offset scan that plagues offset pagination. But the equivalence stops at the database boundary. Caching strategies differ because cursor tokens are opaque while keyset values are predictable; stability under concurrent inserts depends on the tiebreaker design rather than the token format; and the client contract differs sharply, since keyset exposes your sort column publicly while cursor hides it. Treat the two as equivalent in query cost, not as interchangeable in API design.

When to Choose Each Pattern

The thresholds below are guidance, not laws of physics. They reflect where the trade-offs tend to flip for typical workloads on indexed tables; actual break-even points depend on row width, index selectivity, concurrency, and database engine.

Choose offset pagination when:

  • Your dataset is small enough that deep-offset scans stay under your latency budget, and growth is slow
  • Your API consumers need to jump to specific pages
  • You are building an admin tool or report where page numbers are expected
  • The simplicity of LIMIT/OFFSET outweighs the performance cost

Choose cursor pagination when:

  • Deep-offset latency or cost has become a measurable problem, or your data grows fast enough that it will become one
  • Data is updated frequently and consistency across pages matters
  • You are building a feed, timeline, or infinite scroll experience
  • You want to avoid data drift without exposing sort columns to clients

Choose keyset pagination when:

  • You want stable, performant pagination without opaque tokens
  • Your sort column is stable and unlikely to change
  • Debugging and manual API testing are important to your workflow
  • You are willing to bake the sort column into your public contract

Pagination and API Reliability Operations

Pagination choice is not just a query-design question; it feeds directly into observability and incident response. Deep-offset requests are a classic signal of client misuse: a script looping to page 500 of a million-row endpoint will quietly burn database CPU until something breaks. Treat high-offset traffic as an alertable signal. Monitor the distribution of offset values per endpoint, and watch for any single client driving a disproportionate share of deep-page requests. Pair that with a hard cap on limit to protect uptime. A maximum page size is not merely a convenience for the client; it is a load-shedding control on your side, and one of the cheapest reliability levers you have.

Cursor pagination interacts with reliability differently. Because it forces sequential traversal, it naturally limits how much data a single client can pull in a given window, and it makes rate-limit budgets easier to reason about. It also makes incident diagnosis cleaner: when a customer reports a missing row, a cursor-based client contract is far easier to replay deterministically than an offset-based one whose results shifted under concurrent writes.

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, and gives you a knob to pull during an incident.

  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 page and per_page parameters for offset-style pagination, while still exposing standard Link headers (including rel="next" and rel="prev") that many clients consume as if they were cursor pointers. If you offer more than one pattern, 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. Their database query plans are typically identical, but caching behavior, stability under concurrent inserts, and the public API contract differ in ways that matter for reliability and evolution.

Sources