Why Posgresql give 30% connections for auth_user of PgBouncer

Question

I have a problem with Postgresql use PgBouncer Pool
I have 40 DB
My app just use 1 user to connect to DB (userA). But when I query pg_stat_activiy, I found about 230 connections idle from user with name "userA" and 160 connections from user with name "pgbouncer".
All connections from user with name "pgbouncer" just have query info is "insufficient privilege", didn't have any info like: backend_start, query_start, ....

My PgBouncer Config

[databases]
* = host=localhost port=1234 auth_user=pgbouncer

[pgbouncer]
listen_port = 1234
listen_addr = *
auth_type = md5
auth_file = users.txt
auth_query = SELECT username, password from ...
pidfile = pgbouncer.log
logfile = pgbouncer.pid
admin_users = pgbouncer
stats_users = pgbouncer
default_pool_size = 30
max_client_conn = 2000
max_db_connections = 0
min_pool_size = 0
pool_mode = transaction
reserve_pool_size = 0
reserve_pool_timeout = 5
query_timeout = 0
ignore_startup_parameters = extra_float_digits  

Now I My App can not have enough connections to work

Answer 1

It seems like you're facing an issue with the number of idle connections in your PgBouncer pool, both from your application user ("userA") and from the PgBouncer user ("pgbouncer"). Here are some steps to troubleshoot and optimize your PgBouncer configuration:

Review pgbouncer.ini Configuration: Check the max_client_conn setting in your pgbouncer.ini. This parameter controls the maximum number of client connections that PgBouncer will accept.

max_client_conn = 100

Make sure pool_mode is set appropriately. For instance, pool_mode = transaction might be suitable for many applications.

pool_mode = transaction

Adjust the default_pool_size and min_pool_size parameters to better match your application's needs.

default_pool_size = 20
min_pool_size = 5