Skip to content
Tutorials Center
My learning

Topics

Lesson 28 of 36 ยท Performance and Patterns

Pagination: Offset and Cursor

Paginate Prisma results with skip and take or with cursor-based keyset pagination, understand why offset gets slow and unstable, and build a stable next-page API.

Returning every row of a large table is slow, wastes memory and overwhelms users. Pagination returns one page at a time. Prisma supports two styles, offset (skip and take) and cursor (cursor and take). They behave very differently as data grows and changes.

Prisma code is read-only; the TryIts are labelled stand-ins on node:sqlite that run the SQL these options produce.

Offset pagination: skip and take

TypeScript
const page = 3, pageSize = 20;

const [posts, total] = await prisma.$transaction([
  prisma.post.findMany({
    orderBy: { id: 'asc' },
    skip: (page - 1) * pageSize,
    take: pageSize,
  }),
  prisma.post.count(),
]);
const totalPages = Math.ceil(total / pageSize);

It becomes LIMIT 20 OFFSET 40. It is simple, supports "jump to page 7" and page numbers, and is fine for small or admin tables. Always add a deterministic orderBy (ideally ending with a unique field like id); without it the database may return rows in any order, so pages overlap or skip.

Two problems appear at scale:

  1. Slow deep pages. OFFSET 100000 makes the database read and throw away 100,000 rows before returning yours.
  2. Unstable pages. If a row is inserted or deleted while the user browses, everything shifts: they see a duplicate or miss a row.

Cursor pagination

A cursor is the position of the last row you returned. The next page starts right after it:

TypeScript
const posts = await prisma.post.findMany({
  orderBy: { id: 'asc' },
  take: 20,
  skip: cursorId ? 1 : 0,                       // skip the cursor row itself
  cursor: cursorId ? { id: cursorId } : undefined,
});
const nextCursor = posts.length === 20 ? posts[posts.length - 1].id : null;

The database seeks straight to the cursor row through the index (cost does not grow with page depth) and nothing shifts when rows are added. The trade-offs: no "jump to page N", and you need a unique, ordered field. For a non-unique sort such as createdAt, order by [{ createdAt: 'desc' }, { id: 'desc' }] and make the cursor use the unique id.

The portable SQL idea behind it is keyset pagination: WHERE id > :lastId ORDER BY id LIMIT 20.

Node.js Stand-in: offset shifts when data changes, keyset does not

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Returning pagination metadata

For an API, return the page plus what the client needs to continue:

TypeScript
async function listPosts(cursor?: number, limit = 20) {
  const rows = await prisma.post.findMany({
    take: limit + 1,                              // fetch one extra to detect another page
    ...(cursor && { cursor: { id: cursor }, skip: 1 }),
    orderBy: { id: 'asc' },
  });
  const hasMore = rows.length > limit;
  const items = hasMore ? rows.slice(0, limit) : rows;
  return { items, nextCursor: hasMore ? items.at(-1)!.id : null };
}

Fetching limit + 1 rows answers "is there more?" without a separate count(). Counting a huge table on every request is itself expensive; consider returning an approximate total or none at all. Always cap limit (for example at 100) so clients cannot request a million rows.

Backward paging and negative take

take: -20 with a cursor reads backwards: the 20 rows before the cursor, returned in the original order. Combined with take: 20 going forward, it lets you build previous and next links.

Choosing

Need Use
Admin table, page numbers, under about 10k rows Offset
Feed, infinite scroll, API for large data Cursor
Export all rows Cursor loop in batches

Whatever you pick, make sure the orderBy columns are indexed (see indexes and query plans).

Node.js Stand-in: walk a table in cursor batches

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why does deep offset pagination get slow?

    OFFSET N still walks past N rows, so cost grows with page depth.
  2. 2. Which problem does cursor pagination avoid?

    A cursor marks a position, so new rows do not push existing ones to a different page.
  3. 3. In a cursor query you pass the last id as cursor. Why add skip: 1?

    The cursor row is included by default, so skip 1 starts after it.
  4. 4. How can you detect a next page without count()?

    An extra row proves another page exists, and you drop it before returning.

Summary

  • Offset (skip/take) is simple and supports page numbers but is slow and unstable on big, changing tables.
  • Cursor pagination seeks from the last row; it needs a unique ordered field and has no random page jumps.
  • Always use a deterministic orderBy ending in a unique column and cap the page size.
  • Fetch limit + 1 to know whether another page exists.
  • Index the sort columns.

Next: Indexes and Query Plans