Connection Pooling Done Right: PgBouncer, ProxySQL and Beyond

Every production database eventually hits the same bottleneck: too many concurrent connections. Whether it's a burst of API traffic, a misbehaving ORM, or a connection leak from a background job, the database spends more time context-switching between connections than executing queries. Connection pooling is the architectural fix that decouples application connection churn from database resource consumption. Done right, it turns a fragile, connection-starved system into a resilient, predictable one. Done wrong, it adds latency, hides errors, and creates new failure modes. This post covers two battle-tested poolers—PgBouncer and ProxySQL—and the principles that apply to any engine.

Why Connection Pooling Matters for Database Performance

Databases allocate memory and OS threads per connection. PostgreSQL, for example, forks a separate process for each connection; MySQL uses one thread per connection. Beyond a few hundred concurrent connections, the overhead of managing these resources dominates actual query execution. Pooling reuses a fixed set of database connections across many application clients, reducing connection setup/teardown overhead and preventing the database from being overwhelmed by transient spikes. The result is lower latency for short queries, higher throughput under load, and a predictable load profile for the database server.

PgBouncer: Lightweight Pooling for PostgreSQL

PgBouncer is a single-purpose, high-performance connection pooler for PostgreSQL. It runs as a separate daemon, listens on a port (typically 6432), and maintains a configurable pool of backend connections. Applications connect to PgBouncer instead of directly to PostgreSQL. It supports three pool modes: session, transaction, and statement. For most OLTP workloads, transaction-level pooling is the sweet spot—connections are returned to the pool after each transaction completes, maximizing reuse.

# pgbouncer.ini — minimal transaction pooling config
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
pool_mode = transaction
default_pool_size = 25
max_client_conn = 200

Key tuning parameters: default_pool_size sets the number of backend connections per database/user pair; max_client_conn limits total incoming connections. Monitor SHOW POOLS to see active vs idle connections. PgBouncer is stateless and can be restarted without losing backend connections if configured with sbuf_loopcnt=0 and server_lifetime set appropriately.

ProxySQL: Advanced Traffic Management for MySQL

ProxySQL is a feature-rich proxy for MySQL (and compatible forks like MariaDB). Unlike PgBouncer's minimalist design, ProxySQL offers query routing, read/write splitting, query caching, and runtime reconfiguration via a built-in admin interface. It uses a multi-tier architecture: a runtime layer that handles traffic, a memory layer for configuration, and a disk layer for persistence. Changes can be applied without restarting the proxy.

-- ProxySQL admin: add a backend server and enable query rules
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (0, '10.0.1.10', 3306);
LOAD MYSQL SERVERS TO RUNTIME;

-- Route SELECT queries to read replicas (hostgroup 1)
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (1, 1, '^SELECT', 1, 1);
LOAD MYSQL QUERY RULES TO RUNTIME;

ProxySQL's connection pooling is session-based by default, but it can be tuned with mysql-max_connections and mysql-connection_pool_size. Its real power is in traffic shaping: you can rewrite queries, block suspicious patterns, and gradually drain servers during maintenance. The trade-off is complexity—ProxySQL has a steeper learning curve and higher resource usage than PgBouncer.

Comparing Pooling Strategies: Transaction vs Session

Transaction-level pooling returns a database connection to the pool after each COMMIT or ROLLBACK. This maximizes connection reuse but requires that the application does not rely on session state (e.g., temporary tables, session variables, prepared statements). Session-level pooling keeps the same database connection for the entire client session. It preserves state but uses more backend connections, making it suitable for applications that use session-scoped features. Statement-level pooling (PgBouncer only) returns the connection after each statement, which is rarely useful outside of autocommit-heavy workloads.

Rule of thumb: Use transaction-level pooling unless your application explicitly depends on session state. If it does, refactor the app to avoid that dependency—it will scale better and be easier to debug.

Common Pitfalls and How to Avoid Them

Three mistakes recur across teams. First: setting pool sizes too large. A pool of 50 connections per database instance is often enough for 500+ concurrent clients; more connections just increase contention. Second: ignoring prepared statement handling. PgBouncer in transaction mode does not support prepared statements across transactions—use pool_mode=session or disable prepared statements in the ORM. Third: not monitoring pool saturation. When all connections are in use, new clients queue up. Without alerts on queue depth, you will see timeouts before you know there is a problem.

For ProxySQL, a common pitfall is misconfiguring query rules that accidentally route writes to read replicas. Always test rules with SELECT * FROM stats_mysql_query_rules and use apply=1 only after verification. For PgBouncer, watch the SHOW STATS output for avg_wait_time—if it rises above 1ms, your pool is undersized or your queries are too slow.

Beyond the Basics: Auto-Scaling and Observability

Static pool sizes work for predictable workloads, but modern infrastructure demands elasticity. For PgBouncer, you can script pool resizing via SET commands in the admin console (port 6432, database pgbouncer). For ProxySQL, the admin interface supports dynamic changes to mysql-servers and mysql-query_rules, enabling integration with auto-scaling groups. Observability is critical: export pool metrics (active connections, queue depth, query latency) to Prometheus or similar. PgBouncer exposes metrics via SHOW STATS and SHOW POOLS; ProxySQL has stats_mysql_connection_pool and stats_mysql_query_digest. Without these, you are flying blind.

Choosing the Right Pooler for Your Workload

If you run PostgreSQL and need a simple, low-overhead pooler, PgBouncer is the default choice. It excels at transaction-level pooling and has minimal CPU/memory footprint. If you run MySQL or need advanced traffic management (read/write splitting, query rewriting, runtime reconfiguration), ProxySQL is the better fit. For mixed environments, consider both: PgBouncer for PostgreSQL, ProxySQL for MySQL. Avoid using application-level pooling (e.g., HikariCP with oversized pools) as a substitute—it cannot protect the database from connection storms the way a dedicated proxy can.

The short version

Connection pooling is not optional for production databases. PgBouncer gives PostgreSQL a lightweight, transaction-level pooler that handles spikes without overhead. ProxySQL provides MySQL with a full-featured proxy for traffic management and observability. Use transaction-level pooling where possible, monitor queue depth and pool utilization, and never set pool sizes larger than necessary. Choose the tool that matches your engine and complexity tolerance—then test it under load before going live.