PostgreSQL Connection Pooling with PgBouncer

PostgreSQL spawns a process for each connection. At a hundred concurrent connections that is manageable. At a few hundred it starts consuming serious RAM and CPU on context switching — and most of those connections are idle most of the time. PgBouncer sits in front of PostgreSQL and maintains a small pool of actual database connections, multiplexing many application connections across them.

---

How pooling modes work

PgBouncer has three modes. The one you pick determines when a server connection is returned to the pool:

Mode	Releases server connection	Use when
Session	When the client disconnects	You need SET, LISTEN, prepared statements
Transaction	After each transaction commits	Stateless apps, most web backends
Statement	After each statement	Rare — autocommit only, no multi-statement transactions

Transaction mode is the right choice for most web applications. It gives the highest connection reuse with acceptable constraints: no session-level state (SET, LISTEN/NOTIFY), and named prepared statements require extra config.

---

Install PgBouncer

sudo apt install pgbouncer    # Debian / Ubuntu
sudo dnf install pgbouncer    # RHEL / Fedora / Rocky

---

Configure PgBouncer

The main config file is /etc/pgbouncer/pgbouncer.ini. A working transaction-mode setup:

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

[pgbouncer]
listen_addr    = 127.0.0.1
listen_port    = 5432

; Authentication
auth_type      = md5
auth_file      = /etc/pgbouncer/userlist.txt

; Pooling
pool_mode      = transaction
max_client_conn = 1000
default_pool_size = 20

; Logging
logfile        = /var/log/postgresql/pgbouncer.log
pidfile        = /var/run/postgresql/pgbouncer.pid

Key settings:

• max_client_conn — maximum total connections PgBouncer will accept from applications
• default_pool_size — maximum actual connections to PostgreSQL per database/user pair
• Keep default_pool_size under PostgreSQL's max_connections (default 100) with headroom for admin sessions

---

Set up the user list

PgBouncer authenticates clients against /etc/pgbouncer/userlist.txt. Get the password hash directly from PostgreSQL:

SELECT concat('md5', md5('secretpassword' || 'appuser'));

Write the result to userlist.txt (one entry per line):

"appuser" "md5a8b56ed42f7c98a1c1e7ccc0f6e1bde2"

The md5 value is md5 + md5 hash of password + username (concatenated, no separator).

Set permissions:

sudo chown postgres:postgres /etc/pgbouncer/userlist.txt
sudo chmod 640 /etc/pgbouncer/userlist.txt

---

Start and enable PgBouncer

sudo systemctl enable --now pgbouncer
sudo systemctl status pgbouncer

---

Connect through the pool

Your application connects to PgBouncer the same way it connects to PostgreSQL — just point it at PgBouncer's address and port. When PgBouncer is on a different port (common when PostgreSQL is also on the same host), use port 6432:

listen_port = 6432

postgresql://appuser:secretpassword@127.0.0.1:6432/mydb

---

Monitor the pool

Connect to PgBouncer's virtual admin console:

psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer

Useful commands inside the console:

SHOW POOLS;     -- pool status, client count, server count per database/user pair
SHOW CLIENTS;   -- connected clients
SHOW SERVERS;   -- backend connections to PostgreSQL
SHOW STATS;     -- request throughput and latency
SHOW CONFIG;    -- current running config

SHOW POOLS output:

 database | user    | cl_active | cl_waiting | sv_active | sv_idle | maxwait
----------+---------+-----------+------------+-----------+---------+---------
 mydb     | appuser |        45 |          0 |        18 |       2 |       0

cl_waiting > 0 means clients are waiting for a connection from the pool — increase default_pool_size or check for long-running transactions holding connections open.

---

Reload config without downtime

sudo systemctl reload pgbouncer

Or from the admin console:

RELOAD;

---

Prepared statements in transaction mode

Named prepared statements are session-scoped in PostgreSQL. In transaction mode, the server connection is returned to the pool after each transaction, so the prepared statement is gone on the next transaction.

Options:

1. Use unnamed prepared statements (extended query protocol, $1 placeholders) — most drivers handle this automatically.
2. Enable server_reset_query = DEALLOCATE ALL — PgBouncer runs this before returning a connection, cleaning up named statements.
3. Switch to session mode if your app depends heavily on PREPARE/EXECUTE.

Most ORMs and connection pool libraries work fine in transaction mode without any changes. Test with your actual driver before assuming a problem.

---

Per-database pool overrides

Different databases can have different pool sizes:

[databases]
hightraffic = host=127.0.0.1 port=5432 dbname=hightraffic pool_size=40
reporting   = host=127.0.0.1 port=5432 dbname=reporting   pool_mode=session pool_size=5

The reporting database uses session mode (for long-running analytical queries) while hightraffic stays on transaction mode with a larger pool.

---

Set the PostgreSQL connection limit to match

PgBouncer's pool is only useful if PostgreSQL can accept the pool connections. Check and raise PostgreSQL's limit in /etc/postgresql/*/main/postgresql.conf:

max_connections = 100

A practical formula: default_pool_size × number of databases × number of PgBouncer instances, plus 5 connections for admin/monitoring headroom. Restart PostgreSQL after changing max_connections:

sudo systemctl restart postgresql

For more on query performance after reducing contention, see How to Read a PostgreSQL EXPLAIN ANALYZE Output. The EXPLAIN Query Analyzer can parse and summarize query plans without manual interpretation.