Connection pooling reuses a limited set of actual database connections across many application requests — reducing the overhead of constantly opening/closing connections and preventing connection exhaustion under load.
Why Connection Pooling Matters
Each database connection has real overhead (memory, process/thread allocation) — an application opening a new connection per request, especially at scale, wastes resources and can exhaust the database's maximum connection limit; pooling maintains a smaller set of reused connections instead.
PgBouncer for PostgreSQL
Step 1 — Install PgBouncer
sudo apt install pgbouncer -yStep 2 — Configure PgBouncer
sudo nano /etc/pgbouncer/pgbouncer.ini[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb
[pgbouncer]
listen_port = 6432
listen_addr = 127.0.0.1
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25Step 3 — Configure User Authentication
sudo nano /etc/pgbouncer/userlist.txt"dbuser" "SCRAM-SHA-256$..."Step 4 — Start PgBouncer
sudo systemctl enable --now pgbouncerStep 5 — Point Your Application at PgBouncer
DATABASE_URL=postgresql://dbuser:password@localhost:6432/mydbYour application connects to PgBouncer's port (6432) instead of PostgreSQL directly (5432); PgBouncer manages the actual pooled connections to PostgreSQL behind the scenes.
Understanding Pool Modes
| Mode | Behavior |
|---|---|
| session | Connection assigned for the client's entire session — least aggressive pooling |
| transaction | Connection assigned per transaction, released after — good balance for most applications |
| statement | Connection assigned per statement — most aggressive, but breaks multi-statement transactions |
transaction mode is the most common choice for typical web applications, balancing efficiency with compatibility.
ProxySQL for MySQL/MariaDB
sudo apt install proxysql -yProxySQL provides similar connection pooling for MySQL/MariaDB, along with additional features like query routing and caching — configuration is more involved than PgBouncer, done through ProxySQL's own admin interface (accessible via a MySQL client connection to its admin port).
Basic ProxySQL Configuration
mysql -h 127.0.0.1 -P 6032 -u admin -padminINSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (0, '127.0.0.1', 3306);
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;Sizing Your Pool
The pool size should reflect what your database can actually handle efficiently, not simply match your application's peak concurrent request count — a well-tuned pool often uses far fewer actual database connections than the application's total concurrent capacity, since pooling's whole point is efficient reuse.
Monitoring Pool Effectiveness
SHOW POOLS;PgBouncer's admin console (accessible via a special connection) shows pool statistics — monitor for connection wait times or pool exhaustion indicating your pool size needs adjustment.
Common Errors
Application errors about too many connections despite pooling — verify the application is actually configured to connect to the pooler's port, not bypassing it and connecting directly to the database.
Continue Reading
- How to Optimize PostgreSQL Performance for a VPS
- How to Tune MySQL/MariaDB Performance for a VPS
- How to Monitor Database Performance and Slow Queries
Browse more articles in Databases.
