Prisma Pagination: Choosing Cursor Over Offset for Reliable, Scalable Paging

Why Pagination Matters in Modern Apps

When you expose a list of items—orders, posts, comments—to a user, you can’t load everything at once. Pagination splits the data into consumable chunks. But not all pagination strategies are created equal. In Prisma, the choice between skip/take (offset) and cursor/take (cursor) can dramatically affect performance and correctness.

The Core Problem

Large tables with millions of rows are common in production. A naive offset query like SELECT * FROM posts ORDER BY createdAt DESC LIMIT 10 OFFSET 1000 forces the database to scan and discard 1,000 rows before returning the next 10. If rows are inserted or deleted between page requests, the user may see duplicates or gaps.

Thesis: Use Cursor Pagination When You Need Predictable, Fast Pages

Prisma’s cursor‑based API, built on a unique key (usually an auto‑incrementing id or a composite unique field), guarantees stable results even when the underlying data changes. It also keeps query cost low regardless of the offset value.

Section 1 – How Cursor Pagination Works

Cursor pagination relies on a deterministic ordering column. Prisma translates a cursor into a keyset scan that the database can execute with an index seek:

prisma.post.findMany({
  where: { createdAt: { gt: lastSeenDate } },
  cursor: { id: lastSeenId },
  take: 10,
  orderBy: { createdAt: 'desc' }
});

Key points:

• Cursor value is the primary key of the last row returned on the previous page.
• OrderBy must reference the same column used in the cursor (or a composite that includes it) to maintain determinism.
• Prisma adds a WHERE id > :cursorId clause (or < for descending order) behind the scenes.

Section 2 – Performance Comparison

Consider a table with 2 million posts. Running the following two queries on PostgreSQL shows a stark difference in execution plans:

1. Offset query: SELECT * FROM posts ORDER BY createdAt DESC LIMIT 10 OFFSET 1000000; – the planner chooses a Seq Scan followed by a Top-N Heap sort, costing thousands of I/O operations.
2. Cursor query: SELECT * FROM posts WHERE id > 2000000 ORDER BY id ASC LIMIT 10; – the planner selects an Index Scan, seeking directly to the start of the page.

In practice, cursor queries remain fast even when the offset is in the millions, while offset queries can grow linearly with the offset value.

Section 3 – Reliability Under Concurrent Writes

Offset pagination can misbehave when rows are added or removed between page loads. For example, if a new post is inserted before the current page, the next page will skip that post and shift the entire list. Cursor pagination avoids this by anchoring the page to a specific key that cannot move.

However, cursor pagination requires that the ordering column be unique. If you order by a non‑unique field like createdAt alone, two rows with the same timestamp could appear in both pages. The fix is to add the primary key to the orderBy clause:

orderBy: [
  { createdAt: 'desc' },
  { id: 'asc' }
]

This guarantees a total order and prevents duplicates.

Section 4 – Trade‑Offs and Limitations

• Client complexity: The caller must store the cursor value (often the last id) between requests. This is trivial in a web client but adds state to server‑side pagination logic.
• Index requirement: The cursor column must be indexed. If you need to paginate on a non‑indexed column, you’ll have to create an index or fall back to offset.
• First page: The first request has no cursor. You can either omit the cursor field or use a sentinel value like cursor: { id: 0 } if your IDs are positive.
• Complex queries: When filtering by multiple conditions, ensure the cursor column is still part of the orderBy chain; otherwise Prisma may throw an error.

Actionable Checklist

1. Choose the ordering column – typically the primary key or a unique timestamp.
2. Add an index if not already present: CREATE INDEX posts_createdat_idx ON posts(createdAt, id);
3. Implement the query as shown in the example above, making sure orderBy matches the cursor column.
4. Test for determinism – run two consecutive pages with a new row inserted between them and verify no duplicates or gaps.
5. Check the query plan – enable Prisma's debug logs (DEBUG=*) and confirm the generated SQL includes the cursor condition and uses an index seek.

Conclusion

When you need scalable, consistent paging over large tables, cursor pagination is the recommended Prisma pattern. It keeps queries fast, eliminates the pitfalls of offset changes, and scales linearly with the page size rather than the offset value. For small, static datasets, offset pagination remains a quick and easy choice. Evaluate your data size, write patterns, and client state before deciding which strategy to adopt.