1

I'm working with a tech stack of Prisma ORM and Postgres on Supabase with PgBouncer enabled. We're also using Google Cloud Run to deploy our Node/Express API.

When looking through logs for production, you will see occurances of the same errors being raised again and again. One such example looks like:

Timed out fetching a new connection from the connection pool. More info: http://pris.ly/d/connection-pool (Current connection pool timeout: 10, connection limit: 1)

I've read up on this page: https://www.prisma.io/docs/concepts/components/prisma-client/working-with-prismaclient/connection-pool.

The timeout of 10 seconds is the Prisma connection pool default and the connection limit of 1 is set via the query parameter of the connection string I use, postgresql://[USER]:[PASSWORD]@localhost:[PORT]/[NAME]?pgbouncer=true&connection_limit=1, given from the docs also: https://www.prisma.io/docs/guides/performance-and-optimization/connection-management/configure-pg-bouncer.

This was done because we thought multiple instances of our API containers would hog the connections to PgBouncer.

My question is:

  1. Have I set up PgBouncer correctly, and if not, how can I look into setting it up properly?
  2. As I'm trying to use PgBouncer, does that make my messing around with the connection_limit query parameter pointless? Should I just get rid of that?
  3. How can I stop these timeouts from occurring?

What I've tried:

  • Removing the connection_limit - this seemed to have little to no effect.
  • Reviewing Supabase -> Database -> Roles for connections - of the 60 max connections I see pgbouncer in the list with connections - so I assume this means pgbouncer is working?
  • Looking through pg_stat_activity - I'm seeing 2 different client addresses across multiple processes, almost all idle (I assume because pgbouncer is managing them?)
  • I'm thinking of increasing the pool timeout from the default of 10, but I don't really want to do that.
truthordare
  • 13
  • 2
  • "Removing the connection_limit - this seemed to have little to no effect" It is hard to see how this would have no effect. Are you sure this change was put into use? At the very least, it should have changed the value reported in the error message, if not changing the error entirely to a different one. – jjanes Apr 12 '23 at 23:53
  • Hi @jjanes! Yes, I changed the connection string we use. The messages continued but the value did change. – truthordare Apr 13 '23 at 08:14
  • Also, it's worth clarifying that `connection_limit` isn't to do with PgBouncer. – truthordare Apr 13 '23 at 09:59
  • How high did you test for connection_limit? What happened when you removed it? (I don't know if prisma treats that as infinite, or as 1.) – jjanes Apr 13 '23 at 13:01
  • I removed the `connnection_limit` query parameter so it fell back to the Prisma default (cpus * 2 + 1) which for us was 5. It then continued for a day or so before the time out logs returned (as per the log I initially sent, except 1 being 5) then fell over completely (unable to connect), so I had to add it back to get it working again. – truthordare Apr 13 '23 at 20:36
  • Hey, I've also been hitting the same issue - were you able to figure out a work around? Did increasing the timeout work? – kungfoo Aug 10 '23 at 08:50
  • Not really. We've found increasing the timeout to 15s helped, but we're still not sure why as the process that it's timing out on doesn't take that long. Connection limit needs to be 1 with PgBouncer. Without it it'll overuse Supabase connections and eventually crash the DB. If you're using the same structure then try increasing the timeout slightly and then debug in Supabase. – truthordare Aug 12 '23 at 20:03

1 Answers1

1

1 & 2: Looking at the Prisma docs, you should not be using connection_limit=1 since you're using an external connection pooler, i.e. PgBouncer. Have you tried increasing the connection_limit beyond the Prisma default?

3: There are also some troubleshooting steps here related to the timeout error.

soedirgo
  • 31
  • 2
  • 2