How to Use pgBouncer for PostgreSQL Connection Pooling

How to Use pgBouncer for PostgreSQL Connection Pooling

pgBouncer is a lightweight connection pooler for PostgreSQL that reduces connection overhead and improves performance on your Breeze.

Why Connection Pooling

• PostgreSQL forks a process per connection, consuming significant memory
• Applications may open hundreds of connections under load
• pgBouncer multiplexes many client connections to fewer database connections

Installation

sudo apt install pgbouncer -y

Configuration

Edit /etc/pgbouncer/pgbouncer.ini:

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
default_pool_size = 20
max_client_conn = 200
max_db_connections = 50

Set Up Authentication

Add users to /etc/pgbouncer/userlist.txt:

"myuser" "md5hashofpassword"

Generate the hash from PostgreSQL:

psql -U postgres -c "SELECT concat('\"', usename, '\" \"', passwd, '\"') FROM pg_shadow WHERE usename = 'myuser';"

Pool Modes

• session - connection assigned for entire session (safest)
• transaction - connection returned after each transaction (recommended)
• statement - connection returned after each statement (most aggressive)

Start and Connect

sudo systemctl enable --now pgbouncer
psql -h 127.0.0.1 -p 6432 -U myuser mydb

Your Breeze applications now connect through pgBouncer, dramatically reducing PostgreSQL resource usage.