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.

What is pgbouncer in postgresql?

PgBouncer is a lightweight connection pooler for PostgreSQL. It reduces the overhead of creating new connections by maintaining a pool of connections that can be reused by incoming client requests. This helps improve performance, especially in high-load environments, by managing connection limits and providing efficient use of database resources.

Pgbouncer why?

Using PgBouncer is beneficial because it optimizes the handling of database connections. It reduces the overhead associated with establishing new connections, thus improving application performance and reducing resource consumption. PgBouncer also helps manage the maximum number of connections to the database, preventing overloads and ensuring more reliable application behavior under heavy load.