Cursor-Based Pagination vs Offset Pagination: Which One Should Your API Use?

Every list endpoint eventually faces the same question: how do you split 2 million orders into pages a client can walk through? Pick offset pagination and you get simple SQL plus page numbers users understand. Pick cursor-based pagination and you get stable results plus consistent latency at any depth, but you give up “jump to page 47.”

Most teams pick offset because it’s the default in every tutorial. Then the orders table hits a few million rows, page 4,000 starts timing out, and users report seeing the same record twice while scrolling. This guide covers how both styles work, where offset breaks, why Stripe and Slack ship cursors, and how to test either style with chained requests in Apidog. By the end you’ll know exactly which one fits your endpoint.

If you want the broader picture first, our API pagination guide covers every strategy side by side. This article goes deep on the two that matter most.

How offset pagination works

Offset pagination maps directly to SQL. The client sends a page number and a page size; the server translates them into LIMIT and OFFSET.

SELECT id, customer_id, total_cents, created_at
FROM orders
ORDER BY created_at DESC
LIMIT 25 OFFSET 50;

That query returns page 3 of your orders list at 25 rows per page. The request looks like this:

GET /v1/orders?page=3&per_page=25

And a typical response:

{
  "data": [
    {
      "id": "ord_8821",
      "customer_id": "cus_1932",
      "total_cents": 4599,
      "created_at": "2026-08-30T14:22:07Z"
    }
  ],
  "page": 3,
  "per_page": 25,
  "total": 1848203,
  "total_pages": 73929
}

The appeal is obvious. Clients can jump to any page. The server can return a total count. Any developer can build it in an afternoon. For a small admin table, this is the right call, and our step-by-step guide to pagination in REST APIs walks through a full offset build.

But offset carries two structural problems, and neither one shows up in development. Both show up in production.

Problem 1: page drift

Offset counts rows from the top of the sorted result. It knows nothing about which rows the client already saw. So when rows are inserted or deleted between requests, the pages shift underneath the client.

Say a user loads page 1 of orders sorted newest-first, rows 1 through 25. While they read, 3 new orders arrive. They request page 2, which is OFFSET 25. Rows 23, 24, and 25 from the first response have now been pushed down into positions 26 through 28. The user sees them again. Duplicates.

Deletion flips it. Remove 3 rows from page 1 while the user reads it, and OFFSET 25 now skips 3 rows the user never saw. Silent data loss, and nobody gets an error.

For a monthly report nobody scrolls in real time, drift is harmless. For an activity feed, a sync endpoint, or anything a script walks page by page while writes continue, drift means duplicated or missing records. Consumers notice.

Problem 2: deep offsets scan everything they skip

OFFSET 500000 doesn’t teleport to row 500,001. The database walks the index through half a million entries, discards them, and then returns your 25 rows. Cost grows linearly with depth: O(n) where n is the offset.

Concrete numbers make this real. On a Postgres orders table with 2 million rows and an index on created_at:

• LIMIT 25 OFFSET 0 reads 25 index entries. A few milliseconds.
• LIMIT 25 OFFSET 100000 reads 100,025 entries and throws away 100,000. Tens of milliseconds.
• LIMIT 25 OFFSET 1500000 reads 1.5 million entries. Now you’re deep into hundreds of milliseconds, holding buffers and burning CPU for one page.

Markus Winand’s no-offset writeup on Use The Index, Luke demonstrates this cost with query plans and is worth reading in full. The pattern in production is a slow-query log dominated by high-offset requests, often from one crawler dutifully walking every page of your public API. One client, and your p99 doubles.

How cursor-based pagination works

Cursor-based pagination, also called keyset pagination, drops the row counter. Instead of “skip 50 rows,” the client says “give me rows after this specific record.” The cursor identifies the last row the client saw, so the server can seek directly to the next batch.

The SQL uses a row comparison on the sort key instead of OFFSET:

SELECT id, customer_id, total_cents, created_at
FROM orders
WHERE (created_at, id) < ('2026-08-30T14:22:07Z', 'ord_8821')
ORDER BY created_at DESC, id DESC
LIMIT 25;

Notice the two-column comparison. created_at alone isn’t unique; two orders can land in the same millisecond, and a non-unique sort key means rows get skipped or repeated at page boundaries. Adding id as a tiebreaker makes the ordering total and the pagination exact. With a composite index on (created_at, id), the database seeks straight to the boundary and reads 25 entries. Page 1 and page 60,000 cost the same.

The API shouldn’t expose those raw values, though. Real implementations encode the sort key into an opaque token, usually base64:

GET /v1/orders?limit=25&cursor=eyJjcmVhdGVkX2F0IjoiMjAyNi0wOC0zMFQxNDoyMjowN1oiLCJpZCI6Im9yZF84ODIxIn0

Opacity is a design decision, not obfuscation for its own sake. Clients who can’t parse the cursor can’t build URLs by hand, which leaves you free to change the sort key, add a shard hint, or switch storage engines without breaking anyone. The contract becomes “pass back what we gave you,” nothing more.

The trade: there’s no page 47. A cursor only knows “after this row,” so clients walk forward (and backward, if you issue a previous-cursor) one page at a time. Total counts don’t come along for free either; counting is a separate query. For designs where the dataset itself is huge, our guide on designing API pagination for millions of records covers the scaling side in more depth.

Trade-offs at a glance

One subtlety in that table deserves emphasis: cursor pagination demands a deterministic sort. If your endpoint lets clients sort by a mutable, non-unique column like status, keyset logic gets painful fast. Offset tolerates sloppy ordering; cursors punish it.

Which one should you pick?

Match the style to how the data gets consumed.

Admin tables and dashboards: offset. Internal tools with a few thousand rows, humans clicking page numbers, and a visible “1,848 results” count. Drift doesn’t matter, depth stays shallow, and jump-to-page is a real feature. Offset wins on build cost.

Infinite scroll feeds: cursor. Nobody jumps to page 47 of a feed. Users only ever load “more,” writes happen constantly, and duplicates are visible and embarrassing. This is the textbook cursor case.

Public APIs: cursor. You don’t control your consumers. Someone will write a loop walking every page, and with offset, deep pages become your problem at 3 a.m. Cursors keep every page cheap and let you evolve internals behind the opaque token. Our REST API pagination guide covers the URL and header conventions in detail.

Exports and sync jobs: cursor. A batch job pulling all 2 million orders needs two guarantees: no missed rows despite concurrent writes, and flat cost per page. Offset provides neither. A cursor also gives you a free resume point when the job dies at row 1.4 million.

The honest rule of thumb: offset for small, human-browsed, count-heavy interfaces; cursors for anything large, live, or public.

How real APIs handle it

Stripe is fully cursor-based. Every list endpoint accepts starting_after (an object ID) and limit, and responses include has_more. To fetch the next page of charges, you pass the ID of the last charge you received. The Stripe pagination docs show the pattern; note there’s no total count anywhere, a deliberate omission at their write volume.

GitHub’s REST API still exposes page and per_page on most endpoints, with Link headers pointing to next and last pages. But read the GitHub pagination docs closely: they instruct clients to follow the Link header verbatim instead of constructing page URLs, and newer endpoints have shifted to cursors, exactly because deep offset walks over massive repos hurt.

Slack migrated its Web API to cursor pagination and now marks it as the approach all new methods use. Methods like conversations.history return response_metadata.next_cursor, and an empty cursor string means you’ve reached the end, as described in the Slack pagination docs.

Three high-traffic APIs, and the direction of travel is one way: toward cursors.

Designing the response envelope

A cursor API lives or dies on its envelope. Keep it boring and predictable:

{
  "data": [
    {
      "id": "ord_8846",
      "customer_id": "cus_2201",
      "total_cents": 12900,
      "created_at": "2026-08-30T16:01:44Z"
    }
  ],
  "has_more": true,
  "next_cursor": "eyJjcmVhdGVkX2F0IjoiMjAyNi0wOC0zMFQxNjowMTo0NFoiLCJpZCI6Im9yZF84ODQ2In0"
}

Four rules make it solid:

• Always return has_more. Clients shouldn’t infer the end from a short page; a page can be short mid-stream if you filter after fetching.
• Return next_cursor: null on the final page, and document it. Slack’s empty-string convention works too; pick one and never mix them.
• Reject invalid cursors with a 400, not an empty 200. A garbled cursor is a client bug, and hiding it costs someone a day of debugging.
• Sign or version the cursor payload if it encodes anything beyond sort keys. You’ll thank yourself during the next schema migration.

Testing both styles in Apidog

Pagination bugs hide at boundaries: the last page, the empty page, the cursor whose anchor row got deleted. Manual clicking won’t catch them, but a chained test scenario will, and this is where Apidog earns its place in the workflow.

For cursor endpoints, build a test scenario with two steps:

1. Call the endpoint and extract the cursor. Add a post-processor to the first request with the JSONPath $.next_cursor, and store it in a variable like nextCursor. Apidog lets you copy the JSONPath straight from the response panel; the full walkthrough is in how to set assertions and extract variables with JSONPath.
2. Loop the next-page request. Wrap a second request in a ForEach or loop step, pass {{nextCursor}} as the cursor parameter, re-extract $.next_cursor each iteration, and exit when has_more is false. Assert on every pass that no id repeats from the previous page and page size never exceeds limit.

For offset endpoints, the same structure applies with a counter variable: increment page, assert data length equals per_page until the final page, and assert total stays consistent across the walk.

Then add the edge cases as their own steps, each with explicit assertions:

• Empty page: request a filter matching zero rows; assert data is [], has_more is false, and status is 200.
• Invalid cursor: send cursor=not-a-real-cursor; assert status 400 and a machine-readable error code.
• Deleted anchor row: create an order, grab a cursor anchored to it, delete the order, then use the cursor; assert the walk continues from the correct position instead of erroring. Keyset comparisons handle this naturally, and the test proves it.

Once the scenario passes locally, run it in CI on every merge. Download Apidog for free and you can have the full cursor-walk scenario, loops and assertions included, running in under half an hour.

FAQ

Is cursor pagination always better?

No. Offset is the better fit when users need page numbers, totals, and random access over a modest dataset, which describes most internal admin tools. Cursors are better when the dataset is large, writes are frequent, or the API is public. The failure mode is defaulting to offset for a public list endpoint and discovering the O(n) cost after launch.

How do I get a total count with cursor pagination?

Run a separate SELECT COUNT(*) with the same filters, either as a distinct endpoint or an opt-in query parameter like include_count=true. Cache it aggressively; an approximate count refreshed every minute satisfies nearly every UI. Stripe skips totals entirely, which tells you how often clients truly need them.

Can I offer both pagination styles on one endpoint?

You can, and GitHub effectively does during its transition, but avoid it on new APIs. Two styles mean two sets of edge cases, two test matrices, and client confusion about which to use. Pick one per endpoint. If you’re designing the contract from scratch, the patterns in our REST API pagination guide will keep the parameter naming consistent across your surface.

What happens if the cursor’s anchor row is deleted?

With keyset pagination, nothing breaks. The WHERE (created_at, id) < (?, ?) comparison doesn’t require the anchor row to exist; it seeks to the boundary position and continues. This is a real advantage over “cursor as row lookup” designs, and it’s exactly the edge case worth asserting in your Apidog test scenario before a consumer finds it for you.