Thread

Pgbouncer performance query

KK CHN <kkchn.in@gmail.com> — 2026-01-22T12:35:12Z

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

Re: Pgbouncer performance query

Dominique Devienne <ddevienne@gmail.com> — 2026-01-22T12:35:30Z

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

Re: Pgbouncer performance query

Adrian Klaver <adrian.klaver@aklaver.com> — 2026-01-22T16:28:02Z

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

Re: Pgbouncer performance query

KK CHN <kkchn.in@gmail.com> — 2026-01-23T04:24:42Z

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
dynamic_shared_memory_type = posix      # the default is usually the first
option

max_wal_size = 1GB
min_wal_size = 80MB

default_text_search_config = 'pg_catalog.english'
shared_preload_libraries =
'$libdir/dbms_pipe,$libdir/edb_gen,$libdir/dbms_aq,pg_stat_statements'

edb_dynatune = 66                       # percentage of server resources

edb_dynatune_profile = 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
>

Re: Pgbouncer performance query

Daniel Verite <daniel@manitou-mail.org> — 2026-01-23T15:04:24Z

	KK CHN wrote:

> default_pool_size = 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/