DEV Community

Cover image for Configuring PgBouncer Connection Pooling for PostgreSQL
Raizan
Raizan

Posted on Originally published at chasebot.online

Configuring PgBouncer Connection Pooling for PostgreSQL

#postgres #tutorial #database #devops

What You'll Need

  • A clean server instance running Ubuntu 22.04 LTS on a Hetzner VPS or Contabo VPS (or DigitalOcean as an alternative)
  • Namecheap if domain needed for SSL/TLS database hostnames
  • PostgreSQL 14 or higher installed on your target environment
  • Sudo or root privileges on the server terminal
  • n8n Cloud or self-hosted n8n if connecting external workflow automation tooling
  • Make.com only for comparisons regarding high-frequency database connection polling overhead

Table of Contents

Understanding PgBouncer and Connection Pooling

PostgreSQL relies on a process-based architecture. Whenever a client connects to the database, PostgreSQL forks a brand new backend process to handle that specific session. While this guarantees complete memory isolation and stability between client sessions, it introduces substantial overhead. Each forked process consumes somewhere between 2MB to 10MB of RAM immediately upon spawn, without having executed a single query yet.

When you run microservices, web application clusters, or high-throughput workflow engines, your total concurrent client connections can spike into the thousands. Opening and closing raw PostgreSQL connections creates massive CPU spikes due to repetitive connection handshakes, authentication checks, and process creation. If your backend infrastructure exhausts available physical memory under high connection loads, you should review our detailed guide on Configuring Swap Space on Ubuntu Linux VPS to prevent operating system kernel panics and out-of-memory crashes.

PgBouncer is a lightweight connection pooler designed to run in front of PostgreSQL. It intercepts incoming client connections and multiplexes them across a much smaller, static pool of persistent database backend connections. Instead of managing thousands of backend processes, PostgreSQL only maintains a lean set of active processes, keeping memory usage constant while throughput scales exponentially.

PgBouncer operates in three distinct pooling modes:

  1. Session Pooling: PgBouncer assigns a backend connection to the client when it logs in and holds it active until the client explicitly disconnects. This is the safest mode, but it offers the least memory savings for long-lived idle connections.
  2. Transaction Pooling: PgBouncer assigns a backend connection to the client only for the duration of a transaction. Once the transaction completes (after COMMIT or ROLLBACK), the connection returns to the pool. This mode delivers maximum performance and memory efficiency for modern web applications.
  3. Statement Pooling: PgBouncer assigns a backend connection for a single SQL query. Transactions containing multi-statement blocks are prohibited in this mode.

For almost all production web frameworks, transaction pooling provides the optimal balance between application compatibility and infrastructure scaling.

Step 1: Installing PgBouncer and PostgreSQL

Let us log into our server terminal to install the necessary packages using the official apt package manager. If you are setting up fresh server infrastructure for your databases, you can check out our walk-through on Deploying Open Source Workflow Systems On Hetzner to plan your virtual instance specifications. We will deploy our setup on a flexible Hetzner VPS instance.

Execute the following commands in your shell to update package repositories and install PostgreSQL, additional database utilities, and PgBouncer:

sudo apt update
sudo apt install -y postgresql postgresql-contrib pgbouncer
Enter fullscreen mode Exit fullscreen mode

Verify that both services are running using systemctl commands:

sudo systemctl status postgresql
sudo systemctl status pgbouncer
Enter fullscreen mode Exit fullscreen mode

Before configuring the pooler, let us create a dedicated production database and database user inside PostgreSQL. Switch to the default postgres system account and launch the interactive psql terminal:

sudo -u postgres psql
Enter fullscreen mode Exit fullscreen mode

Run these SQL statements to create a target database called production_db and an application user named app_user with a secure password:

CREATE DATABASE production_db;
CREATE USER app_user WITH ENCRYPTED PASSWORD 'SuperSecurePassword123!';
GRANT ALL PRIVILEGES ON DATABASE production_db TO app_user;
\c production_db
GRANT ALL ON SCHEMA public TO app_user;
\q
Enter fullscreen mode Exit fullscreen mode

Now that the database and user are present inside PostgreSQL, we can construct the connection pooling configuration.

💡 Fast-Track Your Project: Don't want to configure this yourself? I build custom n8n pipelines and bots. Message me with code SYS3-DEVTO.

Step 2: Configuring pgbouncer.ini and userlist.txt

PgBouncer uses two primary configuration files: pgbouncer.ini for runtime settings and pool limits, and userlist.txt for client authentication credentials.

First, stop the default PgBouncer service so we can apply our configurations safely:

sudo systemctl stop pgbouncer
Enter fullscreen mode Exit fullscreen mode

Now, replace the default configuration file located at /etc/pgbouncer/pgbouncer.ini with our optimized configuration. Open the file in your preferred text editor:

sudo nano /etc/pgbouncer/pgbouncer.ini
Enter fullscreen mode Exit fullscreen mode

Write the complete configuration detailed below:

[databases]
production_db = host=127.0.0.1 port=5432 dbname=production_db user=app_user password=SuperSecurePassword123!
* = host=127.0.0.1 port=5432

[pgbouncer]
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
admin_users = postgres, admin
stats_users = stats, postgres

pool_mode = transaction
server_reset_query = DISCARD ALL
max_client_conn = 5000
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 5
max_db_connections = 50
max_user_connections = 50

server_idle_timeout = 600
server_connect_timeout = 15
server_login_retry = 15
query_timeout = 0
client_idle_timeout = 0
client_login_timeout = 60

ignore_startup_parameters = extra_float_digits, application_name
Enter fullscreen mode Exit fullscreen mode

Save and close the file.

Next, we must generate the user authentication credentials file located at /etc/pgbouncer/userlist.txt. PgBouncer needs this file to authenticate incoming database client requests without querying PostgreSQL for every plain request.

Open /etc/pgbouncer/userlist.txt:

sudo nano /etc/pgbouncer/userlist.txt
Enter fullscreen mode Exit fullscreen mode

Insert the full user list and matching credentials. You can use raw text passwords enclosed in quotes or standard PostgreSQL password hashes:

"app_user" "SuperSecurePassword123!"
"postgres" "SuperSecurePassword123!"
"admin" "SuperSecurePassword123!"
Enter fullscreen mode Exit fullscreen mode

Save the file and set strict file permission attributes so that unprivileged Linux users cannot read raw credentials off the disk:

sudo chown -R postgres:postgres /etc/pgbouncer
sudo chmod 0600 /etc/pgbouncer/userlist.txt
sudo chmod 0640 /etc/pgbouncer/pgbouncer.ini
Enter fullscreen mode Exit fullscreen mode

If you manage web frontends or internal database consoles on your server, such as when Deploying Appsmith on Budget Hetzner Cloud VPS, you will configure your dashboard datasources to target host port 6432 instead of port 5432. This routes all GUI data queries cleanly through PgBouncer connection pools.

Step 3: Tuning PostgreSQL and Systemd Service

Because PgBouncer handles the thousands of incoming idle connections from clients, PostgreSQL no longer needs an unnecessarily large max_connections allocation. Restricting PostgreSQL to a modest pool of connections conserves system RAM and prevents CPU cache contention on the host system.

Open your main PostgreSQL configuration file:

sudo nano /etc/postgresql/14/main/postgresql.conf
Enter fullscreen mode Exit fullscreen mode

Find the networking and resource configuration keys and adjust them to match these parameters:

listen_addresses = '127.0.0.1'
max_connections = 100
shared_buffers = 1GB
work_mem = 16MB
maintenance_work_mem = 256MB
Enter fullscreen mode Exit fullscreen mode

Save the file. Next, grant local access permissions inside PostgreSQL by updating /etc/postgresql/14/main/pg_hba.conf so PgBouncer can connect over loopback TCP:

sudo nano /etc/postgresql/14/main/pg_hba.conf
Enter fullscreen mode Exit fullscreen mode

Ensure the host TCP rules allow password authentication for host connections on loopback addresses:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     peer
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256
Enter fullscreen mode Exit fullscreen mode

Save and exit. Restart PostgreSQL to apply the parameter changes:

sudo systemctl restart postgresql
Enter fullscreen mode Exit fullscreen mode

Now, tune the Linux OS system limits for PgBouncer so the daemon does not run out of available file descriptors when thousands of simultaneous client sockets connect to port 6432.

Create an override directory for the PgBouncer systemd service:

sudo mkdir -p /etc/systemd/system/pgbouncer.service.d
sudo nano /etc/systemd/system/pgbouncer.service.d/override.conf
Enter fullscreen mode Exit fullscreen mode

Add these system limits to ensure high concurrent file handling capacity:

[Service]
LimitNOFILE=65536
Enter fullscreen mode Exit fullscreen mode

Reload systemd daemon files and start PgBouncer:

sudo systemctl daemon-reload
sudo systemctl enable pgbouncer
sudo systemctl start pgbouncer
Enter fullscreen mode Exit fullscreen mode

Check the health status of PgBouncer to confirm it is active and listening on port 6432:

sudo systemctl status pgbouncer
Enter fullscreen mode Exit fullscreen mode

Step 4: Benchmarking Performance with Pgbench

To measure the efficiency gains of connection pooling, we can run a benchmark using pgbench, the standard PostgreSQL benchmarking utility.

First, initialize a benchmark database schema directly on PostgreSQL port 5432:

sudo -u postgres pgbench -i -s 20 production_db
Enter fullscreen mode Exit fullscreen mode

Now, run a stress test sending 150 concurrent client connections directly against the unpooled PostgreSQL port 5432:

pgbench -c 150 -j 8 -T 30 -h 127.0.0.1 -p 5432 -U app_user production_db
Enter fullscreen mode Exit fullscreen mode

You will observe higher connection latency, potential connection rejected errors, and noticeable system CPU stress as PostgreSQL struggles to fork individual backend processes for all 150 incoming clients.

Next, run the exact same load test pointed directly at PgBouncer on port 6432:

pgbench -c 150 -j 8 -T 30 -h 127.0.0.1 -p 6432 -U app_user production_db
Enter fullscreen mode Exit fullscreen mode

The output will display a significant decrease in latency along with a substantial bump in total Transactions Per Second (TPS).

starting benchmark...
transaction type: <builtin: TCB-like>
scaling factor: 20
query mode: simple
number of clients: 150
number of threads: 8
duration: 30 s
number of transactions actually processed: 142850
latency average = 31.421 ms
tps = 4761.666667 (including connections establishing)
tps = 4812.333333 (excluding connections establishing)
Enter fullscreen mode Exit fullscreen mode

You can inspect live connection metrics and pool statistics inside PgBouncer by connecting to the special administrative database named pgbouncer using your administrative database account:

psql -h 127.0.0.1 -p 6432 -U postgres pgbouncer
Enter fullscreen mode Exit fullscreen mode

Run administrative commands inside the pgbouncer console:

SHOW POOLS;
SHOW STATS;
SHOW CLIENTS;
SHOW SERVERS;
Enter fullscreen mode Exit fullscreen mode

To exit the administrative pooler console, type:

\q
Enter fullscreen mode Exit fullscreen mode

Your PostgreSQL instance is now protected by PgBouncer transaction pooling, fully equipped to sustain thousands of concurrent connection bursts without degrading database server performance.

Getting Started

Implementing connection pooling is one of the most cost-effective performance upgrades you can make to a database architecture. By running PgBouncer on a performant host like a Hetzner VPS or Contabo VPS, you eliminate unnecessary connection overhead and unlock predictable database response times.

Outsource Your Automation

Don't have time? I build production n8n workflows, WhatsApp bots, and fully automated YouTube Shorts pipelines. Hire me on Fiverr, mention SYS3-DEVTO for priority. Or DM at chasebot.online.

Originally published on Automation Insider.

Top comments (0)