1

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?

Improve this question

1 Answer 1

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

What about the Prisma connection_limit parameter? How should that be relative to the pool_size? Can it be greater and then Prisma's connection attempts will be queued by pgbouncer?
prisma's connection_limit should be set according to your expected client connections, this the thing you need to define first. For example if you expecting 100 connections , you need to set prisma's client_connection=100 and pgbouncer's max_client_connections=100. If you set to <100, say 50 then the 51st connection attempt to pgbouncer will result in an error because it exceeds max_client_connections. Hence to achieve queuing you need to set max_client_connections>=connection_limit.
What about in a horizontal scaling scenario, where multiple instances of the same app connect to the same db with same user. assuming the db's max connections is 100, and i have a connection_limit set on prisma to 10, but this app can scale to a max of 20 instances (10*20=200), how should I configure pgbouncer?
@rhyek This is no different, if you expecting 200 concurrent client connections, you may set max_client_connections >=200 . The expensive thing here is the DB connection and in your case since only one DB and one user you only have to define one pool. If you can have 100 connections, you can set pool size as 100. But if you know your query workloads in terms of rate and execution time you can optimise the pool size to even lower. pgbouncer offering a virtual DB named pgbouncer which you can check to see how fast your client connections are served.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.