Prisma Pagination: Offset vs Cursor vs Keyset
Use offset when users need numbered pages, a Prisma cursor for a simple unique sort key, and a composite keyset cursor when a feed is ordered by a non-unique value such as createdAt. Keyset is a cursor implementation: it resumes from the last sort tuple instead of a page number. The right choice depends on ordering, indexes, and whether users need arbitrary page jumps.
The Conventional Wisdom
Offset (Prisma skip: 990, SQL OFFSET 990 LIMIT 10): the database must work past the skipped rows. Deep pages usually cost more than shallow pages.
Keyset (WHERE id > $1 ORDER BY id LIMIT 10): a matching index can seek from the boundary without scanning every earlier page. Filters, sort order, and the query plan still affect performance.
Prisma's cursor API and the package's cursorStrategy: 'keyset' expose different ways to build cursor queries. A slow query from one strategy does not establish that every cursor implementation is slow.
What We Measured
The published benchmark reports these results for @nestarc/pagination 0.3.0, Prisma 7.9.1, PostgreSQL 16, and 10,000 rows on Apple Silicon with local Docker. These are the existing measurements, not a new keyset benchmark:
| Scenario | Avg |
|---|---|
| Offset — page 1 | 1.04ms |
| Offset — page 100 | 2.61ms |
| Cursor — first page (sort by id) | 0.56ms |
| Cursor — deep page (sort by id) | 0.58ms |
| Cursor — deep page (sort by createdAt) | 11.05ms |
The offset cases sort by createdAt and include an exact count; the ID cursor cases sort by id and omit the count. The roughly 78% difference between 2.61ms and 0.58ms includes those changes in work. It does not isolate the cost of offset versus cursor pagination, measure library overhead against direct Prisma, or measure the keyset strategy. Within the same offset setup, page 100 took about 2.5 times as long as page 1.
The Prisma Cursor Caveat
The measured createdAt cursor case took 11.05ms compared with 0.58ms for the ID cursor. That is evidence about this workload, not a guarantee about SQL generated by every Prisma version. The summary does not include a captured SQL log or EXPLAIN plan, so it cannot establish that Prisma always omits LIMIT for a non-PK sort.
Before applying the result to your endpoint, follow the query logging and plan inspection steps. Keep the Prisma version, schema, indexes, filters, and cursor boundary with the results. See Prisma 7 pagination for the ORM API and its trade-offs.
When to Use Which
| Scenario | Best choice | Why |
|---|---|---|
| UI with page numbers | Offset | Users expect "Page 1, 2, 3..." |
| Infinite scroll ordered by a unique ID | Prisma cursor | A simple unique ordering and no page jumps |
| Large feeds with a matching index | Keyset cursor | Seek from the previous boundary; verify the plan at depth |
| Admin dashboards | Offset | Need "jump to page 50" |
| Non-unique sort (createdAt, name) | Keyset cursor + unique tie-breaker | Preserve a deterministic order when values tie |
| Mobile apps (Load More) | Cursor | Choose the strategy that matches the endpoint's ordering |
Three Queries Against the Same Prisma Model
These examples use the Prisma 7 Client API and a PostgreSQL User model. Add the model to your application's schema and generate its client using the Prisma 7 setup:
model User {
id Int @id @default(autoincrement())
name String
email String @unique
role String @default("member")
createdAt DateTime @default(now()) @db.Timestamp(3)
@@index([createdAt(sort: Desc), id(sort: Desc)])
}The millisecond timestamp precision matches JavaScript Date; preserve that precision when serializing a boundary. All three queries keep the same role filter and request one extra row to determine whether another page exists. The offset and keyset examples use newest-first ordering; the Prisma cursor example deliberately uses a unique ID order.
import type { PrismaClient } from './generated/prisma/client';
const pageSize = 20;
function pageWindow<T>(rows: T[]) {
return {
data: rows.slice(0, pageSize),
hasNextPage: rows.length > pageSize,
};
}Offset: Jump to a Numbered Page
async function offsetPage(prisma: PrismaClient, page: number) {
if (!Number.isSafeInteger(page) || page < 1 || page > 100_000) {
throw new RangeError('page must be an integer from 1 to 100000');
}
const rows = await prisma.user.findMany({
where: { role: 'member' },
orderBy: [{ createdAt: 'desc' }, { id: 'desc' }],
skip: (page - 1) * pageSize,
take: pageSize + 1,
});
return pageWindow(rows);
}The id tie-breaker makes equal timestamps deterministic. The page cap is an example application limit, not a Prisma limit. A total count is a separate query; omit it when the UI only needs a next-page button. Inserts or deletions before the offset can shift rows between requests.
Prisma Cursor: Continue After a Unique ID
async function cursorPage(prisma: PrismaClient, afterId?: number) {
const rows = await prisma.user.findMany({
where: { role: 'member' },
orderBy: { id: 'asc' },
...(afterId === undefined ? {} : { cursor: { id: afterId }, skip: 1 }),
take: pageSize + 1,
});
const result = pageWindow(rows);
const last = result.data.at(-1);
return { ...result, nextCursor: result.hasNextPage && last ? last.id : null };
}Call without afterId for page one, then pass nextCursor. skip: 1 excludes the boundary row. The cursor comes from the last returned row, not the extra lookahead row. This ID order is not a guarantee of creation-time order. Prisma's record cursor depends on locating its boundary record; if that record is deleted, it may return an empty page. Use a stored value boundary when traversal must survive deletion of that row.
Keyset Pagination with createdAt and id
For newest-first order, store both sort values and express the boundary directly in where. This query does not need to look up the previous row:
type Boundary = { createdAt: Date; id: number };
async function keysetPage(prisma: PrismaClient, after?: Boundary) {
const rows = await prisma.user.findMany({
where: {
role: 'member',
...(after ? { OR: [
{ createdAt: { lt: after.createdAt } },
{ createdAt: after.createdAt, id: { lt: after.id } },
] } : {}),
},
orderBy: [{ createdAt: 'desc' }, { id: 'desc' }],
take: pageSize + 1,
});
const result = pageWindow(rows);
const last = result.data.at(-1);
return {
...result,
nextCursor: result.hasNextPage && last
? { createdAt: last.createdAt, id: last.id }
: null,
};
}If the last returned tuple is (2026-10-29T10:00:00.000Z, 42), rows with that timestamp and IDs below 42 remain eligible. Filtering only on an earlier timestamp would skip them. For an HTTP cursor, serialize the timestamp as ISO 8601, validate the decoded date and integer ID, and restore the date before calling this helper. Keep authorization and tenant filters server-controlled on every request; a cursor is not authorization.
-- Equivalent boundary, illustrative SQL rather than a captured Prisma query.
SELECT * FROM "User"
WHERE "role" = 'member'
AND ("createdAt" < $1 OR ("createdAt" = $1 AND "id" < $2))
ORDER BY "createdAt" DESC, "id" DESC
LIMIT 21;The schema's composite index matches the ordering. For a frequently filtered feed, consider leading equality columns such as role or tenantId in the index and verify the plan. Ascending or mixed-direction sorts require corresponding comparison directions.
Deleting the boundary row does not invalidate the saved tuple, but keyset does not freeze the dataset. Updating a sort value can cause skips or repeats; new records before the saved boundary are absent from later pages. Use stable sort values and a defined snapshot/export strategy when a fixed dataset is required.
Before shipping, test tied timestamps spanning a page boundary, deletion of the boundary row, empty/final pages, and changes before that boundary. Every traversal should retain the same sort and filters.
Using @nestarc/pagination
@nestarc/pagination provides NestJS query parsing, response links, and both cursor strategies. After installation, configure the same newest-first tuple:
import { paginate, type PaginateQuery } from '@nestarc/pagination';
async function listUsers(query: PaginateQuery, prisma: PrismaClient) {
return paginate(query, prisma.user, {
sortableColumns: ['createdAt', 'id'],
paginationType: 'cursor',
cursorStrategy: 'keyset',
cursorColumns: ['createdAt', 'id'],
defaultSortBy: [['createdAt', 'DESC'], ['id', 'DESC']],
where: { role: 'member' },
});
}The first request can omit after because cursor mode is explicit. Follow the returned links.next or links.previous, preserving filters and sort; package cursors have their own format. Version 0.3.2 includes the keyset navigation fixes from 0.3.1 and count-free offset navigation fixes. With countStrategy: 'none', offset totals are omitted and links.last is null; follow links.next until it becomes null. See the response contract.
The package also supplies filter operators, multi-column sorting, case-insensitive contains search, and Swagger documentation. An exact total still has a database cost; choose the count policy independently of pagination strategy.