7

I am using PostgreSQL with PgBouncer, and receive the following error in my application:

08P01: no more connections allowed (max_client_conn)

pgbouncer.ini has the following configuration:

max_client_conn = 100
default_pool_size = 20
pool_mode = session

postgresql.conf has:

max_connections = 100

The PostgreSQL server has 5 databases that are accessed by a single user.

My understanding is that max_connections should be equal to (default_pool_size * # databases), and that max_client_conn should be at least this much. I have also seen the comment in the pgbouncer.ini that the default_pool_size should be equal to the max_client_conn if using session pooling.

So to resolve this error I am encountering, should I simply set default_pool_size = 100? Or increase just the max_client_conn? Or something else?

Improve this question

1 Answer 1

Reset to default
14

The point of using PGBouncer is to let it pool connections to the PG server. If your PG max_connections = 100 and PGBouncer max_client_conn = 100 then you aren't pooling anything. You should set your PGBouncer max_client_conn to a much higher number to allow it to marshal actual PG connections to service the clients. I've seen max_client_conn set to default_pool_size * # databases * user count * some arbitrary number (10) to allow enough PGBouncer connections to service all the databases connected to by all the users with an arbitrary (10) connection multiplier. For example, I have a PG server with one database, max_connections = 100, a PGBouncer default_pool_size of 20 and a PGBouncer max_client_conn = 1000. That effectively allows PGBouncer to pool 20 connections to my single database while allowing my client 1000 connections to process it's data. (I'm capturing a lot of data real time from a web api).

Improve this answer
3
  • “while allowing myself client 1000 connections to process” - can you please explain? Those 1000 clients connect to pg_bouncer, but then use a pool of 20? Is that right? Thanks
    – Kevin Meredith
    Apr 6, 2020 at 2:56
  • 1
    Close. Consider each client app to consume one of the 20 available connections in the pool (simultaneously.) Each of those clients can make up to 1000 connections (per client, essentially) to the database to process requests. So if I have 20 individual clients, each creating 1000 connections to do their work, PGBouncer will handle all 20,000 'sessions' without tearing down and recreating each of the 20 pool connections between client connections, thereby reducing total time for each 'session' (no build/teardown time). –
    – Scott Reeves
    Jan 16, 2021 at 17:17
  • Thanks @ScottReeves this makes sense. I have been wrapping my head around a recommended values for these settings.
    – realnsleo
    Oct 1, 2021 at 15:31

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.