prisma: MongoDB `findUnique()` or `findUniqueOrThrow()` leads to COLLSCAN if multiple queries happen at the same time

We are facing similar issues with these non-optimized queries. Some of our collection grew a lot in the recent weeks and now these COLLSCAN became a serious issue. A simple findUnique is taking +30 seconds to resolve on a 2.5mi records collection.

By enabling the debug on Prisma queries, we can tell all of it is due to the misusage of $expr + $in. Seems like any Prisma query will lazily just wrap your query with the $expr command instead of building a more specific query.

As a workaround, we manage to replace some of our queries with a Prisma OR operator.

So instead of something like

prisma.users.findMany({ where: { id: { in: sortedMemberIds } })

We replaced with

prisma.users.findMany({ where: { OR: sortedMemberIds.map((id) => ({ id })) }})

It’s a silly workaround and doesn’t work for all the cases, but it helps for now. The problem is even worse in our project since we expose a GraphQL endpoint, and rely on Prisma/Nexus/PalJS generated resolvers. All of those will generate the offender queries. To mitigate, we introduced custom resolvers that will do the workaround query described above. It’s a huge drawback to the project, as we need to handle each individual resolver manually now.

From quickly scanning Prisma engine codebase, I think calls like these here are the culprit (I’m nowhere near to understand Rust code 😅). Instead of just passing the query to the $expr command, it should be building the mongodb specific query depending on the requested filters. Not a trivial task, I guess, but it’s what I’d expect from a data access library.

Overall, we had a bunch of drawback with the Prisma + MongoDB integration. We manage to work around some of them, but it’s becoming unsustainable. It was first introduced because we wanted to better integrate with our new GraphQL services and not having to write resolvers manually for everything. As it stands now, we are back on writing new code with mongoose.

I checked the logs again from the production, made sure that it does not run any old version of the code where findUnique or findUniqueAndThrow is used and still see quite a lot of COLLSCANS from $in-queries on the primary key. I created therefore a separate issue https://github.com/prisma/prisma/issues/23775

We are facing similar issues with these non-optimized queries. Some of our collection grew a lot in the recent weeks and now these COLLSCAN became a serious issue. A simple findUnique is taking +30 seconds to resolve on a 2.5mi records collection.

By enabling the debug on Prisma queries, we can tell all of it is due to the misusage of $expr + $in. Seems like any Prisma query will lazily just wrap your query with the $expr command instead of building a more specific query.

As a workaround, we manage to replace some of our queries with a Prisma OR operator.

So instead of something like

prisma.users.findMany({ where: { id: { in: sortedMemberIds } })

We replaced with

prisma.users.findMany({ where: { OR: sortedMemberIds.map((id) => ({ id })) }})

It’s a silly workaround and doesn’t work for all the cases, but it helps for now. The problem is even worse in our project since we expose a GraphQL endpoint, and rely on Prisma/Nexus/PalJS generated resolvers. All of those will generate the offender queries. To mitigate, we introduced custom resolvers that will do the workaround query described above. It’s a huge drawback to the project, as we need to handle each individual resolver manually now.

From quickly scanning Prisma engine codebase, I think calls like these here are the culprit (I’m nowhere near to understand Rust code 😅). Instead of just passing the query to the $expr command, it should be building the mongodb specific query depending on the requested filters. Not a trivial task, I guess, but it’s what I’d expect from a data access library.

Overall, we had a bunch of drawback with the Prisma + MongoDB integration. We manage to work around some of them, but it’s becoming unsustainable. It was first introduced because we wanted to better integrate with our new GraphQL services and not having to write resolvers manually for everything. As it stands now, we are back on writing new code with mongoose.

the issue for a {where: {id: {in: array}}} is tracked here, i think: https://github.com/prisma/prisma/issues/21453

This here is about colscan that are caused by findUnique. Do you also observe those in your project?

I am serious though, a quick issue from you with a plain and obvious reproduction would go a long way. You clearly have a reproduction case now, and as a new issue we would also try to reproduce and then potentially prioritize that.

The initial description is still correct. findUnique(OrThrow) lead to a colscan due to wrongly implemented batching. I hope its clear how problematic this issue is in production 😔. (and many of the other reported and well documented mongodb performance problems.

I can offer to create a repo with some test cases though, maybe i can do that over the weekend.

@macrozone you could do a client extension that would convert findUnique to findFirst automatically.

@macrozone if you want to opt out of batching, you can use findFirst(OrThrow) instead of findUnique(OrThrow).

If you are using composite unique you will need to flatten where:

prisma.model.findUnique({
  where: {
     name_email: {
         name,
         email
     }
  }
})

becomes:

prisma.model.findUnique({
  where: {
      name,
      email
  }
})

For plain unique, just replacing findUnique with findFirst is enough.

@janpio is it possible to disable batching? This bug is very serious and we need to take immediate action 😢