Postgres Email Lists

pgsql-general

❮

Pgbouncer performance query

Jump to comment-1
KK CHN<kkchn.in@gmail.com>
Jan 22, 2026, 12:29 PM UTC
List,
I am trying pgbouncer for inhouse deployment for PostgreSQL 16.
My setup is PostgreSQL VM : 5444 <=> Pgbouncer VM:5444 <===> Clients
(PostgreSQL with IP 10.12.0.2 and Pgbouncer 10.12.0.35 )
Conducted an inhouse benchmark test (pgbench) performed, I have seen
the following results.
On direct hit the DB server handled tps = 162252.508744 (without initial
connection time)
latency average = 1.233 ms (Total time taken around 2 Minutes to
complete)
Through Pgbouncer it handled tps = 25107.166425 only (
without initial connection time)
latency average = 11.949 ms ( Total time taken around 20 Minutes to
complete )
Could someone shed some light on improving the total time taken by
pgbouncer in this scenario ? How can I improve the total time taken
from 20 Minutes to any reasonably good value, say 5 Minutes is it possible
?
I agree when I have increased the concurrent connections to 300
(pgbench -c 300 ) then Direct hit on DB server fails with Error too many
clients as follows
[root@pgbouncer ~]# pgbench -c 300 -j 2 -t 100000 -h 10.12.0.2 -p 5444
-U recoil -S recoil
Password:
pgbench (16.9, server 16.3.0)
starting vacuum...end.
pgbench: error: connection to server at "10.12.0.2", port 5444 failed:
FATAL: sorry, too many clients already
pgbench: error: could not create connection for client 134
[root@pgbouncer ~]#
I have followed this link for benchmark tests (
https://www.thediscoblog.com/supercharging-postgres-with-pgbouncer)
on Direct hit on DB Server without pgbouncer RESULTS:
[root@pgbouncer ~]# pgbench -c 200 -j 2 -t 100000 -h 10.12.0.2 -p 5444 -U
recoil -S recoil
Password:
pgbench (16.9, server 16.3.0)
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 500
query mode: simple
number of clients: 200
number of threads: 2
maximum number of tries: 1
number of transactions per client: 100000
number of transactions actually processed: 20000000/20000000
number of failed transactions: 0 (0.000%)
latency average = 1.233 ms
initial connection time = 1549.421 ms
tps = 162252.508744 (without initial connection time)
[root@pgbouncer ~]#
Through Pgbouncer to DB Server
[root@pgbouncer ~]# pgbench -c 300 -j 2 -t 100000 -h localhost -p 5444 -U
recoil -S recoil
Password:
pgbench (16.9, server 16.3.0)
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 500
query mode: simple
number of clients: 300
number of threads: 2
maximum number of tries: 1
number of transactions per client: 100000
number of transactions actually processed: 30000000/30000000
number of failed transactions: 0 (0.000%)
latency average = 11.949 ms
initial connection time = 26.699 ms
tps = 25107.166425 (without initial connection time)
[root@pgbouncer ~]# date
Thu Jan 22 22:13:46 IST 2026
[root@pgbouncer ~]#
*But this takes around 20 Minutes to finish. Is this usual behavior ?
```
*
```
my DB VM(RHEL9.4) is 16vCPU, 16GB RAM and
Pgbouncer VM(FreeBSD 14.3) is 8 vCPU and Ram is [root@pgbouncer ~]#
sysctl -h hw.physmem
hw.physmem: 17143681024
[root@pgbouncer ~]#
TOP usage statistics of pgbouncer vm with 200 clients
last pid: 10020; load averages: 1.23, 0.83, 0.59
```
                up 187+22:53:33 22:59:41
```
27 processes: 1 running, 20 sleeping, 6 stopped
CPU: 0.0% user, 0.0% nice, 0.0% system, 0.0% interrupt, 100% idle
Mem: 79M Active, 1063M Inact, 1410M Wired, 1030M Buf, 13G Free
Swap: 7068M Total, 7068M Free
pgbouncer.ini
[root@pgbouncer ~]# grep ^[^\;\;] /usr/local/etc/pgbouncer.ini
[databases]
recoil = host=dbmain.mydomain.in port=5444 dbname=recoil
[users]
[pgbouncer]
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
listen_addr = *
listen_port = 5444
auth_type = md5
auth_file = /usr/local/etc/pgbouncer.users
admin_users = myuser
stats_users = myuser,
poolmode = transaction // *Is this the poolmode I have to use *?
maxpreparedstatements = 100
serverresetquery = DISCARD ALL
serverresetquery_always = 1
ignorestartupparameters = extrafloatdigits, options, statement_timeout,
idleintransactionsessiontimeout
maxclientconn = 5000
defaultpoolsize = 40
minpoolsize = 20
reservepoolsize = 10
reservepooltimeout = 5
maxdbconnections = 900
maxuserconnections = 800
server_lifetime = 3600
serveridletimeout = 60000
[root@pgbouncer ~]#
*Any parameters do I need to adjust for better performance in terms of
latency time improvement, kindly guide me*
Best regards,
Krishane
View in PostgreSQL Archives →
- Jump to comment-1
  Daniel Verite<daniel@manitou-mail.org>
  Jan 23, 2026, 3:04 PM UTC
  KK CHN wrote:
  defaultpoolsize = 40
  That limits the number of connections from pgBouncer to the database
  to 40. That's per user/database, but pgbench connects to the same
  database/same user. So when running pgbench -c 200, without pgBouncer
  there are 200 active connections, whereas through pgBouncer there are
  only 40 active connections in Postgres.
  When queries are issued to pgBouncer and the 40 connections
  are already busy, it makes them wait.
  That alone might explain why the average latencies are so different
  between pgBouncer and direct connections.
  If you really want to support 200 concurrent clients, increase the pool
  size accordingly.
  Best regards,
  --
  Daniel Vérité
  https://postgresql.verite.pro/
  View in PostgreSQL Archives →
- Jump to comment-1
  Adrian Klaver<adrian.klaver@aklaver.com>
  Jan 22, 2026, 4:28 PM UTC
  On 1/22/26 04:35, KK CHN wrote:
  List,
  I am trying pgbouncer for inhouse deployment for PostgreSQL 16.
  My setup is PostgreSQL VM : 5444 <=> Pgbouncer VM:5444 <===> Clients
  (PostgreSQL with IP 10.12.0.2 and Pgbouncer 10.12.0.35 )
  Conducted an inhouse benchmark test (pgbench) performed, I have seen > the following results.
  On direct hit the DB server handled tps = 162252.508744 (without > initial connection time)
  latency average = 1.233 ms (Total time taken around 2 Minutes to > complete)
  Through Pgbouncer it handled tps = 25107.166425 only > ( without initial connection time)
  latency average = 11.949 ms ( Total time taken around 20 Minutes to > complete )
  Could someone shed some light on improving the total time taken by > pgbouncer in this scenario ? How can I improve the total time > taken from 20 Minutes to any reasonably good value, say 5 Minutes is it > possible ?
  The significant difference between the test setups is the introduction of an additional VM between the clients and the database server in the pgBouncer case.
  Have you tried it with pgBouncer installed in the Postgres VM?
  Also see:
  https://www.pgbouncer.org/faq.html#should-pgbouncer-be-installed-on-the-web-server-or-database-server
  --
  Adrian Klaver
  adrian.klaver@aklaver.com
  View in PostgreSQL Archives →
- Jump to comment-1
  Dominique Devienne<ddevienne@gmail.com>
  Jan 22, 2026, 12:35 PM UTC
  On Thu, Jan 22, 2026 at 1:29 PM KK CHN <kkchn.in@gmail.com> wrote:
  I agree when I have increased the concurrent connections to 300 (pgbench -c 300 ) then Direct hit on DB server fails with Error too many clients as follows
  Just increase max_connections then:
  https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-MAX-CONNECTIONS
  Sounds like you should stick to direct PostgeSQL access, if pgBouncer
  makes it 10x slower :). --DD
  View in PostgreSQL Archives →
  Jump to comment-1
  KK CHN<kkchn.in@gmail.com>
  Jan 23, 2026, 4:18 AM UTC
  My query is on the latency average = 11.949 ms (with Pgbouncer ), at
  the same time direct hit on Database server latency average = 1.233 ms
  (Without pgbouncer) Is this an expected usual behaviour when you
  employ pgbouncer ?
  NOTE: Both pgbench tests hit the database server with pgbouncer and
  without pgbouncer performed from the pgbouncer virtual machine tty only
  not from the database server tty. So how does pgbouncer running as a
  separate VM affect the latency part ?
  Or is this due to pgbouncer as a separate VM I was running in front of the
  database server ? Somewhere I have referenced it is better to run
  pgbouncer on a separate instance to avoid the overhead of the pgbouncer
  process on the database server (?)
  Or as Adrian Klaver suggested, the best solution is to run the pgbouncer on
  the same database server.
  What do others suggest ?
  Just increase max_connections then:
  
  https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-MAX-CONNECTIONS
  
  Already max_connections = 500 in the postgresql.conf You suggest to
  increase it to further ( 1000 ?)
  Please find the postgresql.conf important params here ( Any thing to fine
  tune ? )
  listen_addresses = '*' # what IP address(es) to listen on;
  port = 5444 # (change requires restart)
  max_connections = 500 # (change requires restart)
  shared_buffers = 128MB # min 128kB
  dynamicsharedmemory_type = posix # the default is usually the first
  option
  maxwalsize = 1GB
  minwalsize = 80MB
  defaulttextsearchconfig = 'pgcatalog.english'
  sharedpreloadlibraries =
  '$libdir/dbmspipe,$libdir/edbgen,$libdir/dbmsaq,pgstat_statements'
  edb_dynatune = 66 # percentage of server resources
  edbdynatuneprofile = oltp # workload profile for tuning.
  timed_statistics = off # record wait timings, defaults to
  on
  Regards,
  Krishane
  On Thu, Jan 22, 2026 at 6:05 PM Dominique Devienne <ddevienne@gmail.com>
  wrote:
  On Thu, Jan 22, 2026 at 1:29 PM KK CHN <kkchn.in@gmail.com> wrote:
  I agree when I have increased the concurrent connections to 300
  (pgbench -c 300 ) then Direct hit on DB server fails with Error too many
  clients as follows
  
  Just increase max_connections then:
  
  https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-MAX-CONNECTIONS
  
  Sounds like you should stick to direct PostgeSQL access, if pgBouncer
  makes it 10x slower :). --DD
  View in PostgreSQL Archives →