Hi everyone; I am attempting to leverage pgbouncer with Prisma and setting the

pgbouncer=true

flag on our connections. However our application still seems to be creating a connection pool of

2n+1

(so in our case 5 as these are 2 core machines) on our GCP Cloud Run applcation. I wanted to know: 1. What is the recommendation w.r.t to connection pooling in the application (Prisma) when we have pgBouncer configured. I was under the assumption that the application shouldn't pool connections at all and should opt rather to simply "request" a new connection (or create a new one) per query. 2. What happens if we set the

connection_limit

to 0? Does this just disable application side pooling? The recommendation seems to be setting it to "1" however our intended behavior is that Prisma defers entirely to pgbouncer to supply connections on demand. 3. Prisma chunks large / complex queries into a series of SELECT statements one after the other. How does this behavior impact settings + usage of pgbouncer? 4. I assumed setting

pgbouncer=true

should have disabled the client side application pool totally. I am still seeing (however) "timeouts" fetching a new connection with connection limit printing 5. Is that expected?

Hey @Pranav Sathyanarayanan 👋 Apologies for the delay in getting back to you. I am trying to understand your use case, I have usually seen pgbouncer being used in a Serverless context, but in your case, it seems you are using it with GCP Cloud Run, so is there any particular reason for this? PgBouncer and Prisma’s connection pool are on two different levels. Prisma defines how many connections PrismaClient can use at any point in time in parallel, and PgBouncer makes sure that the database connection limit is increased to the outside world. Setting the connection limit to

1

for example will ensure Prisma will never run two queries in parallel but wait for the first one to finish before starting the second one.

What happens if we set the

connection_limit

to 0?

It would consider the connection_limit as unlimited and not disable application side pooling.

I am still seeing (however) “timeouts” fetching a new connection with connection limit printing 5. Is that expected?

Can you enable metrics and check the connection pooling statistics?

@Nurul Appreciate the response. I actually believe we have it figured out; for the time being we will up our application's connection limits past

2n+1

. The issue I was experiencing was actually cross-regional latency on GCP. The "Prisma" pool was still timing out because older connections were taking 10x longer than before as I had my pgBouncer. in a different region than the Cloud Run instance running Prisma.

Hi @Pranav Sathyanarayanan, Thanks for getting back to me, So if I understand correctly moving pgbouncer closer to the GCP cloud run instance fixed the issue for you?

Exactly. We had an Autopilot Kubernetes cluster in

us-central1

hosting pgbouncer (simple config, connect to CloudSQL over Private IP). However our Cloud Run instance was in

us-east1

. A simple latency test e2e (15 line nodejs app) showed that avg roundtrip latency for ~255ms for a simple

SELECT uuid FROM user LIMIT 1

query when Cloud Run was communicating to GKE inter-region vs intra-region. 25ms over 100 queries (serial) us-east1 -> Serverless VPC Connector-> us-east1 255ms over 100 queries (serial) us-east1 -> Serverless VPC Connector -> us-central1

Amazing, thanks for the follow up response 🙌 Also, Our Product team would be very interested in jumping on a quick call to understand a bit more about your use case and any other pain points or feedback you could share with us. Would you have some time maybe next week? Feel free to select a time that works for you here: https://calendly.com/d/dxt-c8n-2gh/prisma-product-team-user-interview