Choosing Between Offset and Cursor Pagination in Prisma

The Pagination Performance Gap

When building REST APIs with Prisma, the primary challenge is maintaining consistent response times as your database grows. The most common mistake is relying on skip and take for all pagination needs. While this approach is intuitive, it creates a performance bottleneck known as the "offset overhead," where the database must scan and discard thousands of rows before returning the requested page.

The goal is to choose a strategy that balances developer velocity with the ability to handle millions of records without linear latency increases.

Comparing Pagination Strategies

Feature	Offset (Skip/Take)	Cursor-based
Mechanism	skip: n, take: m	cursor: { id: x }, take: m
UI Pattern	Page numbers (1, 2, 3...)	Infinite scroll / "Load More"
Performance	Degrades as offset increases	Constant time (O(1) index seek)
Data Stability	Items shift if records are added/deleted	Stable; items are anchored to the cursor
Requirements	None	Unique, indexed sort field

Trade-offs and Decision Logic

Use Offset Pagination (Skip/Take) when:

• The total dataset is small (e.g., under 10,000 records).
• The business requirement specifically demands jumping to a specific page number (e.g., "Go to page 45").
• The data is static and rarely changes, meaning record shifts won't confuse the user.

Use Cursor-based Pagination when:

• You are building a feed or a list that scales to millions of rows.
• The application has high write concurrency (inserts/deletes), which would cause offset pagination to skip or duplicate items.
• You are implementing infinite scroll or "Next/Previous" navigation.

Implementing Cursor-based Pagination

Cursor-based pagination works by using a unique identifier (the cursor) to tell the database exactly where the last page ended. In Prisma (v4.x+), the cursor property identifies the starting point, and skip: 1 is used to avoid including the cursor record itself in the next result set.

// Run this in your TypeScript service layer
// Required permissions: Database read access via Prisma Client
async function getPosts(cursor?: string, limit: number = 20) {
  try {
    return await prisma.post.findMany({
      take: limit,
      // skip: 1 ensures the cursor record isn't duplicated on the next page
      skip: cursor ? 1 : 0,
      cursor: cursor ? { id: cursor } : undefined,
      orderBy: { id: 'asc' },
      select: {
        id: true,
        title: true,
        author: { select: { name: true } },
      },
    });
  } catch (error) {
    console.error("Pagination error:", error);
    throw new Error("Failed to fetch paginated results");
  }
}

Implementation Details:

• Placeholders: Replace prisma.post with your actual model name.
• The Cursor: The cursor must be a unique field (typically the Primary Key).
• Ordering: The orderBy field must match the field used in the cursor object.

Verification and Limitations

To verify the implementation, seed a database with 150 records. Request pages of 20. Ensure that the 20th record of Page 1 is not the 1st record of Page 2. If you see duplicates, check that skip: 1 is applied when a cursor is present.

Performance Check: Use your database's EXPLAIN ANALYZE tool via prisma.$queryRaw. An efficient cursor query will show an Index Scan or Index Seek. An inefficient offset query will show a Seq Scan or a high number of "rows removed by filter" as the page number increases.

Limitations:

• Reverse Navigation: Moving to a "Previous" page is more complex. You must reverse the orderBy direction and the take logic, then reverse the resulting array in memory.
• Non-Unique Fields: If you use a non-unique field as a cursor, Prisma may return inconsistent results or skip records that share the same value as the cursor.