Implementing High-Performance Pagination in Prisma with Cursor-Based Navigation

The Performance Gap: Offset vs. Cursor

When datasets grow into the hundreds of thousands or millions of records, standard offset-based pagination (using skip and take) causes significant database latency. This happens because the database must scan and discard all previous rows before reaching the requested offset, leading to linear performance degradation.

Cursor-based pagination solves this by using a unique identifier—the cursor—to mark the exact position of the last retrieved record. The database jumps directly to that record using an index, ensuring that fetching page 1,000 is as fast as fetching page one.

Prerequisites

• Prisma Client installed and configured.
• A database schema with a unique field (usually the primary key id) to serve as the cursor.
• A defined sort order for the query to ensure result stability.

Implementing Forward Pagination

To fetch the next set of records, you provide the unique ID of the last item from the previous page and a positive take value. Note that Prisma includes the cursor record itself in the result set, so you typically request take: limit + 1 and slice the first element off in your application logic.

// Run this in your application server (Node.js/TypeScript)
// Required permissions: Database read access

async function getNextPage(cursorId: string | null, limit: number = 10) {
  const results = await prisma.post.findMany({
    take: limit + 1, // Fetch one extra to check if there is a next page
    cursor: cursorId ? { id: cursorId } : undefined,
    orderBy: { id: 'asc' },
  });

  // Remove the cursor record itself from the results
  const records = cursorId ? results.slice(1) : results;
  
  return {
    data: records.slice(0, limit),
    nextCursor: records.length > limit ? records[limit - 1].id : null,
  };
}

Implementing Backward Pagination

Navigating to the previous page requires a negative take value. This tells Prisma to look at records preceding the cursor. Because the database returns these records in reverse order, you must manually reverse the array in your code to maintain the correct UI sequence.

// Run this in your application server (Node.js/TypeScript)

async function getPreviousPage(cursorId: string | null, limit: number = 10) {
  const results = await prisma.post.findMany({
    take: -(limit + 1),
    cursor: cursorId ? { id: cursorId } : undefined,
    orderBy: { id: 'asc' },
  });

  // Remove the cursor record and reverse the array to restore 'asc' order
  const records = cursorId ? results.slice(0, -1).reverse() : results.reverse();

  return {
    data: records.slice(-limit),
    prevCursor: records.length > limit ? records[0].id : null,
  };
}

Comparison: Offset vs. Cursor

Feature	Offset (skip/take)	Cursor (cursor/take)
Performance	Degrades as page number increases	Constant regardless of page depth
Data Stability	Items can be skipped/duplicated if rows are inserted	Stable; anchors to a specific record
Jump to Page X	Supported natively	Not supported (must navigate sequentially)
Requirement	None	Unique, indexed field

Verification and Diagnostics

To verify the implementation, execute a query with a known ID as the cursor and a take: 10. Confirm that the first record returned is the one immediately following the cursor in the specified sort order.

For performance validation, use database profiling (e.g., EXPLAIN ANALYZE in PostgreSQL). Compare a query with skip: 10000 against a query with a cursor at the 10,000th record. The cursor query should show a significantly lower execution time and fewer scanned rows.

Limitations

• No Random Access: You cannot jump directly to page 50 without knowing the cursor of page 49.
• Sorting Constraints: The field used as the cursor must be unique. If you sort by a non-unique field (like createdAt), you must combine it with a unique ID to avoid skipping records with identical timestamps.