PgBouncer Enhances PostgreSQL Performance in High-Traffic Environments

Managing database connections effectively is crucial for maintaining the performance and reliability of your PostgreSQL instances. PostgreSQL’s performance can degrade with an excessive number of concurrent connections, making connection pooling solutions like PgBouncer essential for high-traffic environments. In this article, we’ll walk through the steps to set up PgBouncer, a lightweight connection pooler for PostgreSQL, and configure it to optimize your database connections.

Why Use PgBouncer?

PostgreSQL can handle a limited number of concurrent connections efficiently. It is recommended to set the maximum connections lower than 10 per CPU core, with a maximum of 20 per CPU core as the limit. If your application needs to handle more user sessions, using a connection pooler like PgBouncer becomes necessary. PgBouncer reduces the overhead of establishing and closing connections by reusing a pool of established connections.

Prerequisites

Before we start, ensure you have the following dependencies installed on your system:

• PostgreSQL
• Python3 with psycopg2

You can install these dependencies using the following commands:

dnf install python3-psycopg2-2.9.9-1PGDG.rhel9.x86_64.rpm # Dependency
dnf install pgbouncer-1.22.1-42PGDG.rhel9.x86_64.rpm

Setting Up PgBouncer

1. Configure User Authentication

First, we’ll create a userlist file for PgBouncer to manage user authentication.

psql -Atq -h 10.*.**.** -p 5432 -U postgres -d postgres -c "SELECT concat('"', usename, '" "', passwd, '"') FROM pg_shadow" >> /etc/pgbouncer/userlist.txt

2. Edit the PgBouncer Configuration

Next, we’ll configure the pgbouncer.ini file. Open the file in your preferred text editor:

vi /etc/pgbouncer/pgbouncer.ini

Add the following configuration:

[databases]
* = port=5432

[pgbouncer]
pool_mode = session
listen_addr = 10.5.**.**
default_pool_size = 100
reserve_pool_size = 20
max_client_conn = 500
admin_users = postgres
ignore_startup_parameters = extra_float_digits
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
listen_port = 6432
auth_file = /etc/pgbouncer/userlist.txt
client_tls_sslmode = require
client_tls_ca_file = /etc/pgbouncer/root.crt
client_tls_key_file = /etc/pgbouncer/pgbouncer.key
client_tls_cert_file = /etc/pgbouncer/pgbouncer.crt

3. Restart and Enable PgBouncer

After editing the configuration, reload and restart the PgBouncer service to apply the changes:

systemctl restart pgbouncer
systemctl enable pgbouncer

You can check the status of the PgBouncer service to ensure it is running correctly:

systemctl status pgbouncer

4. Connect to PgBouncer

Now, you can perform your database operations through the PgBouncer port:

psql -p 6432

To connect to the PgBouncer manager interface:

psql -p 6432 pgbouncer

5. Monitoring and Managing PgBouncer

To monitor pool management:

show pools;

To review statistics:

show stats;

6. Update System Limits

Finally, update the systemd service file to increase the file descriptor limits. To ensure that PgBouncer can handle a large number of concurrent connections, you need to increase the file descriptor limits. File descriptors are integral resources used by the operating system to manage open files and sockets. Each connection to the database through PgBouncer consumes a file descriptor. If the limit is too low, PgBouncer will not be able to accept additional connections.

Update: 05 Jul 2024