how to determine max_client_conn for pgbouncer

Question

I'm sort of an "accidental dba" so apologies for a real noob question here. I'm using pgbouncer in pool_mode = transaction mode. Yesterday I started getting errors in my php log:

no more connections allowed (max_client_conn)

I had max_client_conn = 150 to match max_connections in my postgresql.conf.

So my first question is, should pgbouncer max_client_conn be set equal to postgresql max_connections, or am I totally misunderstanding that relationship?

I have 20 databases on a single postgres instance behind pgbouncer with the default default_pool_size = 20. So should max_client_conn be 400? (pool_size * number_of_databases)?

Thanks

Answer 1

https://pgbouncer.github.io/config.html

max_client_conn Maximum number of client connections allowed.

default_pool_size How many server connections to allow per user/database pair.

so max_client_conn should be way larger then postgres max_connections, otherwise why you use connection pooler at all?..

If you have 20 databases and set default_pool_size to 20, you will allow pgbouncer to open 400 connections to db, so you need to adjust posgtres.conf max_connections to 400 and set pgbouncer max_client_conn to smth like 4000 (to have average 10 connections in pool for each actual db connection)

This answer is only meant to provide an example for understanding the settings, not as a statement to literally follow. (eg I just saw a config with:

max_client_conn = 10000
default_pool_size = 100
max_db_connections = 100
max_user_connections = 100

for cluster with two databases and max_connections set to 100). Here the logic is different, also mind max_db_connections is set and in fact connection limits are set individually per database in pgbouncer [database] section.

So - play with small settings to get the idea of how config influence each other - this is "how to determine max_client_conn for pgbouncer" the best