Pagination - Offset-Based vs Cursor-Based

When an endpoint returns a list (orders, products, comments...), it cannot return all records at once if the table contains hundreds of thousands or millions of rows, because this exhausts Node's RAM and creates massive response bottlenecks. Pagination resolves this by returning only a small subset at a time; however, the conventional pagination approach (skip/take) carries a performance pitfall easily overlooked by beginners, which is particularly critical for large tables or infinite scroll feeds.

1. Why unbounded findMany() should not be used

• db.order.findMany({ where: { status: 'pending' } }) without take/limit: if 500,000 records match the condition, Node must hold all 500,000 objects in RAM simultaneously to process and return them.
• The consequence mirrors the large-file fs.readFile() issue learned in Phase 1 (Streams): it can trigger heap out of memory errors, slow down the server, and bottleneck large JSON responses sent to the client.
• You must always limit the number of records returned per request using take (or limit).

2. Offset-based pagination (skip/take)

The most familiar approach, using page numbers:

const orders = await db.order.findMany({
  where: { status: 'pending' },
  take: 20,
  skip: (page - 1) * 20,
});

• The problem: even with indexes on the filter/sort columns, the DB must still count and discard all rows preceding the skip threshold before retrieving the exact 20 rows needed.
• As page numbers increase (higher skip values), query time grows progressively slower, with complexity increasing nearly linearly with page depth (for example, page 10,000 with skip: 199980 will be noticeably slower than page 1).
• Advantage: allows jumping directly to a specific page number (e.g. "go to page 50"), suitable for table-based UIs with numbered pagination controls.

3. Cursor-based pagination

Uses the value of the last row on the previous page as a reference marker (cursor), rather than counting skipped rows:

const orders = await db.order.findMany({
  where: { status: 'pending', id: { gt: lastSeenId } },
  take: 20,
  orderBy: { id: 'asc' },
});

• Because id (or the column used as the cursor) is already indexed, WHERE id > lastSeenId leverages the index to jump directly to the exact starting position without counting or scanning past preceding rows.
• Query performance remains stable and constant, independent of how deep you are querying, because conceptually there are no "pages", only "fetching records starting after the last marker."
• Trade-off: cannot jump directly to a specific ordinal page position (no concept of "page 50"), supporting only forward or backward sequential traversal from the current cursor. Ideal for infinite scroll feeds (such as social networks), but unsuitable for table UIs with numbered page selectors.

4. When to choose which