Connection Pooling: HikariCP, pgBouncer, and ProxySQL Explained

Every database query needs a connection. Opening a new connection takes time — TCP handshake, authentication, session initialization — often 20-50ms or more. Connection pooling solves this by keeping connections open and reusing them.

Why Connection Pooling Matters

Without pooling, every request opens a new connection and closes it when done. Under load, you spend more time opening and closing connections than running queries.

Request 1: Open connection (30ms) → Query (5ms) → Close connection (5ms) = 40ms
Request 2: Open connection (30ms) → Query (5ms) → Close connection (5ms) = 40ms
Request 3: Open connection (30ms) → Query (5ms) → Close connection (5ms) = 40ms

With pooling, requests reuse existing connections:

Request 1: Get connection (0.1ms) → Query (5ms) → Return connection (0.1ms) = 5.2ms
Request 2: Get connection (0.1ms) → Query (5ms) → Return connection (0.1ms) = 5.2ms
Request 3: Get connection (0.1ms) → Query (5ms) → Return connection (0.1ms) = 5.2ms

The connection is already open. You save the connection setup time on every request.

How Connections Flow Through a Pool

flowchart TD
    subgraph App["Application"]
    T1["Request 1"]
    T2["Request 2"]
    T3["Request N"]
    end
    subgraph Pool["Connection Pool (HikariCP)"]
    PQ["Pool Queue"]
    C1["Connection 1"]
    C2["Connection 2"]
    C3["Connection 3"]
    end
    subgraph Proxy["pgBouncer (optional)"]
    PB["Transaction Pool"]
    end
    subgraph DB["PostgreSQL"]
    P1["Backend 1"]
    P2["Backend 2"]
    P3["Backend N"]
    end
    T1 & T2 & T3 --> PQ
    PQ --> C1 & C2 & C3
    C1 & C2 & C3 --> PB
    PB --> P1 & P2 & P3

Applications acquire a connection from the pool, run their query, then return it. With pgBouncer in transaction mode, connections are only borrowed for the duration of each transaction — this is how a single pool serves hundreds of application instances without exhausting max_connections.

Connection Pool Sizing

Pool size is a balance: too small wastes throughput, too large wastes resources and can overwhelm the database.

The Formula

A common starting point is:

pool_size = (number_of_cores * 2) + effective_spindle_count

This is from the PostgreSQL documentation. For SSDs (no spindle contention), it’s roughly:

pool_size = (number_of_cores * 2) + 0 = 2 * cores

Factors That Affect Pool Size

Pool size depends on several factors. CPU-bound queries need smaller pools while I/O-bound queries can use larger pools. More clients need larger pools. Each connection uses memory, so the pool can’t exceed what the database can handle. High network latency favors larger pools to keep pipes full.

Real-World Example

For a 4-core database server with an SSD:

pool_size = (4 * 2) + 0 = 8 connections

But if you have 100 concurrent clients, you’ll need to queue requests or increase the pool, accepting some contention.

HikariCP Configuration

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://db-server:5432/mydb");
config.setUsername("user");
config.setPassword("password");
config.setMaximumPoolSize(10);
config.setMinimumIdle(2);
config.setConnectionTimeout(30000);  // 30 seconds
config.setIdleTimeout(600000);       // 10 minutes
config.setMaxLifetime(1800000);      // 30 minutes
config.setPoolName("myapp-pool");

HikariDataSource ds = new HikariDataSource(config);

Key HikariCP Settings

• maximumPoolSize — maximum connections in the pool. Set based on the formula above.
• minimumIdle — minimum connections to keep idle. Set lower than maximumPoolSize for variable load.
• connectionTimeout — how long to wait for a connection before throwing an exception.
• idleTimeout — how long to keep an idle connection before closing it.
• maxLifetime — maximum lifetime of a connection, regardless of idle.

HikariCP Deep Dive

HikariCP is the de facto standard for Java connection pooling. It’s known for minimal overhead and fast performance.

Why HikariCP Is Fast

HikariCP uses several techniques. It instruments bytecode via Javassist to generate optimized connection handling. It minimizes network frames to keep them small. It aggressively prunes dead connections. And it uses concurrent skip lists for lock-free data structures.

Recommended HikariCP Settings

config.setMaximumPoolSize(10);
config.setMinimumIdle(5);
config.setConnectionTimeout(30000);
config.setIdleTimeout(600000);
config.setMaxLifetime(1800000);
config.setLeakDetectionThreshold(60000);  // Detect leaks after 60 seconds

Monitoring HikariCP

// Get pool metrics
HikariPoolMXBean pool = ds.getHikariPoolMXBean();
int activeConnections = pool.getActiveConnections();
int idleConnections = pool.getIdleConnections();
int totalConnections = pool.getTotalConnections();
int threadsAwaitingConnection = pool.getThreadsAwaitingConnection();

pgBouncer

pgBouncer is a connection pooler for PostgreSQL. Unlike application-level pools, pgBouncer sits between the application and the database as a proxy.

Why pgBouncer?

pgBouncer provides database-level pooling as a single pool for all applications connecting to a database. It supports transaction pooling mode where connections are only held during transactions, not sessions. It caches authentication to reduce overhead. And it’s lightweight, written in C with minimal resource usage.

Installation

# Ubuntu/Debian
apt-get install pgbouncer

# Or from source
./configure --prefix=/usr/local && make && make install

Basic Configuration

[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  # or session
max_client_conn = 100
default_pool_size = 20

Pool Modes

pgBouncer supports three pool modes:

# Session mode: connection is returned to pool when client disconnects
pool_mode = session

# Transaction mode: connection is returned after transaction commits
# This allows more clients than connections
pool_mode = transaction

# Statement mode: connection is returned after each statement
# Not compatible with prepared statements or multi-statement transactions
pool_mode = statement

Transaction mode is the most efficient but has limitations:

• No session-level features (SET LOCAL, prepared statements across transactions)
• Transactions must be explicit (no BEGIN blocks for multi-statement)

PgBouncer with HikariCP

Use both together for layered pooling:

Application (HikariCP pool=10) → pgBouncer (pool=20) → PostgreSQL (max_connections=100)

pgBouncer handles connections to the database; HikariCP handles connections to pgBouncer.

ProxySQL

ProxySQL is a more sophisticated proxy that handles both MySQL and PostgreSQL with advanced features.

Why ProxySQL?

ProxySQL supports MySQL, PostgreSQL, and MariaDB. It can route queries to replicas or primaries based on query type. It has built-in query caching and throttling. It also supports traffic mirroring for test systems.

ProxySQL Configuration

-- Add MySQL servers
INSERT INTO mysql_servers (hostname, port, weight, comment) VALUES ('db-primary', 3306, 100, 'Primary');
INSERT INTO mysql_servers (hostname, port, weight, comment) VALUES ('db-replica', 3306, 100, 'Replica');

-- Create monitoring user on MySQL
CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor_password';
GRANT REPLICATION CLIENT ON *.* TO 'monitor'@'%';

-- Configure user
INSERT INTO mysql_users (username, password, active, default_hostgroup) VALUES ('app_user', 'app_password', 1, 0);

-- Load configuration
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL USERS TO RUNTIME;

Query Routing with ProxySQL

-- Route reads to replica, writes to primary
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (1, 1, '^SELECT.*', 1, 1);  -- Reads go to hostgroup 1 (replicas)

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (2, 1, '^INSERT|^UPDATE|^DELETE', 0, 1);  -- Writes go to hostgroup 0 (primary)

Connection Timeout Settings

Poor timeout configuration causes problems. Set them deliberately.

Application Timeouts

// HikariCP
config.setConnectionTimeout(30000);  // Wait 30s for connection
config.setValidationTimeout(5000);  // 5s to validate connection

Database-Level Timeouts

-- PostgreSQL: statement timeout (8 seconds)
SET statement_timeout = '8s';

-- MySQL: wait timeout
SET GLOBAL wait_timeout = 28800;

Load Balancer Timeouts

# HAProxy
timeout client          30s
timeout server          30s
timeout connect          5s
timeout queue           60s

Idle Connection Management

Idle connections waste resources. Configure pools to close unused connections.

HikariCP Idle Timeout

config.setMinimumIdle(2);     // Keep at least 2 idle
config.setIdleTimeout(600000); // Close idle after 10 minutes

PgBouncer Idle Management

server_idle_timeout = 600  # seconds

When to Keep Connections Alive

Keep connections alive for high-frequency queries (keep warm), when connection setup is expensive (SSL, authentication), and when the database is on a high-latency network.

When to Close Idle Connections

Close idle connections when many databases share a server, when each connection consumes significant memory, and when queries are infrequent.

When Connection Pooling Helps

Pooling helps when request volume is high (many short requests benefit most from connection reuse), when authentication is expensive (connection setup takes 20ms+), when applications are latency-sensitive, and when the database max_connections is low.

When Connection Pooling Hurts

Pooling can hurt when queries run for minutes (long-lived connections see little benefit), when prepared statements are used across connections, when applications rely on session state (SET variables, temp tables) since transaction pooling breaks this, and when pools are oversized (too many connections cause context switching and memory pressure).

Transaction Pooling Gotcha

With pgBouncer in transaction mode:

-- This breaks: SET LOCAL only lasts for the transaction
BEGIN;
SET LOCAL app.setting = 'value';  -- Connection returned to pool after COMMIT, setting lost
COMMIT;

-- This also breaks: prepared statements are connection-specific
PREPARE myplan AS SELECT * FROM orders WHERE id = $1;
-- Different connection after COMMIT, prepared statement gone

Connection Pool Comparison

Feature	HikariCP	pgBouncer	ProxySQL
Layer	Application-level	Database proxy	Database proxy
Language	Java	C	C++
Transaction pooling	N/A (app-level)	Yes	Yes
Query routing	No	No	Yes (read/write split)
Connection multiplexing	No	Yes	Yes
MySQL support	Yes	No	Yes
PostgreSQL support	Yes	Yes	Yes
Set up complexity	Low	Medium	High
Memory footprint	Per-app	Single process	Single process
Best for	Java apps, per-instance pooling	PostgreSQL at scale	Multi-database routing, read replicas

Monitoring Connection Pools

HikariCP Metrics

// Micrometer metrics (Spring Boot)
hikaripool.mysql = { ... }
metrics:
  - hikaricp.connections.active
  - hikaricp.connections.idle
  - hikaricp.connections.pending
  - hikaricp.connections.max
  - hikaricp.connections.min

pgBouncer Monitoring

# Show pools
psql -h localhost -p 6432 -U pgbouncer -c 'SHOW POOLS;'

# Show clients
psql -h localhost -p 6432 -U pgbouncer -c 'SHOW CLIENTS;'

# Show servers
psql -h localhost -p 6432 -U pgbouncer -c 'SHOW SERVERS;'

# Show usage
psql -h localhost -p 6432 -U pgbouncer -c 'SHOW STATS;'

Common Production Failures

Pool exhaustion causing timeouts: You set maximumPoolSize too low for your concurrency. Requests start queuing up, connectionTimeout fires, and your API starts returning 503s. Under load, this cascades. Monitor threadsAwaitingConnection in HikariCP or SHOW POOLS in pgBouncer — if it is consistently above zero, your pool is too small.

Leaked connections not detected: An application bug holds a connection open without returning it to the pool. maximumPoolSize connections leak, subsequent requests block, and eventually the pool is exhausted. HikariCP’s leakDetectionThreshold catches this — set it to something shorter than your p99 query time so leaks are detected before the pool starves.

pgBouncer transaction mode breaking session state: You deploy pgBouncer in transaction mode but your application uses SET LOCAL, temporary tables, or prepared statements that persist across statements. SET LOCAL values vanish after commit because the connection is returned to the pool. Either switch pgBouncer to session mode or audit your application for session-level features.

Pool oversized for database max_connections: You set HikariCP maximumPoolSize = 100 on 50 application instances connecting to PostgreSQL with max_connections = 100. The math fails — 50 x 100 = 5,000 required backend connections. Either use pgBouncer in front of PostgreSQL, or ensure maximumPoolSize * instances <= max_connections.

Idle connections exceeding database limits: Your application runs on a serverless platform that scales instances to zero between requests. Each cold start opens connections up to maximumPoolSize, and with many instances, you briefly exceed max_connections. Set minimumIdle = 0 in HikariCP for serverless workloads and let connections be created on demand.

Prepared statements not working across pgBouncer: You use prepared statements in PostgreSQL with pgBouncer in transaction mode. Prepared statements are connection-scoped, but pgBouncer returns your connection to the pool after each commit. Your next transaction gets a different connection and the prepared statement is gone. Either use session mode or execute statements without preparation.

Wrong pool mode causing connection pressure: You run pgBouncer in session mode when you should be in transaction mode. Each client holds a backend connection for the entire session, which means you can support fewer concurrent clients than max_connections allows. For high-concurrency OLTP, transaction mode is almost always the right choice.

Capacity Estimation: Pool Size Math

The pool size formula from PostgreSQL documentation: connections = (core_count * 2) + effective_spindle_count. For SSDs, effective_spindle_count is effectively 0, so the formula simplifies to 2 * cores. For a database server with 16 cores and spinning disks, that is roughly 32 backend connections per pool.

But that is only the starting point for a single application. When you have N application instances, the constraint becomes total_connections = pool_size * N. PostgreSQL’s max_connections is a hard ceiling. If you deploy 10 application instances each with pool_size=50, you need 500 backend connections. PostgreSQL default is 100. At that scale, you need pgBouncer in transaction mode between your applications and PostgreSQL — pgBouncer multiplexes hundreds of application connections onto a small number of backend connections.

Memory consumption per connection: a PostgreSQL backend typically uses 5-10 MB of memory at idle and can grow with complex queries. A pool of 50 connections can consume 250-500 MB of PostgreSQL server memory just for idle backends. At 500 connections, you are looking at 2.5-5 GB of reserved memory that cannot be used for shared_buffers or query execution. This is why oversized pools are a memory problem, not just a contention problem.

Real-World Case Study: PgBouncer at Stripe

Stripe runs one of the largest PostgreSQL deployments in the production software world, processing millions of transactions per day. Their database team has written extensively about their connection pooling architecture. The problem they faced: thousands of application servers, each running multiple worker processes, all connecting to PostgreSQL primaries and replicas. The connection count math was brutal — without pooling, they would have needed tens of thousands of backend connections.

Their solution was pgBouncer in transaction mode, deployed as a sidecar process on each application host. Each application process connects to its local pgBouncer, which multiplexes those connections down to a small number of actual PostgreSQL connections. This let them run thousands of application instances with predictable connection counts.

Key numbers from their setup: they typically run default_pool_size = 10 to 20 per pgbouncer instance, which feeds into a much smaller set of actual PostgreSQL connections. At their scale, PostgreSQL max_connections is tuned carefully and monitored aggressively — going over means immediate connection failures for payment processing.

The lesson: the formula 2 * cores applies when a single application is your only client. As soon as you have multiple application instances, pgBouncer becomes a multiplier for connection efficiency, not just a connection multiplexer. Without it, you either exhaust max_connections or you under-deploy application instances and leave throughput on the table.

Interview Questions

Q: Your application is a Node.js server handling 500 concurrent requests. Each request needs a database connection. Your PostgreSQL server has 8 CPU cores and max_connections = 100. How do you approach connection pooling?

With 500 concurrent requests and only 100 available connections, you cannot give each request its own connection. The solution is pooling, but a pool of 100 in each of N application instances still needs N _ 100 connections total. The right architecture is application-level pooling (a small pool per application instance, say 10-20 connections) feeding into pgBouncer in transaction mode, which multiplexes onto the 100 PostgreSQL backends. Node.js is single-threaded but async, so a small pool handles the concurrency efficiently. The key constraint is pool_size _ application_instances <= max_connections, and pgBouncer breaks that dependency.

Q: A service starts throwing connection timeout errors after running fine for hours. You check and find that SHOW POOLS in pgBouncer shows zero available connections. What is happening?

Pool exhaustion typically means either the pool was sized too small for the actual concurrency, or connections are leaking (not being returned to the pool). HikariCP exposes threadsAwaitingConnection as a metric — if that is climbing, requests are queueing up faster than connections free up. In pgBouncer, SHOW STATS shows num_waited and num_timeout — non-zero values mean clients are waiting and timing out. Check for long-running transactions holding connections, connection leaks in the application, or a sudden traffic spike the pool was not designed for. The fix is either increase pool size (if the database can handle it), reduce transaction duration, or add retry logic for pool exhaustion errors.

Q: You switch pgBouncer from session mode to transaction mode to handle more concurrent users. What breaks?

Transaction mode is the most efficient pool mode but it breaks anything that relies on session state. SET LOCAL values vanish when the transaction commits and the connection returns to the pool. Temporary tables are dropped when the session ends — with transaction pooling, that happens after every commit. Prepared statements are connection-scoped, so they disappear when you get a new connection after commit. PostgreSQL advisory locks are also connection-scoped and do not survive pooling. Audit your application for SET, SET LOCAL, PREPARE, advisory locks, and ON COMMIT actions before switching to transaction mode.

Q: How do you decide between HikariCP, pgBouncer, and ProxySQL for a new application?

HikariCP is embedded in your application process — it pools connections per JVM instance. Use it when you are on the JVM and want minimal overhead for connection reuse within each application instance. PgBouncer sits between your application and the database — it pools at the database level. Use it when you have many application instances or processes and need to multiplex them onto fewer database connections. ProxySQL is a full database proxy with query routing, caching, and traffic shaping. Use it when you need read/write splitting across replicas, query caching, or multi-database routing. Most production PostgreSQL deployments end up using both HikariCP (per-instance) and pgBouncer (server-level) together.

Conclusion

Connection pooling is essential for performance under load. HikariCP is the standard for Java applications, providing fast bytecode-optimized pooling. PgBouncer is excellent for PostgreSQL, especially with transaction pooling mode for high-scale workloads. ProxySQL adds sophisticated routing and query management features. Set your pool sizes based on database and client count, configure timeouts deliberately, and monitor to catch problems early.

For more on database infrastructure, see Database Scaling and Relational Databases.