Offset pagination performance limits in Prisma at scale

Direct Answer

Offset threshold: Prisma’s skip maps to SQL OFFSET, so the cost grows linearly with the offset value. There is no single hard cutoff; in most real‑world workloads the slowdown becomes noticeable when OFFSET exceeds a few thousand rows on a table that does not have a covering index. If the table is heavily indexed or the database engine can employ a seek, the threshold can be an order of magnitude larger.

Cursor consistency: Prisma does not enforce cursor uniqueness. When the cursor column is not unique (or part of a composite unique key), concurrent inserts or deletes can shift rows, leading to duplicated or missing items between pages. Therefore, cursor‑based pagination is only guaranteed to be consistent if the cursor column is uniquely identified.

Minimum cursor column attributes:

• Non‑nullable
• Part of a unique index (ideally the primary key) or a composite of columns that together form a unique key
• Included in the ORDER BY clause that Prisma uses for the page
• Declared as @id or @unique in the Prisma schema (or enforced by the database)

Likely Explanation

Because OFFSET requires the database engine to scan and discard rows up to the offset, the query planner will use a full table scan or a non‑covering index scan for large offsets. Even with a simple index on the ordering column, the engine still needs to walk the index entries up to the offset, which is an O(N) operation. In contrast, cursor pagination translates to a WHERE clause that seeks directly to the last seen key value, giving O(log N) or O(1) performance depending on the index.

Concurrent mutations affect cursor pagination only if the cursor column is not unique. Without uniqueness, the same key value can appear in multiple rows, and a new row inserted between two page requests can shift the ordering, causing duplicates or gaps. The database does not guarantee stable ordering for non‑unique columns, so the application must enforce uniqueness or accept the risk.

Confirmed Facts

• Prisma maps skip to SQL OFFSET and take to LIMIT.
• Cursor pagination in Prisma requires the cursor column(s) to be part of the ORDER BY clause.
• Prisma does not automatically enforce cursor uniqueness; developers must declare @unique or @id in the schema.
• Non‑unique cursor columns can lead to inconsistent page results under concurrent writes.
• For reliable cursor pagination, the cursor column(s) must be non‑nullable and part of a unique index.

Practical Steps for Your Case

1. Measure offset performance: Create a sample table with the same size and indexes as production. Run findMany with increasing skip values (e.g., 0, 5k, 10k, 20k) and record execution time. The offset where the query time starts to rise sharply is your practical threshold.
2. Verify cursor uniqueness:
   • Inspect the Prisma schema for @unique or @id on the cursor field(s).
   • Run a test insert that violates the uniqueness constraint; the database should reject it.
3. Implement cursor pagination:
   • Define the cursor field(s) as unique in the schema.
   • Use Prisma’s cursor option with take and orderBy that match the unique key.
   • Handle cursor errors by retrying or falling back to offset pagination if the cursor becomes stale.
4. Monitor under load:
   • Track page latency and any duplicate or missing rows during peak write periods.
   • Adjust the cursor strategy if anomalies appear.

Missing Diagnostic Detail

To provide a more precise offset threshold recommendation, could you share:

• The approximate row count of the target table.
• Which indexes exist on the ordering column(s).
• The database engine (PostgreSQL, MySQL, SQLite, etc.) and its version.
• Typical concurrent write rate for that table.

These details will help narrow down the practical limit for offset pagination in your environment.