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

Two surprises:

1. Offset degradation is already measurable at 10K rows — page 100 was about 2.5x slower than page 1 in this run
2. Cursor + non-PK sort remains costly — the deep createdAt case was about 19x slower than the deep ID cursor

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

In this benchmark, the deep ID cursor was about 78% faster than deep offset. Composite keyset was not measured in that table; benchmark it on your own query before claiming a speedup.

Keyset Pagination with createdAt and id

After installing the module, configure an endpoint with an explicit sort tuple:

import { paginate, type PaginateQuery } from '@nestarc/pagination';

// Inside a service with an injected PrismaService.
async function listUsers(query: PaginateQuery, prisma: PrismaService) {
  return paginate(query, prisma.user, {
    sortableColumns: ['createdAt', 'id'],
    paginationType: 'cursor',
    cursorStrategy: 'keyset',
    cursorColumns: ['createdAt', 'id'],
    defaultSortBy: [['createdAt', 'DESC'], ['id', 'DESC']],
  });
}

Here PrismaService is your application's client provider and User.id is unique. Both fields must be populated and stable. The first request can omit after because paginationType: 'cursor' selects cursor mode. Follow the returned links.next URL for the next page and preserve the same sort and filters. See the cursor response format.

For a Prisma model named User without @@map or field @map declarations, the matching schema index is:

@@index([createdAt(sort: Desc), id(sort: Desc)])

With descending order, the next-page boundary is conceptually:

-- Illustrative SQL, not a captured Prisma query.
SELECT * FROM "User"
WHERE "createdAt" < $1
   OR ("createdAt" = $1 AND "id" < $2)
ORDER BY "createdAt" DESC, "id" DESC
LIMIT 21;

If two users share a timestamp, id orders them unambiguously. Include equality filters such as tenantId when designing the index for a tenant-scoped endpoint, and inspect the resulting plan. Ascending or mixed-direction sorts need their corresponding comparison directions; do not reuse this descending predicate blindly.

A cursor does not freeze the dataset across HTTP requests. Changing sort values while paging can still cause skips or repeats, and newly inserted records may appear before the saved boundary. Use stable ordering and a defined snapshot/export strategy when you need a fixed dataset.

Using @nestarc/pagination

@nestarc/pagination supports offset and cursor pagination, with Prisma and keyset cursor strategies:

// Auto-detects mode: offset by default, cursor when ?after= is present
@Get()
async findAll(@Paginate() query: PaginateQuery) {
  return paginate(query, this.prisma.user, {
    sortableColumns: ['id', 'name', 'createdAt'],
    filterableColumns: { role: ['$eq', '$in'] },
    searchableColumns: ['name', 'email'],
  });
}

12 filter operators, multi-column sorting, full-text search, and Swagger auto-documentation included.

Documentation · GitHub · Benchmark