Types of Pagination: Offset, Cursor, and Keyset Explained
The main pagination approaches are offset-based pagination, which skips a number of records, and cursor-based pagination, which continues from a position returned by the server. Keyset pagination uses the last item's sort values to find the next batch and is a common way to implement a cursor API.
Numbered pages, Load more, and infinite scroll describe the interface. They are separate choices from how the server retrieves records: an infinite feed can use offsets, and a cursor API can power Previous and Next buttons.
Pagination types compared#
| Approach | How the next batch is selected | Best fit | Main limitation |
|---|---|---|---|
| Offset / page-based | Skip N records, return the next batch | Tables with numbered pages and direct page jumps | Deep offsets can be expensive; changing data can shift pages |
| Cursor-based API | Send the server's continuation token | Sequential browsing and feeds | Usually cannot jump directly to an arbitrary page; performance depends on implementation |
| Keyset / seek | Filter after the last ordered values | Large, indexed datasets with a stable sort order | Needs a unique ordering and careful handling of changed sort values |
Cursor and keyset are not mutually exclusive. A cursor is an API contract; keyset describes the query strategy underneath it. An opaque token could instead hide an offset or reference server-side state.
Offset-based pagination (page-based pagination)#
With a page size of 10, page 3 skips the first 20 records. These requests can express the same operation:
GET /api/users?page=3&per_page=10
GET /api/users?offset=20&limit=10
For page numbers starting at 1, calculate offset = (page - 1) * pageSize.
SELECT id, name
FROM users
ORDER BY id ASC
LIMIT 10 OFFSET 20;
Use a unique ordering so tied values cannot shuffle between requests. For example, when sorting by creation time, include id as a tie-breaker: ORDER BY created_at DESC, id DESC. PostgreSQL documents both the need for predictable ordering and the cost of computing rows skipped by a large offset. PostgreSQL: LIMIT and OFFSET
Choose offsets when users need page numbers or direct jumps. An inventory table with filters and a few hundred results is a reasonable starting point. If the interface displays “page 3 of 42,” the API also needs a total count or estimate; pagination alone does not provide one.
What happens when records change?#
Suppose page 1 contains IDs [10, 9, 8] in descending order. Before the next request, ID 11 is inserted. The new list starts [11, 10, 9, 8, 7, 6]. Skipping three records now returns [8, 7, 6]: the user sees ID 8 twice. A deletion before the boundary can instead cause a record to be skipped.
A unique ordering resolves ties, but does not freeze the dataset across requests.
Cursor-based pagination#
The client asks for the first batch without a cursor:
GET /api/users?limit=2
An illustrative response for users ordered by descending ID:
{
"items": [
{ "id": 10, "name": "Alice" },
{ "id": 9, "name": "Bob" }
],
"nextCursor": "eyJsYXN0SWQiOjl9",
"hasNext": true
}
Then the client sends the returned token unchanged:
GET /api/users?limit=2&after=eyJsYXN0SWQiOjl9
In this example the token encodes {"lastId":9}. It is readable for demonstration; clients should treat it as opaque. Base64 is neither encryption nor protection against modification. Servers must validate cursors and enforce authorization on every request, and can sign tokens when integrity is needed.
The response fields above are an example API design. Other conventions exist: GraphQL's connection specification exposes cursors with edges and pagination information through pageInfo. GraphQL Cursor Connections Specification
Reset the cursor when filters or sorting change. A position from “newest users” is not a valid continuation of “users sorted by name.” The server should reject incompatible tokens rather than silently return misleading results.
Keyset pagination (seek pagination)#
For the descending-ID API above, the next query can seek beyond the last returned ID:
SELECT id, name
FROM users
WHERE id < 9
ORDER BY id DESC
LIMIT 3;
For a requested page size of 2, fetch up to 3 rows. Return only the first 2; the extra row tells you whether hasNext is true. Generate the next cursor from the last returned row, not the extra row. At the end, return hasNext: false and nextCursor: null.
Unlike offsets, inserting ID 11 does not move this query's boundary: it still requests IDs below 9.
Sorting by a timestamp#
For a chronological feed, store both the timestamp and unique ID in the cursor. Assume created_at is non-null and id is a non-null unique key. In PostgreSQL, the next-page query can be:
SELECT id, name, created_at
FROM users
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT $3;
Bind $1 and $2 to the cursor's timestamp and ID, and $3 to pageSize + 1. Row comparison checks the timestamp first, then the ID when timestamps match. Preserve the database timestamp's precision when encoding it. PostgreSQL: Row Constructor Comparison
A matching index is a starting point for this query:
CREATE INDEX users_created_at_id_idx
ON users (created_at DESC, id DESC);
An index matching the ordering can avoid a separate sort. Actual performance also depends on filters, data distribution, and the query plan; adding a cursor parameter alone does not make an API fast. PostgreSQL: Indexes and ORDER BY
Keyset limitations#
- Changing sort values: an item moved across the boundary can be missed or repeated. Prefer stable keys when possible.
- No automatic snapshot: new records and edits may still affect later requests. For consistent exports, choose an explicit snapshot or versioning strategy; see database isolation levels.
- No direct page 50: reaching an arbitrary page generally requires traversing cursors or maintaining additional checkpoints.
- Backward navigation: a client can retain earlier cursors. A server implementing reverse seek must invert the comparison and query order, then restore the presentation order.
Infinite scroll, Load more, and numbered pages#
Choose the interaction according to the task:
- Numbered pages: useful for navigation to a known position. Offset pagination usually makes this simplest.
- Load more: appends a batch only when requested. It works with either offsets or cursors and lets users control the pace.
- Infinite scroll: automatically requests another batch near the end of the list. Cursor-backed keyset queries can suit a growing feed, but the interface still needs loading, retry, and end-of-list states.
For append-based interfaces, prevent overlapping requests, discard stale results after filter changes, and preserve scroll position when users return from an item. Keep loaded items visible when a request fails and provide an accessible way to retry. Use item IDs to suppress accidental duplicates, while remembering that client-side deduplication cannot recover skipped records.
Client-side vs server-side pagination#
Client-side pagination downloads the complete dataset and slices it in the browser. It can be convenient for a small, bounded list, but dividing that list into pages does not reduce the initial transfer.
Server-side pagination retrieves only a batch per request using one of the approaches above. For a database-backed endpoint, apply the limit in the query rather than fetching every record and slicing afterward. The Node.js and SQLite example is a starting point for building a list endpoint; its unbounded query is a natural place to add pagination.
Which pagination approach should you choose?#
Start with the navigation requirement:
- Direct page jumps and modest result sets: use offset/page-based pagination, deterministic sorting, and a sensible page-size limit.
- Sequential browsing of a large or frequently changing dataset: consider a cursor API backed by keyset queries and suitable indexes.
- A small list already needed in full: client-side slicing may be enough.
- An exact export while data changes: define consistency requirements first; a cursor alone cannot guarantee an unchanged dataset.
Keep pagination details behind a clear data-access interface. A repository layer can return items and continuation information without making callers construct database queries. Whichever method you choose, test tied sort values, empty results, insertions, deletions, and the final page before shipping it.