I'm deploying a service that uses Prisma with postgresql and pgbouncer. What I would like to know is what to set the Prisma's connection_limit url parameter to, given that my database supports only 22 connections and pgbouncer is configured to use 22 connections. Can the connection_limit parameter be greater than 22? I think connection_limit configures Prisma's internal connection pool which in theory could be larger than what pgbouncer's connection limit which would result in the connections being queued up by pgbouncer.
Is my reasoning correct?
You need to set pgbouncer pool_size to 22 and set Max_client_connections to equal or higher than that of prisma. Pgbouncer will queue client connections if there is no available connection in the pool (of 22) and service each of connection in queue as soon as one of server (db)conn released back to pool. Pgbouncer only consumes (4kB) per connection and can handle thousands of client seemlessly.
I would recommend you set pool_mode to transaction for efficiently using the pool if you client connections not depending on any session level features of PostgreSQL.