A fintech platform's PostgreSQL cluster started rejecting connections during market open hours. They had 2,000 application pods each trying to maintain 5 persistent connections — 10,000 connections against a PostgreSQL instance that supports 1,000 max. They had never heard of PgBouncer. After deploying PgBouncer in transaction mode, their effective application connection count dropped from 10,000 to 80 actual database connections with zero performance regression.
This guide covers how PgBouncer and ProxySQL work, when to use each, and how to configure them for production scale.
- PgBouncer in transaction mode allows thousands of application connections to share tens of database connections.
- ProxySQL provides query routing, read/write splitting, and connection pooling for MySQL clusters.
- PgBouncer does not support all PostgreSQL features in transaction mode — SET, LISTEN/NOTIFY require session mode.
- Size your pool: (max_connections - superuser_reserved) / average_query_duration_ms * 1000.
Why Connection Pooling Is Critical at Scale
PostgreSQL allocates ~5MB of memory per connection. At 10,000 connections, that is 50GB just for idle connections — before any data is loaded. Connection setup also adds 10-50ms latency for each new connection, compounding under load.
-- Check current PostgreSQL connection usage
SELECT count(*) AS total, state, wait_event_type, wait_event
FROM pg_stat_activity GROUP BY state, wait_event_type, wait_event
ORDER BY total DESC;
-- See connection limit
SHOW max_connections;PgBouncer Configuration
Transaction Mode (Recommended for Most Apps)
# pgbouncer.ini
[databases]
mydb = host=pg-primary port=5432 dbname=mydb
[pgbouncer]
listen_port = 6432
listen_addr = 0.0.0.0
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction # transaction = highest multiplexing
max_client_conn = 10000 # max incoming application connections
default_pool_size = 80 # actual connections to PostgreSQL
min_pool_size = 10 # keep these connections warm
reserve_pool_size = 10 # emergency connections
server_idle_timeout = 600 # close idle server connections after 10 min
client_idle_timeout = 0 # never close idle client connections
log_connections = 0 # disable for performance
log_disconnections = 0Transaction mode does not support SET statements that persist across transactions, LISTEN/NOTIFY, advisory locks, or PREPARE statements. Applications using these features must use session mode or connect directly.
Pool Sizing Formula
# Pool size calculation
# PostgreSQL optimal connections = 2 × CPU cores (for CPU-bound workloads)
# For I/O-bound workloads: CPU cores × 4
# Example: 16 vCPU PostgreSQL instance, I/O bound workload
optimal_pool = 16 * 4 # = 64 connections
# Add 20% buffer for peak load
pool_size = int(optimal_pool * 1.2) # = 77 -> set to 80ProxySQL Configuration for MySQL
Read/Write Splitting
-- Add MySQL backend servers
INSERT INTO mysql_servers(hostgroup_id, hostname, port)
VALUES (1, 'mysql-primary', 3306), -- write group
(2, 'mysql-replica-1', 3306), -- read group
(2, 'mysql-replica-2', 3306);
-- Route writes to primary, reads to replicas
INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup)
VALUES (1, 1, '^SELECT', 2), -- SELECTs to read group
(2, 1, '.*', 1); -- everything else to write group
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;Connection Pool Settings
-- ProxySQL global settings
SET mysql-max_connections = 10000;
SET mysql-default_max_latency_ms = 1000;
SET mysql-connection_max_age_ms = 0; -- 0 = no expiry
SET mysql-max_transaction_time = 14400000;
LOAD MYSQL VARIABLES TO RUNTIME;
-- Monitor pool utilization
SELECT hostgroup, srv_host, srv_port,
ConnUsed, ConnFree, ConnOK, ConnERR, Queries
FROM stats_mysql_connection_pool;Enable ProxySQL's query cache for read-heavy workloads. Identical SELECT queries that return within the TTL window are served from cache, reducing database load by 40-60% in some read-heavy applications.
Monitoring PgBouncer
-- Connect to PgBouncer admin interface
psql -p 6432 -U pgbouncer pgbouncer
SHOW POOLS; -- pool utilization per database/user
SHOW CLIENTS; -- connected application clients
SHOW SERVERS; -- backend PostgreSQL connections
SHOW STATS; -- query throughput and latency- PgBouncer transaction mode multiplexes 10,000+ app connections into 50-100 database connections — deploy it before you need it, not after a connection crisis.
- Pool size should equal 2-4× CPU core count on the PostgreSQL host — not the number of application pods.
- ProxySQL read/write splitting automatically routes SELECT queries to replicas, reducing primary load by 40-70% for read-heavy applications.
- Applications using SET, LISTEN/NOTIFY, or advisory locks require session mode PgBouncer or direct connections.
Working with JusDB on Connection Management
JusDB deploys and tunes PgBouncer and ProxySQL for teams hitting connection limits. We have resolved connection exhaustion incidents that were misdiagnosed as hardware capacity issues, often within hours of engagement.