Pagination in Prisma with Cursor is Slow

Question

I've tried both methods (cursor and offset) to paginate through my ~1000 doc DB, however, I can't get cursor to be faster than offset.

// cursor method
    const allDocs = []
    let queryResults = await prismaClient.obsidian.findMany({
        take: 100
    })
    while (queryResults.length > 0) {
        allDocs.push(...queryResults)
        queryResults = await prismaClient.obsidian.findMany({
            take: 100,
            skip: 1,
            cursor: {
                id: queryResults[queryResults.length - 1]!.id
            }
        })
    }
    return allDocs

takes ~25 seconds to complete (NB: also it seems to be using OFFSET, I thought it wasn't supposed to do that?)

Query: SELECT "public"."obsidian"."id", "public"."obsidian"."doc", "public"."obsidian"."createdAt", "public"."obsidian"."updatedAt", "public"."obsidian"."filename" FROM "public"."obsidian", (SELECT "public"."obsidian"."id" AS "obsidian_id_0" FROM "public"."obsidian" WHERE ("public"."obsidian"."id") = ($1)) AS "order_cmp" WHERE "public"."obsidian"."id" >= "order_cmp"."obsidian_id_0" ORDER BY "public"."obsidian"."id" ASC LIMIT $2 **OFFSET** $3

    // offset method
    const count = await prismaClient.obsidian.count()
    const pages = Math.ceil(count / 100)
    const allDocsPromises = []
    for (let i = 0; i < pages; i++) {
        const page = prismaClient.obsidian.findMany({
            skip: i * 100,
            take: 100
        })
        allDocsPromises.push(page)
    }
    const allDocs = await Promise.all(allDocsPromises)
    return _.flatten(allDocs)

takes ~14 seconds to complete. My thought is that offset method is running faster due to Promise.all() but is there a way to get cursor pagination to return all the documents super fast?

Answer 1

Prisma is using some internal mechanisms to buffer data in order to speed up and resolve N+1 problem. But in some case, it would lead to poor performance.

This is a prisma middleware to bypass it's mechanism and speed up. If you think you are running slow, you may give it a try.

prisma.$use(async (params, next) => {
    if (params.args?.cursor) {
      const key = _.lowerFirst(params.model)
      const result = await prisma[key].findUniqueOrThrow({
        where: params.args.cursor,
        select: params.args.orderBy ? _(params.args.orderBy).mapValues(x => true).value() : undefined,
      })
      params.args.where = {
        ...params.args.where,
        ..._(params.args.cursor).mapValues((x, k) => ({
          [x === 'desc' ? 'lte' : 'gte']: result[k],
        })).value(),
        ..._(params.args.orderBy).mapValues((x, k) => ({
          [x === 'desc' ? 'lte' : 'gte']: result[k],
        })).value(),
      }
      delete params.args.cursor
    }
    return await next(params)
  })