API Pagination: Offset vs Cursor vs Keyset, Tested on 1 Million Rows
API pagination splits large result sets into pages so clients and servers are not overwhelmed. The two main designs, offset and cursor (keyset), behave very differently as data grows and changes. We timed both on a million rows, showed the duplicate item offset pagination produced when a new record arrived, a skipped row caused by ties, and built an opaque cursor.
- API pagination with offsets is simple, but page 49,999 took 7.67 ms against 0.01 ms with keyset pagination.
- When a new post arrived between two requests, offset paging returned the same item on page 1 and page 2; keyset paging did not.
- Keyset paging on a non-unique column (a date) silently skipped a row until we added the ID as a tiebreaker.
- Wrapping the last position in an opaque, base64-encoded cursor hides the implementation and lets it change later.
No API should return a million records in one response: it would be slow to build, slow to send and likely to crash the client. Pagination returns a page at a time, plus a way to get the next one. The choice of API pagination method looks like a small design detail, but it affects performance at scale, correctness when data changes and how easy the API is to use.
We used SQLite 3.50.4 from Python 3.13.6 for every query, with a 1,000,000-row table for timing and small tables to demonstrate correctness issues. The same SQL patterns apply to PostgreSQL and MySQL, where the gap is usually larger because data lives on disk.
Why paginate?
- Performance: smaller queries, smaller responses, less memory on both sides.
- Stability: one heavy request cannot overload the server, especially combined with rate limiting.
- User experience: feeds and tables show results immediately and load more on demand.
Offset pagination
GET /items?limit=20&offset=40
SELECT id FROM items ORDER BY id LIMIT 20 OFFSET 40;
Offset pagination (or page-number pagination, ?page=3&per_page=20) skips a number of rows and returns the next batch. It is easy to implement, supports jumping to any page, and maps naturally onto numbered pages in a user interface. Query string handling is covered in our URL query parameters guide.
Keyset (cursor) pagination
GET /items?limit=20&after=999980
SELECT id FROM items WHERE id > 999980 ORDER BY id LIMIT 20;
Keyset pagination, also called seek pagination and the basis of most cursor pagination, remembers where the last page ended and asks for rows after that point. It cannot jump to page 500 directly, but each page is found with an index lookup instead of counting through all the earlier rows. Our SQL pagination guide covers the SQL side in more depth.
Speed on 1,000,000 rows
| Page (20 rows each) | OFFSET | Keyset | Same rows? |
|---|---|---|---|
| 1 (offset 0) | 0.02 ms | 0.01 ms | Yes |
| 1,000 (offset 19,980) | 0.52 ms | 0.01 ms | Yes |
| 25,000 (offset 499,980) | 3.94 ms | 0.01 ms | Yes |
| 49,999 (offset 999,960) | 7.67 ms | 0.01 ms | Yes |
Both methods returned identical rows, but offset time grew in a straight line with the page number while keyset stayed flat. Our in-memory table makes every number small; on a disk-based database with wider rows, deep offset pages commonly take seconds, and crawlers or export jobs that walk every page make the total cost quadratic.
Why: the query plans
... ORDER BY id LIMIT 20 OFFSET 999980 -- SCAN items
... WHERE id > 999980 ORDER BY id LIMIT 20 -- SEARCH items USING INTEGER PRIMARY KEY (rowid>?)
OFFSET cannot jump: the database must produce and throw away all 999,980 earlier rows before returning 20. The keyset condition is an index search that starts directly at the right place. Checking plans like this is explained in our SQL EXPLAIN plan guide; the sort column must be indexed for keyset pagination to be fast, as described in SQL indexes explained.
When data changes between requests
-- newest-first feed of 10 posts, 3 per page
page 1: [10, 9, 8]
-- a new post (11) is published before the next request
page 2 with OFFSET 3: [8, 7, 6] post 8 appears twice
page 2 with keyset: [7, 6, 5] WHERE id < 8
Offsets count positions, and positions shift when rows are inserted or deleted. The new post pushed everything down by one, so offset 3 started at post 8, which the user had already seen. Deletions cause the opposite problem: items are skipped. Keyset pagination anchors on the last item’s value, not its position, so new items at the top did not affect the next page. For live feeds, timelines and syncing data, this alone is reason to use keyset or cursor pagination.
The ties trap: always add a unique tiebreaker
-- 10 events: ids 1-5 on 2026-11-22, ids 6-10 on 2026-11-23; page size 4
page 1: [1, 2, 3, 4]
next page WHERE created > '2026-11-22': [6, 7, 8, 9] id 5 skipped
next page WHERE (created, id) > ('2026-11-22', 4): [5, 6, 7, 8] correct
Keyset pagination on a column that is not unique breaks when several rows share the boundary value. Five events had the same date; the first page ended in the middle of them, and asking for “created after that date” skipped the remaining one. The fix is to sort and filter on the column plus a unique key, here (created, id), using a row-value comparison (supported by PostgreSQL, MySQL 8 and SQLite) or the equivalent created > ? OR (created = ? AND id > ?). Index both columns together.
Opaque cursors
def enc(row):
return base64.urlsafe_b64encode(json.dumps({"c": row[1], "i": row[0]}).encode()).decode().rstrip("=")
next_cursor = enc(last_row) # 'eyJjIjogIjIwMjYtMTEtMjIiLCAiaSI6IDR9'
dec(next_cursor) # ('2026-11-22', 4)
Instead of exposing after_id and after_created as separate parameters, many APIs return a single opaque cursor string that encodes the position. Clients treat it as a token and send it back unchanged. That lets the server change sort order or encoding later without breaking clients, and stops clients from fabricating positions. Base64 is encoding, not protection: anyone can decode our cursor, so validate it on the server, and sign it with an HMAC if tampering matters.
Response format
{
"data": [ { "id": 5 }, { "id": 6 } ],
"next_cursor": "eyJjIjogIjIwMjYtMTEtMjIiLCAiaSI6IDR9",
"has_more": true
}
Return the items, a cursor or link for the next page, and a flag or null cursor when there are no more pages. Many APIs also put links in a Link header. Keep the page-size parameter, cap it (for example at 100) and document the default. Our REST API best practices cover consistent naming, and clients can loop over pages with the pattern from our fetch API guide.
Tip: fetch one more row than the page size. If you get it, there is a next page; return only the requested number of rows and you never need a separate count query to set has_more.
Total counts
SELECT count(*) FROM items; -- 0.8 ms on 1,000,000 rows in SQLite
Clients often want “page 3 of 120”. SQLite counted our million rows in under a millisecond, but on large tables in other databases, especially with filters, an exact count can cost as much as the query itself, on every request. Options: make the count optional (?include_total=true), return an estimate, cache it, or drop it and show “load more”. Infinite scroll and cursor pagination usually do not need totals at all.
Offset or cursor: which to choose?
| Offset / page number | Keyset / cursor | |
|---|---|---|
| Deep pages | Slower and slower | Constant speed |
| Data changes during paging | Duplicates and gaps | Stable |
| Jump to page N | Yes | No (next and previous only) |
| Total pages | Natural | Separate query if needed |
| Implementation | Simplest | Needs a unique sort key |
| Best for | Small, stable data; admin tables | Feeds, large or changing data, public APIs, exports |
API pagination design checklist
- Always sort by a deterministic order that ends in a unique column.
- Index the sort columns together.
- Cap the page size and document the default.
- Use cursors for public, large or frequently changing collections.
- Return a clear end-of-results signal.
- Keep filters in the cursor or require them on every request, so a cursor cannot be reused with different filters.
- Consider HTTP caching for first pages, which are requested most often.
Final thoughts
API pagination is easy to start and hard to change later, so choose deliberately. Offset pagination is fine for small, stable data and numbered pages, but in our tests it slowed down on deep pages and showed a duplicate the moment a new item arrived. Keyset pagination, wrapped in an opaque cursor and backed by a unique, indexed sort key, stayed at 0.01 ms on any page and kept results stable. For most public APIs and feeds, cursors are the right default.
Frequently asked questions
What is API pagination?
A way to return a large list of results in smaller pages, with a mechanism such as an offset, page number or cursor that lets the client request the next page.
What is the difference between offset and cursor pagination?
Offset pagination skips a number of rows, which gets slower on deep pages and shifts when data changes. Cursor pagination continues after the last item seen, which stays fast and stable.
Why does offset pagination show duplicate items?
Offsets count positions. If a new row is inserted before the current page between two requests, every row shifts down by one and the next page repeats the last item of the previous one.
What is keyset pagination?
Pagination that filters on the sort key of the last row returned, such as id greater than the last id, so the database can use an index to start exactly where the previous page ended.
Should an API return the total count?
Only if clients need it. Exact counts can be expensive on large filtered tables, so consider making them optional, estimated or cached, or use a has more flag instead.
Keep learning
Need help with testing, WordPress or SEO?
We help teams ship better software and grow their sites. Tell us what you are working on and we will suggest a clear next step.