Hi everyone 👋 , We are using Prisma (3.7.0) and I’ve been a fan of it for a very long time, but recently we are experiencing an enormous amount of timeouts, slow queries and generally bad performance. We are using Sentry transactions to measure query executions and the one that stands out most prominently is

client.challengeAttempt.findUnique({
  where: { challengeId_userId: { challengeId: 'ac', userId: 'dc' } },
})

model ChallengeAttempt {
  id String @id @default(cuid())

  challenge   Challenge @relation(fields: [challengeId], references: [id])
  challengeId String

  user   User   @relation(fields: [userId], references: [id])
  userId String

  @@unique([challengeId, userId])
}

My understanding is that querying a unique field should be almost instant while in our case the logs show that every day some queries need a couple of seconds (range from 200ms to 15s) to finish. We’ve also investigated that the payload is as small as it can be (and it’s small). We are currently processing at most three concurrent requests every second and the CPU/memory of our server barely scratches the potential. DB, on the other hand, is quite bad and reaches the spike every day even though we have 2GB of RAM. Apart from that, we are getting “Timeout fetching a new connection from the pool” even though the pool limit is 84 connections. (strangely, the logs show that there’s no active connection at all times) I’ve also seen this review (https://github.com/edgedb/imdbench) and wanted to ask: 1. Is 2GB of RAM for a db for an app of our size too little? 2. Is this a common problem? 3. How do you cope with low performance? At this point, we are considering switching to something else and help/feedback would be greatly appreciated 🙂

I don't think you set this up to be optimized for performance, all you have is a unique constraint which I don't think is going to give you an index on the challengeId or userId to speed up searching by those. I'd try removing the

id

field and unique constraint, and instead create a compound ID by doing

@@id([challengeId, userId])

which I'm hoping will give you the indexed search you want.

hmm that's an interesting take - I searched the web and found that Postgres creates indexes automatically for unique fields so it checks uniquness more quickly can you share what makes you think differently?

@Daan Helsloot This is an interesting topic for us to note as well

This question also got turned into an issue on my request: https://github.com/prisma/prisma/issues/12545