Cursor vs Offset Pagination: A Deep Dive for API Designers

Pagination determines how clients traverse a collection and how the database finds the next records. Compare offset and cursor approaches against query depth, update frequency, sort order, and navigation requirements.

The Core Difference Between Cursor and Offset Pagination

Offset pagination tells the database: "skip N rows, return M." The API surface looks like:

GET /posts?limit=25&offset=100

The database translates this to LIMIT 25 OFFSET 100. This provides page-number navigation, but query cost and live-data changes need attention.

Cursor pagination tells the database: "give me the next M rows after this specific marker." The API surface looks like:

GET /posts?limit=25&after=eyJpZCI6MTAwfQ==

The cursor is an opaque token — typically a base64-encoded pointer to a specific row. The database translates this to a range query: WHERE id > 100 LIMIT 25. With a suitable index, the query can seek from the boundary instead of skipping earlier rows.

Why Cursor vs Offset Pagination Matters for Large Datasets

The Offset Problem: Scan Cost Grows With Depth

When you ask a database for OFFSET 10000 LIMIT 25, the database still needs to process the skipped rows before returning the next 25. The further you paginate, the more rows the database reads and throws away. At hundreds of thousands of rows, late pages get measurably slower.

Shallow administrative queries may tolerate the cost. Feeds, exports, and automated clients that traverse the full collection need tests at greater offsets.

The Offset Problem: Drift on Live Data

Offset pagination is positional. If you request page 2 of a feed and someone inserts a post between your requests, every record shifts by one. You will either see a duplicate on page 2 or skip a record that fell between the crack. Decide whether that behavior is acceptable for the collection.

Cursor pagination is position-independent. The cursor points to a specific record, not to a position in an imaginary ordered list. Inserts and deletes between requests do not affect your current position.

Implementing Cursor Pagination Correctly

The cursor needs to encode enough information to reconstruct the query — typically the value of the sort column and the row ID. For a feed sorted by created_at descending:

{ "created_at": "2025-11-15T09:00:00Z", "id": 4291 }

Encode this as base64 to make it opaque. Opaque cursors let you change the internal representation without breaking clients.

The query:

SELECT * FROM posts
WHERE (created_at, id) < ('2025-11-15T09:00:00Z', 4291)
ORDER BY created_at DESC, id DESC
LIMIT 26

Fetch one extra row (LIMIT 26 for a page size of 25). If you get 26 results, there is a next page. Return 25 to the client and encode row 25 as the next cursor. If you get 25 or fewer, you are on the last page.

The response shape:

{
  "data": [...],
  "pagination": {
    "next_cursor": "eyJjcmVhdGVkX2F0IjoiMjAyNS0xMS0xNVQwOTowMDowMFoiLCJpZCI6NDI5MX0=",
    "has_more": true
  }
}

When Cursor Pagination Is Wrong for Your Use Case

Cursor pagination is not universally better. It has its own constraints:

• No random page access. You cannot jump to page 47. You can only go forward (and backward if you implement a before cursor). Admin interfaces where users want "jump to page X" need offset pagination.
• Harder to implement bidirectional navigation. Forward-only cursor pagination is straightforward. Supporting before and after cursors for bi-directional scrolling doubles the query complexity.
• Sorting flexibility is limited. The cursor must encode the sort key. If clients can sort by arbitrary columns, each sort configuration needs its own cursor encoding strategy.
• May add unnecessary work for small, static datasets. Offset pagination may be sufficient when queries are fast and page-number navigation is useful.

Keyset Pagination: The Third Option

Keyset pagination is cursor pagination without the encoding layer. Instead of an opaque token, the client passes the actual boundary values as query parameters:

GET /posts?last_id=4291&last_created_at=2025-11-15T09:00:00Z

This is transparent to the client and simpler to debug. The trade-off is that it exposes your internal sort keys and makes it harder to change the underlying query structure without a breaking change. For internal or developer-facing APIs where transparency is valuable, it is a reasonable choice.

Practical Recommendations

Start with offset pagination if your measured query performance is acceptable, the data changes slowly, and users expect page numbers. The simplicity is worth it.

Switch to cursor pagination when you are building a feed, a real-time data stream, or any endpoint that automated clients will walk in full. The performance benefit compounds with dataset size.

Set explicit limits. Whether offset or cursor, always enforce a maximum page size. Return an error if the client requests more than the maximum. An unbounded limit=999999 should never reach your database.

Document the pagination model in your OpenAPI spec. Clients should not have to guess whether next_cursor will be present when they are on the last page. Define the contract.

Discuss API pagination and query performance with LinkIT.