# Cursor vs Offset Pagination: A Deep Dive for API Designers > Understand when to use cursor-based vs offset pagination in your REST API — performance trade-offs, implementation details, and when each breaks down. - **Published:** 2025-12-07 - **Author:** Clixo - **Reading time:** 5 min read - **Tags:** api-design, pagination, backend, performance - **Canonical URL:** https://clixo.sh/blog/cursor-vs-offset-pagination-api-deep-dive Pagination feels like a solved problem until your dataset grows past a hundred thousand rows, your feed updates in real time, or a client tries to export everything by walking through every page. At that point, the choice between cursor-based and offset pagination goes from a cosmetic API detail to a performance and correctness problem. Here is what actually differs between the two approaches, when each breaks down, and how to implement them without the common mistakes. ## 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`. Simple to implement. Immediately intuitive. And broken in two specific ways at scale. **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`. Performance is constant regardless of position. ## 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`, it does not teleport to row 10,000. It scans and discards 10,000 rows, then returns 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. This is rarely a problem for admin dashboards where users never scroll past page 5. It is a real problem when you are building a feed, an export endpoint, or any collection where automated clients walk the full dataset. ### 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. For slowly-changing data this is acceptable. For a live feed, it is not. 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: ```json { "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: ```sql 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: ```json { "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. - **Not suitable for small, static datasets.** If your collection has fewer than a few thousand records and changes rarely, offset pagination is simpler and the performance difference is immaterial. ## 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 ```mermaid flowchart TD A[Need API pagination] --> B{"Dataset over 50k rows?"} B -- No --> C{"Live or frequently changing data?"} C -- No --> D[Use offset pagination] C -- Yes --> E[Use cursor pagination] B -- Yes --> E E --> F["Encode sort key as opaque cursor"] D --> G["Simple limit and offset params"] F --> H[Consistent performance at scale] G --> I["Slower at deep page offsets"] ``` **Start with offset pagination** if your dataset is small (under 50,000 records), changes slowly, and your 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. If you are building a backend where data scale and query performance matter from day one, [Clixo designs and ships production-ready API systems](https://clixo.sh/#contact). --- Clixo · 1141 W Bryn Mawr Ave, Itasca, IL 60143, US · [hello@clixo.sh](mailto:hello@clixo.sh) [Start a build](https://clixo.sh/#contact) · [All services](https://clixo.sh/services) · [Agent guide (llms.txt)](https://clixo.sh/llms.txt)