Cursor Pagination in Prisma: Why Offset Fails at Scale and How to Fix It

The offset pagination trap

You ship a feed endpoint with skip and take. Page 1 loads in 12 ms. Page 500 takes 400 ms. Page 10,000 times out. The database isn't broken — it's doing exactly what OFFSET 500000 LIMIT 20 asks: scan half a million index entries, discard them, then return twenty rows. That scan cost grows linearly with the page number.

Cursor-based pagination sidesteps the scan. Instead of counting rows to skip, you hand the database a bookmark: "start after this unique id." With an index on that column, the engine seeks directly to the position. Page 10,000 costs roughly the same as page 1.

How Prisma implements cursors

Prisma's findMany accepts a cursor object that points at a single unique field value — typically the primary key. Combined with take and an orderBy on that same unique column (or a unique-then-tiebreaker pair), the generated SQL uses a WHERE id > $cursor ORDER BY id LIMIT $take pattern. No OFFSET appears.

Two knobs control the boundary:

• Without skip — the cursor record itself is included in the result set.
• With skip: 1 — Prisma moves one row past the cursor, so the cursor record is excluded.

The skip: 1 variant is the typical choice for "next page" links because the client already has the cursor record from the previous response.

Worked example: a stable feed endpoint

Imagine a Post model with an auto-increment id and a createdAt timestamp. You want an infinite-scroll feed sorted newest-first. Because createdAt can collide, you order by createdAt then id — a unique composite ordering. The example below assumes a recent Prisma version (4.x/5.x) and a Next.js route handler; adapt the request plumbing to your framework.

// schema.prisma
model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String
  createdAt DateTime @default(now())
  @@index([createdAt, id])
}

The endpoint accepts an optional cursor (base64-encoded {"createdAt": "...", "id": 123}) and a limit (default 20). It requests limit + 1 rows to detect whether a next page exists.

// GET /api/posts?cursor=<base64>&limit=20
import { PrismaClient } from '@prisma/client';
import { NextRequest, NextResponse } from 'next/server';

const prisma = new PrismaClient();

export async function GET(req: NextRequest) {
  const { searchParams } = new URL(req.url);
  const limit = Math.min(parseInt(searchParams.get('limit') ?? '20', 10), 100);
  const cursorParam = searchParams.get('cursor');

  let cursor: { createdAt: Date; id: number } | undefined;
  if (cursorParam) {
    try {
      cursor = JSON.parse(Buffer.from(cursorParam, 'base64').toString());
      cursor.createdAt = new Date(cursor.createdAt);
    } catch {
      return NextResponse.json({ error: 'Invalid cursor' }, { status: 400 });
    }
  }

  const posts = await prisma.post.findMany({
    take: limit + 1,
    cursor: cursor ? { createdAt_id: cursor } : undefined,
    skip: cursor ? 1 : 0,           // exclude the cursor record we already sent
    orderBy: [{ createdAt: 'desc' }, { id: 'desc' }],
  });

  let nextCursor: string | null = null;
  if (posts.length > limit) {
    const next = posts.pop(); // remove the extra sentinel row
    nextCursor = Buffer.from(JSON.stringify({
      createdAt: next.createdAt.toISOString(),
      id: next.id
    })).toString('base64');
  }

  return NextResponse.json({ posts, nextCursor });
}

Note that the composite cursor createdAt_id requires a matching @@unique([createdAt, id]) constraint on the model; if you only have the index, cursor on id alone and order by id instead. Run this with DEBUG=prisma:query (or new PrismaClient({ log: ['query'] })) and you should see SQL shaped like:

SELECT * FROM "Post"
WHERE ("createdAt", "id") < ($1, $2)
ORDER BY "createdAt" DESC, "id" DESC
LIMIT 21

No OFFSET. The database seeks to the composite key and reads forward. Verify the actual generated SQL on your version rather than assuming this exact shape.

Trade-offs you accept

• No random access. Clients can't request "page 47." They must walk the chain from the start or a saved cursor. That's fine for infinite scroll; it's a blocker for paginated admin tables with page-number inputs.
• No total count. count() still scans. If you need "showing 1–20 of 12,345," run a separate indexed count query or cache the total.
• Write concurrency. A new row inserted before the current cursor won't appear in already-fetched pages. The feed is eventually consistent — usually acceptable, but document it.
• Unique ordering required. You cannot cursor on createdAt alone. The unique constraint and matching orderBy are mandatory.
• The cursor is not a security boundary. Base64 encoding is obfuscation, not protection — treat cursor contents as untrusted client input and validate them.

Verify before you ship

1. Seed 50,000 rows. Page through with the cursor endpoint and assert zero duplicate ids and no gaps in the sequence you traverse.
2. Enable query logging (new PrismaClient({ log: ['query'] })) and confirm the paginated query uses a WHERE on the cursor columns plus LIMIT, not OFFSET.
3. Benchmark skip: 50000, take: 20 versus a cursor at the same logical position on your actual dataset and index layout.
4. Test edge cases: empty result, final partial page, and the skip: 1 boundary when the cursor record has been deleted.

If your UI needs numbered pages, keep offset pagination for that view and use cursors for the feed. They're not mutually exclusive — they're tools for different access patterns.