Using PostgreSQL and pgBouncer with a large number of clients

Typically, PostgreSQL can handle hundreds or thousands of concurrent connections when properly configured. However, the optimal number of users for your particular case may require testing and tuning.

In case your database reaches a critical point, there are several strategies you can apply to improve PostgreSQL performance, such as optimizing queries, tuning server resources, horizontal scaling, and using caching.

pgBouncer is a program that helps optimize PostgreSQL performance by reducing the number of server connections. It works as an intermediary between clients and servers, serving connection requests, executing queries, and retrieving results. This reduces the number of connections that directly access PostgreSQL, which can improve performance when there are a large number of clients.


  1. For Windows you can download ready-made pgBouncer binary modules -

  2. For Linux it is installed as standard
    apt install pgbouncer

Configuring userlist.txt:

  1. Create or edit the userlist.txt file in the directory where pgBouncer is installed.

  2. Add a string in the format "username" "password" where you specify the username and password for PostgreSQL, e.g.
    "prnwatch_user" "prnwatch_password"

Configuring pgbouncer.ini:

  1. Create or edit the pgbouncer.ini file in the directory where pgBouncer is installed:
    prnwatch_template = host= port=5432 dbname=PRNWATCH auth_user=prnwatch_user
    listen_port = 6432
    listen_addr = *
    auth_type = md5
    auth_file = d:\PGBouncer\userlist.txt
    logfile = d:\PGBouncer\pgbouncer.log
    pidfile = d:\PGBouncer\
    admin_users = prnwatch_user
    pool_mode = transaction
    default_pool_size = 100
    max_client_conn = 10000

Section [databases]:

Section [pgbouncer]:

Registering pgBouncer as a service:

  1. Windows on the command line as administrator
    pgbouncer --regservice pgbouncer.ini
    After that we start the pgBouncer service.

  2. Linux
    pgbouncer -d pgbouncer.ini

On each O&K Print Watch client, we specify a new database connection via pgBouncer.

Change the database connection string in O&K Print Watch:

  1. Windows
    Provider=MSDASQL;Driver={PostgreSQL Unicode};Server=PGBOUNCER_HOSTNAME;Port=6432;Database=prnwatch_template;UID=prnwatch_user;PWD=prnwatch_password;C8=0
  2. Linux
    host=PGBOUNCER_HOSTNAMEport=6432 dbname=prnwatch_template user=prnwatch_user password=prnwatch_password binary_parameters=yes

When using the database connection via pgBouncer, it is necessary to disable it Use serverside prepare. In the Windows version of O&K Print Watch the parameter C8=0 in the connection string is used for this, in the Linux version binary_parameters=yes.

Restart the O&K Print Watch Service:

  1. Windows on the command line as administrator
    net stop "O&K Print Watch Service" & sc start "O&K Print Watch Service"
  2. Linux
    systemctl restart printwatch