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.

Installation:

For Windows you can download ready-made pgBouncer binary modules - https://github.com/pgbouncer/pgbouncer/releases

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

Configuring userlist.txt:

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

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:

Create or edit the pgbouncer.ini file in the directory where pgBouncer is installed:

[databases]
prnwatch_template = host=127.0.0.1 port=5432 dbname=PRNWATCH auth_user=prnwatch_user

[pgbouncer]
listen_port = 6432
listen_addr = *
auth_type = md5
auth_file = d:\PGBouncer\userlist.txt
logfile = d:\PGBouncer\pgbouncer.log
pidfile = d:\PGBouncer\pgbouncer.pid
admin_users = prnwatch_user
pool_mode = transaction
default_pool_size = 100
max_client_conn = 10000

Section [databases]:

prnwatch_template - template name

host - PostgreSQL host address

port - PostgreSQL port

auth_user - user name from userlist.txt

Section [pgbouncer]:

listen_port - port on which pgBouncer accepts connections

listen_addr - address on which pgBouncer is listening. To connect clients remotely, you must use *

auth_type - authentication type. md5 - you can use passwords in userlist.txt both in plain text and md5. For example:
```
"prnwatch_user" "md5145c741eaed0b9c05ae8444b74d987f0"
```
auth_file - Full path to userlist.txt

logfile - Full path where the log file will be stored

pidfile - Full path where the pid file will be stored

admin_users -The name of the user from userlist.txt who is allowed administrative access to pgBouncer. A separate user can be created for this purpose.

pool_mode -Connection pool model, transaction must be used

max_client_conn - Maximum number of client connections

default_pool_size - Connection pool size. Default is 20, can be increased depending on the number of clients and PostgreSQL settings

Registering pgBouncer as a service:

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

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:

Windows

Provider=MSDASQL;Driver={PostgreSQL Unicode};Server=PGBOUNCER_HOSTNAME;Port=6432;Database=prnwatch_template;UID=prnwatch_user;PWD=prnwatch_password;C8=0

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:

Windows on the command line as administrator

net stop "O&K Print Watch Service" & sc start "O&K Print Watch Service"

Linux
```
systemctl restart printwatch
```