API Pagination Patterns: Offset, Cursor and Keyset
Offset, cursor and keyset pagination each trade simplicity for correctness. Learn the trade-offs and which to choose for a large dataset.
By Velloreth
Offset pagination
GET /api/posts?limit=20&offset=40
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 40;
The advantage: it is simple, and it allows a jump to any page.
The disadvantage: the database reads and discards every skipped row.
At an offset of 40, the database reads 60 rows and returns 20. At an offset of 100,000, it reads 100,020 rows and returns 20.
The cost grows with the depth, which is why page 1 is fast and page 1000 is slow.
The correctness problem
A row can appear twice or be skipped when the data changes between requests.
Page 1: rows 1-20
(a new row is inserted at the top)
Page 2: offset 20
→ the row that was at position 20 is now at position 21, so it is skipped
A deletion has the opposite effect, and a row appears on two pages.
This is not a rare edge case. A feed with a new post every few seconds shifts the offset on every request.
The fix is a cursor, which points at a specific row rather than a position.
Cursor pagination
GET /api/posts?limit=20&cursor=eyJpZCI6MTIzNH0
SELECT * FROM posts
WHERE (created_at, id) < ('2026-10-26 10:00:00', 1234)
ORDER BY created_at DESC, id DESC
LIMIT 20;
The cursor encodes the position of the last item, usually as a base64-encoded JSON object.
The advantage: a row inserted at the top does not shift the page, because the cursor points at a specific row.
The disadvantage: it cannot jump to an arbitrary page, and it cannot easily show a page number.
The response includes the next cursor:
{
"data": [...],
"next_cursor": "eyJpZCI6MTI1NH0",
"has_more": true
}
has_more is derived from fetching one extra row, which avoids a separate count query.
Keyset pagination
Keyset pagination is the same idea with an explicit indexed column.
SELECT * FROM posts
WHERE id < 1234
ORDER BY id DESC
LIMIT 20;
The column must be indexed, which makes the query a range scan rather than a full scan.
The performance is constant at any depth, because the index seeks directly to the position.
A composite key is needed for a non-unique sort column:
SELECT * FROM posts
WHERE (created_at, id) < ('2026-10-26 10:00:00', 1234)
ORDER BY created_at DESC, id DESC
LIMIT 20;
The id breaks the tie, so two rows with the same timestamp are ordered consistently.
Without the tie-breaker, a row can be skipped or repeated, which is the subtle bug that appears only when two rows share a timestamp.
The stable sort
A stable sort is required for any pagination.
-- Unstable: two rows with the same created_at have no defined order
ORDER BY created_at DESC
-- Stable: the id breaks the tie
ORDER BY created_at DESC, id DESC
Without a unique tie-breaker, the database may return the rows in a different order for the same query, which produces a duplicate or a missing row between pages.
This is the most common pagination bug, and it is intermittent, which makes it hard to diagnose.
Choosing a pattern
| Offset | Cursor / Keyset | |
|---|---|---|
| Simplicity | High | Medium |
| Jump to a page | Yes | No |
| Performance at depth | Poor | Good |
| Stable under a change | No | Yes |
| Total count | Easy | Expensive |
Use offset for a small dataset, an admin table, or a UI that needs a page number.
Use a cursor for a feed, an infinite scroll, or a large dataset.
A hybrid is common: a cursor for the main list, and an offset for a search result where a page number is expected.
The total count
A count query is expensive on a large table, because it must scan the index.
SELECT COUNT(*) FROM posts WHERE published = true;
The options:
- Omit the count, and return only
has_more. - Cache the count, and refresh it periodically.
- An approximate count, from the database’s statistics.
- A count with a cap, such as
LIMIT 1000, and report “1000+”.
For an infinite scroll, the count is not needed. For a page-number UI, it is, which is one reason the two patterns differ.
Pagination in a client
A client that consumes a paginated API has its own set of concerns, and the pattern it uses determines how much of them it must handle. An offset API lets a client jump to a page, which is what a table with a page number needs, and it forces the client to handle a duplicate or a missing row when the data changes underneath it. A cursor API removes that problem, and it forces the client to keep the cursor rather than a page number, which is a small change and a real improvement for a feed. The practical detail that catches people out is the retry: a client that retries a failed page request with an offset may skip or repeat a row, while a client that retries with a cursor gets the same page. A second detail is the empty page: a cursor API should return an empty list with has_more false rather than an error, so a client can stop cleanly.
Practical guidance
- Use a cursor for a large or changing dataset.
- Use offset for a small dataset or a page-number UI.
- Add a unique tie-breaker to the sort.
- Index the cursor column.
- Encode the cursor opaquely, so the client does not depend on its shape.
- Return
has_morerather than a count. - Fetch one extra row to determine
has_more. - Cap the limit, so a client cannot request a million rows.
- Document the cursor as opaque and subject to change.
For the related guides, see rest-api-design-principles and database-indexing-explained. For the broader context, api-versioning-strategies and api-error-responses-guide.
Quotable summary
Offset pagination is simple and allows a jump to any page, but it becomes slow and incorrect on a large or changing dataset, because the database reads and discards every skipped row and a row can appear twice or be skipped when the data changes. Cursor pagination is stable and efficient because the cursor points at a specific row rather than a position, and keyset pagination uses an indexed column as the cursor, which makes the query a range scan with constant performance at any depth. A stable sort is required, so a unique tie-breaker such as the id must be added to the order, or a row can appear twice or be missed between pages, which is the most common and most intermittent pagination bug. Use offset for a small dataset or a page-number UI and a cursor for a feed, an infinite scroll, or a large dataset.