Connection Pooling: HikariCP, pgBouncer, and ProxySQL

Database connections are not free. That TCP handshake, the authentication round-trip, the session initialization — add them together and you’re looking at 20-50ms before the first query runs. Connection pooling keeps connections warm and ready, so requests can borrow one without going through setup again.

The trouble is that pooling has its own pitfalls. Get the pool size wrong, mismanage timeouts, or skip health checks and you’ll wish you had not bothered. A poorly tuned pool can make things worse instead of better.

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 (30ms) → Query (5ms) → Close (5ms) = 40ms
Request 2: Open (30ms) → Query (5ms) → Close (5ms) = 40ms
Request 3: Open (30ms) → Query (5ms) → Close (5ms) = 40ms

With pooling, requests reuse existing connections:

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

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

How Connections Flow Through a Pool

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 from PostgreSQL docs:

pool_size = (number_of_cores * 2) + effective_spindle_count

For SSDs (no spindle contention), this simplifies to:

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

Factors That Affect Pool Size

CPU-bound queries need smaller pools. 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 — some contention is inevitable.

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

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.

Quick Recap Checklist

• Connection pooling eliminates TCP handshake + auth overhead on every request
• Pool size formula: 2 × cores for SSDs; (2 × cores) + effective_spindle_count for spinning disks
• HikariCP: set maximumPoolSize, minimumIdle, connectionTimeout, idleTimeout, maxLifetime
• PgBouncer in transaction mode: connection returned after each commit, not held for session
• Transaction mode breaks: SET LOCAL, prepared statements, advisory locks, temp tables
• pool_size × instances must stay below max_connections
• Use pgBouncer to break the N × pool_size dependency on max_connections
• ProxySQL: read/write split via query rules to hostgroups
• HikariCP leak detection: set leakDetectionThreshold shorter than p99 query time
• minimumIdle=0 for serverless workloads; connections created on demand

Interview Questions

Further Reading

• HikariCP documentation — configuration reference and performance tuning
• pgBouncer documentation — pool modes, configuration, and monitoring commands
• PostgreSQL max_connections and memory — connection-related runtime parameters
• ProxySQL documentation — query routing, caching, and multi-database support
• Stripe engineering: Scaling PostgreSQL — real-world connection pooling architecture at scale

Conclusion