API Pagination With Cursors vs Offsets and When to Use Each
Your list endpoint was fast when the table had 2,000 rows. Now it has 8 million, page 4,000 takes three seconds to load, and users report seeing the same item twice while scrolling.

Your list endpoint was fast when the table had 2,000 rows. Now it has 8 million, page 4,000 takes three seconds to load, and users report seeing the same item twice while scrolling. Both problems usually trace back to how you built API pagination, and the fix starts with choosing between offsets and cursors deliberately rather than by habit.
I have migrated several production APIs from one style to the other. Neither is always right, but the trade offs are concrete and easy to reason about once you see what the database is actually doing.
How offset API pagination works
Offset pagination is the version most of us learned first. The client sends a starting position, usually alongside a limit parameter that sets the page size:
GET /orders?offset=200
The server translates that into SQL like SELECT ... ORDER BY created_at DESC LIMIT 50 OFFSET 200. It is simple to build, simple to explain and supports jumping straight to page 17, which is handy for admin tables with numbered page links.
The cost hides in the OFFSET. A database cannot skip rows for free. To return rows 200,001 to 200,050, it typically has to walk through the first 200,000 entries in index order and throw them away. On a well indexed PostgreSQL table I measured recently, offset 0 returned in about 2 ms, offset 100,000 in about 60 ms and offset 2,000,000 in over 1.2 seconds. Same query, same page size, very different work.
The duplicate and missing row problem
Offsets also break when data changes between requests. Suppose a user loads page one of newest orders, and five new orders arrive before they request page two. Every row shifts down by five, so the first five rows of page two are the last five rows they already saw. Deletes do the opposite and silently skip items. For a feed or an infinite scroll, that is a visible bug.
How cursor pagination works
Cursor pagination, sometimes called keyset pagination, replaces "skip N rows" with "give me rows after this one." The server returns an opaque token pointing at the last item on the page:
GET /orders?after=eyJjIjoiMjAyNi0xMS0wNCIsImlkIjo4ODEyfQ
Decoded, that cursor holds the sort values of the last row, for example the created_at timestamp and the id. The query then filters to rows whose (created_at, id) pair sorts before the pair stored in the cursor, ordered by created_at DESC, id DESC, with LIMIT 50. With a composite index on (created_at, id), the database jumps straight to the right spot in the index. Page 1 and page 40,000 cost the same.
Inserts and deletes no longer shift pages either, because each request anchors on a specific row rather than a position count.
Choosing between them
| Need | Offset | Cursor |
|---|---|---|
| Jump to page N | Yes | No, only next and previous |
| Stable results while data changes | No | Yes |
| Deep pages on large tables | Slow | Constant time with the right index |
| Total page count in UI | Easy, though COUNT can be slow | Awkward |
| Arbitrary user chosen sort | Easy | Needs an index per sort |
| Implementation effort | Low | Moderate |
My rule of thumb: use cursors for feeds, timelines, activity logs, sync endpoints and any public API where clients page through everything. Use offsets for small, mostly static datasets and internal admin screens where people want numbered pages and rarely go past page 20.
Why "always use cursors" is overstated
A lot of API advice now says offsets are an anti pattern. I disagree with that as a blanket rule.
For a table under roughly 50,000 rows, offset queries stay in the low milliseconds even at the last page. Cursor pagination adds real complexity: you need a unique, stable sort key, composite indexes for every sort order you expose, careful encoding of the cursor and clear rules for what happens when the client changes filters mid scroll. If your product team wants a "sort by any column" table with a page selector, forcing cursors there creates more bugs than it prevents.
The better question is not which style is modern but how large the dataset will get and how fast it changes. A settings list for 300 user roles does not need keyset pagination.
Implementation details that save you later
Whichever style you pick, a few habits prevent painful migrations:
- Always sort on a unique tiebreaker. Sorting only by created_at gives unstable ordering when two rows share a timestamp. Add the primary key as a second sort column.
- Make cursors opaque. Base64 encode them so clients cannot build their own. That lets you change the internals later without breaking anyone.
- Cap the page size. Accept a limit but enforce a maximum, such as 100. Without it, one client asking for 50,000 rows can exhaust your connection pool. Our guide to database connection pooling explains why that hurts everyone else.
- Return a next link, not just a token. Include a ready made URL for the next page in the response. Clients make fewer mistakes.
- Avoid exact totals on big tables. COUNT(*) on millions of rows can cost more than the page itself. Return "has_more" or an estimated count instead.
- Watch out for N+1 queries per page. Fetching related records row by row on a 100 item page multiplies latency. See how N+1 queries slow an API for ways to batch them.
Migrating an existing offset API
If clients already depend on offsets, you do not have to break them. Add cursor support alongside the old parameters, return a next cursor in every response, and log which clients still send offset values. In one migration I worked on, about 80 percent of traffic moved to cursors within two months simply because the official SDK switched first. After that, we capped the maximum offset at 10,000 and returned a clear error message pointing to the cursor parameter, which handled the stragglers without a hard cutoff.
Document the behaviour clearly, especially what happens when the cursor points at a deleted row. Good pagination is part of designing an API that feels predictable, which we cover more broadly in designing a REST API other developers enjoy.
Frequently asked questions
What is the difference between offset and cursor pagination?
Offset pagination asks the database to skip a number of rows and return the next batch. Cursor pagination asks for rows that come after a specific row, identified by its sort values. Cursors stay fast on deep pages and do not repeat or skip items when data changes.
Why is offset pagination slow on large tables?
The database usually has to read and discard every row before the offset, even with an index. Deep pages therefore get slower as the offset grows. Cursor queries use the index to jump directly to the starting point.
Can you jump to a specific page with cursor pagination?
Not directly, since cursors only know the next and previous positions. Some APIs combine both approaches, offering cursors for scrolling and offsets for shallow numbered pages. If users truly need page numbers, offsets are usually the simpler fit.
More in Back-End
Back-End
How to Run Database Migrations Without Locking Tables
The migration itself took 41 milliseconds. The outage lasted nine minutes.
Back-End
How Graceful Shutdown Stops Deploys From Dropping Requests
Every deploy at a job I had before this one left a small notch in the error graph.
Back-End
How to Deliver Webhooks Reliably With Retries and Signing
A partner emails to say they never received the order.created event from last Tuesday.