I am testing pbBouncer by utilising pgBench with a very large database.

I initially setup pgBench with a large database (731GB) - turns outs its just a huge accounts table (626GB)

./pgbench -h -U pgbench -p 5432 -i -s 50000

I have configured my pgBouncer as the following:

pgbench = dbname=pgbench host= port=5432

;; Configuration section

logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid

;; IP address or * which means all IPs
listen_addr = *
listen_port = 6432

;; any, trust, plain, md5, cert, hba, pam
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

;; comma-separated list of users who are allowed to change settings
admin_users = postgres

;; comma-separated list of users who are just allowed to use SHOW command
stats_users = stats, postgres

;; When server connection is released back to pool:
;;   session      - after client disconnects (default)
;;   transaction  - after transaction finishes
;;   statement    - after statement finishes
;pool_mode = session

;; Total number of clients that can connect
max_client_conn = 1000

;; Default pool size.  20 is good number when transaction pooling
;; is in use, in session pooling it needs to be the number of
;; max clients you want to handle at any moment
default_pool_size = 25

; how many additional connection to allow in case of trouble
reserve_pool_size = 5

My max_connections in postgres is configured at 1000, can I reduce this value if I go via pgBouncer?

I was then running some pgBench test scenarios, going through pgbouncer and going direct to postgres.

When running the below via pgBouncer

./pgbench -c 1000 -j 4 -t 1000 pgbench -h -p 6432 -U pgbench

I received alot of query_wait_timeout errors.

client XXX aborted in state 4: ERROR:  query_wait_timeout

I understand the default is 120 seconds but surely this would be enough?

Why cant pgBouncer handle the amount of connections?

I have 5 processors and 40GB of RAM with shared_buffers at 12GB

Is there any recommendations as to why I would be getting this? Would it be a matter of extending the query_wait_timeout

Any help is much appreciated.

1 Answer 1


You are only allowed to have 25+5 = 30 connections to the real database at once. You are trying to have 1000 connections at once. The rest have to wait a while. They aren't willing to wait long enough.

It isn't clear what you expect to happen here. Do you think the sessions will be shared, even though you are in session-pooling mode and not one of the finer pooling modes? Do you think one session will complete its 1000 transactions and turn its connection over to someone else more quickly than it actually will?

  • I was of the understanding with pgBouncer I was able to increase the amount of connections to the database as it acts as a pooler. I currently have max_connections set at 1000 in my postgresql.conf but utilising pgBouncer thought allowed me to make use more connections?
    – rdbmsNoob
    Jul 26, 2021 at 7:05
  • pgBouncer is implementing the configuration you told it to. If you want it to do something else, change the configuration to do something else. It is not about what pgbouncer is capable of, it is about the configuration decisions you made. If you want to use a different pooling mode, use a different pooling mode. If you don't want them to time out after waiting 120 seconds, change the timeout.
    – jjanes
    Jul 26, 2021 at 18:36

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.