MIME-Version: 1.0
From: KK CHN <kkchn.in@gmail.com>
Date: Thu, 22 Jan 2026 18:05:12 +0530
Message-ID: <CAKgGyB_fxnmsMq-a4KxH+PQjL6_5m14UvpEb5ejbuiJy7H_iFA@mail.gmail.com>
Subject: Pgbouncer performance query
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000293f600648f9304d"
Archived-At: <https://www.postgresql.org/message-id/CAKgGyB_fxnmsMq-a4KxH%2BPQjL6_5m14UvpEb5ejbuiJy7H_iFA%40mail.gmail.com>
Precedence: bulk

--000000000000293f600648f9304d
Content-Type: text/plain; charset="UTF-8"

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,
pool_mode = transaction        // *Is this the pool_mode * *I have to use *?
max_prepared_statements = 100
server_reset_query = DISCARD ALL
server_reset_query_always = 1
ignore_startup_parameters = extra_float_digits, options, statement_timeout,
idle_in_transaction_session_timeout

max_client_conn = 5000
default_pool_size = 40
min_pool_size =  20
reserve_pool_size = 10
reserve_pool_timeout = 5
max_db_connections = 900
max_user_connections = 800
server_lifetime = 3600
server_idle_timeout = 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

--000000000000293f600648f9304d
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">List,=C2=A0<div><br></div><div>I am trying pgbouncer=C2=A0=
 for inhouse deployment for PostgreSQL 16.</div><div><br></div><div>My setu=
p is=C2=A0 =C2=A0 =C2=A0 PostgreSQL VM : 5444=C2=A0&lt;=3D&gt; Pgbouncer VM=
:5444=C2=A0 &lt;=3D=3D=3D&gt; Clients</div><div>(PostgreSQL with IP 10.12.0=
.2=C2=A0 and=C2=A0 Pgbouncer 10.12.0.35 )</div><div><br></div><div>Conducte=
d an inhouse=C2=A0=C2=A0benchmark=C2=A0 test (pgbench) performed,=C2=A0 =C2=
=A0I have seen the following results.=C2=A0=C2=A0</div><div><br></div><div>=
On direct hit=C2=A0 the DB server=C2=A0 handled tps =3D 162252.508744 (with=
out initial connection time)=C2=A0</div><div>latency average =3D 1.233 ms=
=C2=A0 (Total time taken around=C2=A0 2 Minutes=C2=A0 to complete)=C2=A0<br=
></div><div>=C2=A0=C2=A0</div><div>Through Pgbouncer=C2=A0 it handled=C2=A0=
 tps =3D 25107.166425 only=C2=A0 ( without=C2=A0initial=C2=A0connection tim=
e)</div><div>latency average =3D 11.949 ms=C2=A0 =C2=A0( Total time taken a=
round 20 Minutes to complete )<br></div><div><br></div><div>Could someone s=
hed some light on=C2=A0 =C2=A0improving the total time taken by pgbouncer i=
n this scenario ?=C2=A0 =C2=A0 =C2=A0How=C2=A0can I improve the=C2=A0 total=
 time taken=C2=A0 from 20 Minutes to any reasonably good value, say 5 Minut=
es is it possible=C2=A0 ?</div><div><br></div><div><br></div><div>I agree=
=C2=A0 when I have increased the concurrent connections to 300=C2=A0 =C2=A0=
(pgbench=C2=A0-c 300 ) then=C2=A0 Direct hit on DB server fails with=C2=A0 =
Error too many clients as follows</div><div><br></div><div>[root@pgbouncer =
~]# pgbench -c 300 =C2=A0-j 2 -t 100000 -h =C2=A010.12.0.2 =C2=A0-p 5444 -U=
 recoil -S recoil<br>Password:<br>pgbench (16.9, server 16.3.0)<br>starting=
 vacuum...end.<br>pgbench: error: connection to server at &quot;10.12.0.2&q=
uot;, port 5444 failed: FATAL: =C2=A0sorry, too many clients already<br>pgb=
ench: error: could not create connection for client 134<br>[root@pgbouncer =
~]#</div><div><br></div><div><br></div><div><br></div><div>I have followed =
this link for benchmark tests ( <a href=3D"https://www.thediscoblog.com/sup=
ercharging-postgres-with-pgbouncer">https://www.thediscoblog.com/supercharg=
ing-postgres-with-pgbouncer</a>)</div><div><br></div><div>on Direct hit on =
DB Server without=C2=A0 =C2=A0 pgbouncer=C2=A0 =C2=A0 =C2=A0 =C2=A0RESULTS:=
=C2=A0</div><div><br></div><div>[root@pgbouncer ~]# pgbench -c 200 =C2=A0-j=
 2 -t 100000 -h =C2=A010.12.0.2 -p 5444 -U recoil -S recoil<br>Password:<br=
>pgbench (16.9, server 16.3.0)<br>starting vacuum...end.<br>transaction typ=
e: &lt;builtin: select only&gt;<br>scaling factor: 500<br>query mode: simpl=
e<br>number of clients: 200<br>number of threads: 2<br>maximum number of tr=
ies: 1<br>number of transactions per client: 100000<br>number of transactio=
ns actually processed: 20000000/20000000<br>number of failed transactions: =
0 (0.000%)<br>latency average =3D 1.233 ms<br>initial connection time =3D 1=
549.421 ms<br>tps =3D 162252.508744 (without initial connection time)<br>[r=
oot@pgbouncer ~]#=C2=A0 =C2=A0=C2=A0</div><div><br></div><div><br></div><di=
v><br></div><div><b>Through=C2=A0 Pgbouncer=C2=A0 =C2=A0 to DB Server</b></=
div><div><br></div><div><div><br class=3D"gmail-Apple-interchange-newline">=
[root@pgbouncer ~]# pgbench -c 300 =C2=A0-j 2 -t 100000 -h localhost -p 544=
4 -U recoil -S recoil<br>Password:<br>pgbench (16.9, server 16.3.0)<br>star=
ting vacuum...end.<br>transaction type: &lt;builtin: select only&gt;<br>sca=
ling factor: 500<br>query mode: simple<br>number of clients: 300<br>number =
of threads: 2<br>maximum number of tries: 1<br>number of transactions per c=
lient: 100000<br>number of transactions actually processed: 30000000/300000=
00<br>number of failed transactions: 0 (0.000%)<br>latency average =3D 11.9=
49 ms<br>initial connection time =3D 26.699 ms<br>tps =3D 25107.166425 (wit=
hout initial connection time)<br>[root@pgbouncer ~]# date<br>Thu Jan 22 22:=
13:46 IST 2026<br>[root@pgbouncer ~]#<br></div><div><br></div><div><b>But t=
his takes=C2=A0 around=C2=A0 20 Minutes to finish. Is this=C2=A0 usual beha=
vior ?=C2=A0 =C2=A0=C2=A0</b></div><div><br></div><div>my DB VM(RHEL9.4)=C2=
=A0 is=C2=A0 16vCPU,=C2=A0 16GB RAM=C2=A0 and=C2=A0</div><div><br></div><di=
v>=C2=A0Pgbouncer VM(FreeBSD 14.3)=C2=A0 is=C2=A0 8 vCPU and Ram is [root@p=
gbouncer ~]# sysctl -h hw.physmem</div>hw.physmem: 17143681024<br>[root@pgb=
ouncer ~]#</div><div><br></div><div>TOP usage statistics of pgbouncer vm wi=
th=C2=A0 200=C2=A0 clients=C2=A0 =C2=A0</div><div><br></div><div>last pid: =
10020; =C2=A0load averages: =C2=A01.23, =C2=A00.83, =C2=A00.59 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 up 187+22:53=
:33 22:59:41<br>27 processes: =C2=A01 running, 20 sleeping, 6 stopped<br>CP=
U: =C2=A00.0% user, =C2=A00.0% nice, =C2=A00.0% system, =C2=A00.0% interrup=
t, =C2=A0100% idle<br>Mem: 79M Active, 1063M Inact, 1410M Wired, 1030M Buf,=
 13G Free<br>Swap: 7068M Total, 7068M Free<br></div><div><br></div><div><br=
><div><b>pgbouncer.ini=C2=A0 =C2=A0</b></div><div>[root@pgbouncer ~]# grep =
^[^\;\;] /usr/local/etc/pgbouncer.ini<br>[databases]<br>recoil =3D host=3D<=
a href=3D"http://dbmain.mydomain.in">dbmain.mydomain.in</a> port=3D5444 dbn=
ame=3Drecoil<br>[users]<br>[pgbouncer]<br>logfile =3D /var/log/pgbouncer/pg=
bouncer.log<br>pidfile =3D /var/run/pgbouncer/pgbouncer.pid<br>listen_addr =
=3D *<br>listen_port =3D 5444<br>auth_type =3D md5<br>auth_file =3D /usr/lo=
cal/etc/pgbouncer.users<br>admin_users =3D myuser<br>stats_users =3D myuser=
,<br>pool_mode =3D transaction=C2=A0 =C2=A0 =C2=A0 =C2=A0 // <b>Is this the=
 pool_mode=C2=A0</b> <b>I have to use </b>?<br>max_prepared_statements =3D =
100<br>server_reset_query =3D DISCARD ALL<br>server_reset_query_always =3D =
1<br>ignore_startup_parameters =3D extra_float_digits, options, statement_t=
imeout, idle_in_transaction_session_timeout<br><br>max_client_conn =3D 5000=
<br>default_pool_size =3D 40<br>min_pool_size =3D =C2=A020<br>reserve_pool_=
size =3D 10<br>reserve_pool_timeout =3D 5<br>max_db_connections =3D 900<br>=
max_user_connections =3D 800<br>server_lifetime =3D 3600<br>server_idle_tim=
eout =3D 60000<br>[root@pgbouncer ~]#=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0</di=
v><div><br></div><div>=C2=A0<b>Any=C2=A0 parameters do I need to=C2=A0 adju=
st for better performance in terms of=C2=A0 latency=C2=A0 time improvement,=
=C2=A0 kindly guide me</b></div><div><b><br></b></div><div><b><br></b></div=
><div>Best regards,</div><div>Krishane=C2=A0</div><div><br></div><div><br><=
/div><div><br></div></div><div><br></div></div>

--000000000000293f600648f9304d--