Stop Using skip for Large Datasets: Implementing Cursor Pagination in Prisma

The Performance Wall of Offset Pagination

When building a list view, the instinct is to use skip and take. This mimics the traditional page-numbering system: "Give me 20 records, but skip the first 500." In Prisma, this translates directly to a SQL OFFSET clause.

The problem is that databases don't actually "jump" to row 501. They must scan through the first 500 rows, count them, and then discard them before returning the requested data. As your dataset grows to tens of thousands of rows, your "Page 100" request becomes significantly slower than your "Page 1" request, leading to API timeouts and database CPU spikes.

Cursor-Based Pagination: The Constant-Time Alternative

Cursor pagination avoids the scan by using a unique identifier—the cursor—to mark the exact spot where the last page ended. Instead of telling the database how many rows to skip, you tell it: "Start exactly after the record with this ID and give me the next 20."

Because the database can use an index to find the cursor instantly, the performance remains constant regardless of whether you are on the first page or the millionth page.

Comparison: Offset vs. Cursor

Feature	Offset (skip/take)	Cursor (cursor/take)
UI Support	Page numbers (1, 2, 3)	"Load More" or Infinite Scroll
Performance	Degrades as offset increases	Consistent (O(1) lookup)
Data Stability	Items shift if rows are deleted	Stable relative to the cursor
Complexity	Low	Medium (requires cursor tracking)

Worked Example: Implementing a Cursor

To implement this, you need a unique, sortable column. While an id is common, a createdAt timestamp is often used for chronological feeds. In this example, we assume a Post model with a unique id.

Run this logic in your backend service layer. Ensure your Prisma Client is up to date (v4.0+ recommended).

async function getPosts(cursorId?: string, limit: number = 20) {
  const posts = await prisma.post.findMany({
    take: limit,
    // If cursorId is provided, start after that record
    cursor: cursorId ? { id: cursorId } : undefined,
    orderBy: { id: 'asc' },
    // skip: 1 is used because the cursor record itself is included in the result
    skip: cursorId ? 1 : 0,
  });

  return {
    data: posts,
    // The ID of the last element becomes the cursor for the next request
    nextCursor: posts.length === limit ? posts[posts.length - 1].id : null,
  };
}

Implementation Details

• The skip: 1 Trick: Prisma's cursor API includes the cursor record in the results. To avoid showing the last item of Page 1 as the first item of Page 2, we skip exactly one record.
• Permissions: This query requires read permissions on the target table.
• Risk: If you use a non-unique field as a cursor, Prisma will throw a runtime error. Always use a @unique or @id field.

The Trade-offs: When to Stick with Offset

Cursor pagination is not a silver bullet. The primary limitation is that it does not support jumping to a specific page. You cannot jump to "Page 10" because the application doesn't know the ID of the last record on Page 9 without fetching the intervening data.

If your product requirement explicitly demands a numbered pagination bar (e.g., an admin dashboard for a small set of users), skip is the correct tool. If you are building a public-facing feed or a dataset that will scale, cursors are mandatory.

Verifying the Result

To verify your implementation, perform the following checks:

1. Boundary Check: Request a page that exceeds the total count. Ensure nextCursor returns null.
2. Stability Check: Fetch Page 1, delete the first record in the database, and fetch Page 2. With cursors, you should not see a duplicate of the second record; with offset, you likely will.
3. Performance Check: Using a database GUI or EXPLAIN ANALYZE, compare the execution plan of a high-offset query vs. a cursor query. The cursor query should show an Index Seek rather than an Index Scan.