Connection Pooling: HikariCP, pgBouncer, and ProxySQL

Introduction

Opening a database connection for every request adds handshake, authentication, and session setup costs. A pool keeps reusable sessions ready so requests can borrow a connection without repeating that work.

Pooling also adds tuning and failure modes. An undersized pool creates queues, while an oversized pool can exhaust database resources; poor timeout or health-check settings can make both problems harder to diagnose. This guide covers sizing, HikariCP, pgBouncer, ProxySQL, and the operational trade-offs.

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

HikariCP documents this as a starting point, not a universal optimum. Benchmark under expected load and tune for the database and workload:

pool_size = (number_of_cores * 2) + effective_spindle_count

When the active data set is fully cached, the formula treats effective spindle count as zero. Do not assume that is true for every SSD workload; measure the system under representative load.

Factors That Affect Pool Size

Pool size is not a one-size-fits-all number. A few things push it up or down.

Query type controls how long a connection sits busy. CPU-bound queries finish fast — a connection might be done in milliseconds. I/O-bound queries hold connections longer while waiting on disk or network. With fast I/O you can keep more connections active without hitting CPU limits. With slow I/O, a small pool causes queuing even when the database is not CPU-bound.

Client count scales the total demand across all your application instances. If you have 50 instances each with a pool of 10, PostgreSQL sees demand for 500 connections. The pool size per instance and the number of instances both matter. More instances mean you need either smaller per-instance pools or a shared pooler like pgBouncer to break the math.

Memory per connection sets the ceiling. PostgreSQL uses 5-10 MB per idle backend, more under load. A pool of 50 connections reserves 250-500 MB on the database server just for idle backends. If your database server has limited RAM, oversized pools starve shared_buffers and query execution memory.

Network latency changes how much time is spent waiting on the wire. If the database is 20ms away, a connection sits idle for 20ms per round-trip. A larger pool keeps multiple connections in flight so throughput does not tank. With low-latency databases on localhost or the same LAN, latency is not a bottleneck and smaller pools work fine.

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 generated JDBC proxies and a lock-free connection collection to keep the overhead of borrowing and returning connections low.

HikariCP generates lightweight JDBC proxies for connections, statements, and result sets. Javassist generates the proxy method bodies during the build, avoiding reflective dispatch on each JDBC call. These proxies wrap the driver’s JDBC interfaces; they do not subclass vendor-specific connection classes.

HikariCP does not continuously probe every idle connection by default. It validates a connection when borrowed if it has been idle long enough, using JDBC’s isValid() when supported or a configured connectionTestQuery. The optional keepaliveTime setting schedules checks for idle connections; it is disabled by default.

The ConcurrentBag combines thread-local caching, queue stealing, and direct hand-off to reduce contention when threads borrow and return connections. Its lock-free design is intended to keep the pool’s coordination overhead low.

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.

PostgreSQL’s max_connections is a hard ceiling — new connections get rejected once you hit it. With 20 application instances each running 10 workers, you need 200 backend connections even if workers are idle 99% of the time. PostgreSQL’s default is 100, so that topology breaks immediately. PgBouncer solves this by sitting in front of PostgreSQL and multiplexing: each application instance connects to its local pgBouncer, which maintains a smaller pool of actual PostgreSQL connections. 50 application instances each opening 20 connections to pgBouncer might only require 30 real PostgreSQL connections underneath. That N-to-M multiplexing is the whole point.

In session mode, a connection to the database is held from the moment a client connects until it disconnects — even when the client is idle between queries. In transaction mode, pgBouncer only borrows a connection for the duration of each transaction. After COMMIT or ROLLBACK, the connection returns to pgBouncer’s pool and becomes available immediately for the next client. This means 100 client connections can share 20 backend connections in a typical OLTP workload where transactions last milliseconds. The trade-off is that session-scoped state does not reliably carry across transactions because each transaction may use a different physical connection. Transaction-scoped features such as SET LOCAL and transaction-level advisory locks work within their transaction; session-level settings, persistent temporary tables, and session-level advisory locks require care. Protocol-level prepared statements can work when pgBouncer is configured to track them with max_prepared_statements.

PostgreSQL’s authentication protocol (md5 or scram-sha-256) requires a full round-trip every time a new connection is established. For PHP or CGI-style workloads where connections open and close frequently, that round-trip adds up. PgBouncer authenticates once per client connection and reuses the result, multiplexing the session onto an already-authenticated backend. High-frequency short-lived connections skip the handshake entirely.

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 or rolls back
# This allows more clients than connections
pool_mode = transaction

# Statement mode: connection is returned after each statement
# Each statement gets a connection only for that statement; multi-statement transactions are unsupported
pool_mode = statement

Transaction mode is efficient but has limitations:

• Session-level state does not persist across transactions; protocol-level prepared statements require pgBouncer tracking to be enabled
• Multi-statement transactions are supported, but the connection is returned after COMMIT or ROLLBACK

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.

PgBouncer is a pure multiplexer — every application connection hits the same database, with no intelligence involved. ProxySQL inspects the SQL query and makes a routing decision. The most common use case is read/write splitting: SELECT queries go to a replica (hostgroup 1), and INSERT, UPDATE, DELETE go to the primary (hostgroup 0). This lets you scale read-heavy workloads by adding replicas without touching application code. Routing rules live in mysql_query_rules and are evaluated top-down — first match wins.

ProxySQL also caches query results, not just metadata. When a rule marks a query as cacheable and the result fits within query_cache_size, it stores the result keyed by the query string. Identical queries hit the cache without touching the database. This works well for dashboards and reporting queries that run frequently against slowly-changing data. The cache refreshes on an interval, or you can flush it explicitly. Writes to a table need a corresponding rule to purge related cache entries — otherwise you serve stale data.

Traffic mirroring sends a copy of matching queries to a test system without affecting the production path. You can validate new query patterns, test ORM-generated SQL, or run load tests against real traffic. Mirror queries run asynchronously — ProxySQL does not wait for the test destination to respond before returning the result to the client, so the production path sees no added latency.

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

Application-level timeouts control how long your code waits for pool operations. connectionTimeout is how long a request waits to borrow a connection from the pool. If all connections are checked out or slow, requests queue here. Set it longer than your p99 query time plus a buffer — a query that times out at the database should not also time out at the pool level waiting for a connection. validationTimeout is how long HikariCP takes to run its liveness check on a borrowed connection. Five seconds is plenty for a SELECT 1.

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

Use 30 seconds as a starting point for most web applications. Five-second timeouts seem reasonable until latency spikes — then every brief database hiccup kills requests. A longer timeout keeps the request thread alive while connections drain, which is better than cascading failures.

Database-Level Timeouts

Database-level timeouts cancel queries that run too long, independent of the application pool. statement_timeout in PostgreSQL aborts any query that exceeds the limit. This catches runaway queries, poorly indexed bulk operations, and accidental full-table scans. Eight seconds is a common starting point for OLTP; reporting queries may need minutes.

wait_timeout in MySQL controls how long the server holds an idle connection open. This is separate from pool-level idle timeouts. Set it higher than your expected idle periods but low enough to reclaim connections from crashed clients.

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

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

For PostgreSQL, set statement_timeout at the session or database level rather than globally when different workloads need different limits. Application pools should set connectionTimeout independently — the two serve different purposes.

Load Balancer Timeouts

Load balancer timeouts sit in front of your database and proxy layer. timeout client and timeout server control how long the load balancer waits for activity. Set these to match your query response times — 30 seconds covers most web API responses. timeout connect is how long HAProxy waits to establish a connection to a backend. Keep this short (5 seconds or less) so unhealthy backends are removed from rotation quickly. timeout queue controls how long a request waits when all backends are saturated.

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

If timeout client is shorter than your query response time, clients get disconnected while waiting. Set it to at least 2x your p99 response time. A 60-second timeout queue means requests sit for up to a minute before rejection — tune based on what your application considers acceptable queuing.

Idle Connection Management

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

HikariCP Idle Timeout

idleTimeout controls how long HikariCP keeps an idle connection before closing it. minimumIdle is the floor — HikariCP maintains at least this many idle connections even if traffic drops to zero, keeping them ready for incoming requests.

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

Ten minutes works well for most applications. Five-minute idle timeouts recycle aggressively, which helps if connections accumulate stale state. Longer values keep connections warmer for bursty traffic. Keep minimumIdle at a low value (2-5) during predictable traffic and let HikariCP scale up under load.

For serverless workloads where instances scale to zero between invocations, set minimumIdle to 0 so connections are not held between cold starts.

PgBouncer Idle Management

server_idle_timeout in pgBouncer controls how long a backend connection sits idle before pgBouncer closes it. This applies to the PostgreSQL connection, not the client connection.

server_idle_timeout = 600  # seconds

Set server_idle_timeout to 600 seconds to match your application pool idle timeout. If your application considers a connection dead after 10 minutes but pgBouncer holds it for longer, you get orphaned backends on PostgreSQL.

When to Keep Connections Alive

Idle timeout settings close connections that sit unused for too long. There are cases where letting that happen hurts more than it helps.

High-frequency query workloads benefit most from keeping connections warm. If your application runs hundreds of queries per second, closing idle connections means spending 20-50ms on TCP handshakes and authentication every time a connection is recycled. Keeping connections alive eliminates that overhead on every request. The memory cost of a few idle connections is negligible compared to the CPU cost of constant reconnection.

Expensive connection setup is another reason to keep connections alive. If your database requires SSL negotiation, strong authentication like SCRAM-SHA-256, or session initialization scripts, that cost is paid once per connection at creation. A connection that gets closed after 10 minutes of idle wastes that investment. For workloads with periodic traffic spikes, pre-warming connections before a spike avoids latency spikes during the spike itself.

High-latency networks make connection reuse more valuable. A 20ms round-trip to the database means every reconnection costs 20ms minimum. If your application sends one query per user request and handles 200 concurrent users, that is 200 x 20ms = 4 seconds of added latency per cycle if connections are recycled constantly. Keeping connections warm at higher pool sizes hides that latency behind already-open sockets.

Serverless or containerized workloads also benefit from keeping connections warm between invocations. If an instance stays alive between requests, dropping connections between requests causes unnecessary reconnection overhead. Set minimumIdle to a small non-zero value to maintain a base pool even during quiet periods.

When to Close Idle Connections

Closing idle connections frees memory and file descriptors on the database server. Each idle PostgreSQL backend consumes roughly 5-10 MB even when doing nothing. On a shared database server running multiple services, aggressive idle timeouts let other services use that memory.

Three scenarios call for shorter idle timeouts. Shared database servers — closing unused connections prevents one application from hogging memory other services need. Expensive session initialization — some PostgreSQL configurations with SCRAM authentication or heavy session initialization scripts consume more memory per connection, so idle costs are higher. Low query frequency — if an instance handles only a few queries per minute, the overhead of maintaining idle connections outweighs the benefit of keeping them warm.

For most web applications with steady traffic, keeping connections alive is cheaper than reconnecting constantly. The memory cost of a few idle connections rarely outweighs the latency of a cold setup.

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, when prepared statements cannot be reused across connections, when an application relies on session state that transaction pooling cannot preserve across transactions, or when pools are oversized and increase memory pressure and context switching.

Connection Pooling Trade-Offs

Transaction Pooling Gotcha

With pgBouncer in transaction mode, session-level state cannot be relied on after a transaction ends. Transaction-scoped settings such as SET LOCAL remain available inside the transaction:

-- SET LOCAL is scoped to this transaction
BEGIN;
SET LOCAL app.setting = 'value';
-- Later transactions may use another server connection.
COMMIT;

-- Session-level PREPARE state is tied to a physical connection
PREPARE myplan AS SELECT * FROM orders WHERE id = $1;
-- A later transaction may use a different connection

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 depends on state attached to one database session. Session-level settings, temporary tables that must persist across transactions, and session-level advisory locks may fail when a later transaction gets a different connection. SET LOCAL and transaction-level advisory locks remain scoped to and usable within their transaction. Protocol-level prepared statements can be tracked when pgBouncer’s max_prepared_statements setting is enabled.

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: In transaction mode, server-side prepared statements are tied to physical connections. Without protocol-level tracking, a later transaction may use another connection and the statement will be unavailable. Current pgBouncer versions can track protocol-level prepared statements when max_prepared_statements is non-zero; otherwise use session mode or disable that prepared-statement behavior in the client.

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

A commonly used pool-sizing starting point is: connections = (core_count * 2) + effective_spindle_count. If the active data set is fully cached, effective spindle count is zero, so the formula simplifies to 2 * cores. Treat it as a benchmark starting point and validate it with your workload. 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.

Production Failure Scenarios

Pool Exhaustion Causing Timeouts

Under high concurrency, all connections in the pool become checked out and busy. New requests queue in the application, connectionTimeout fires, and the API starts returning 503 errors. This cascade happens when maximumPoolSize is set too low for actual concurrency, or when query response times increase unexpectedly. Watch threadsAwaitingConnection in HikariCP — if it stays above zero, the pool is too small for current demand. In pgBouncer, SHOW STATS reports num_waited and num_timeout; non-zero values mean pool saturation at the proxy layer.

Leaked Connections Not Detected

A connection is acquired from the pool but never returned due to an application bug: a missing close() in an error path, an exception that bypasses the release logic, or a query that hangs indefinitely. The connection stays checked out, reducing effective pool capacity by one. After enough leaks, the pool is exhausted and new requests block until connectionTimeout. HikariCP’s leakDetectionThreshold logs a warning with the stack trace when a connection is checked out longer than this value. Set it slightly above your p99 query time so leaks are caught before the pool starves.

pgBouncer Transaction Mode Breaking Session State

pgBouncer in transaction mode returns a server connection to its pool after each COMMIT or ROLLBACK; the client cannot assume its next transaction will use that same server connection. SET LOCAL ends with its transaction. A temporary table, session-level setting, prepared statement, or session-level advisory lock may be unavailable or behave unexpectedly in a later transaction. Audit the application for session-scoped behavior, enable prepared-statement tracking where supported, or use session mode when the application needs a stable server session.

Pool Oversized for Database max_connections

The math fails silently. You set HikariCP maximumPoolSize = 100 and deploy 20 application instances, but PostgreSQL has max_connections = 100. At startup, 20 instances each opening 10 connections is 200 connection attempts. PostgreSQL accepts 100 and rejects the rest. If startup is staggered, the first few instances grab all 100 connections and later instances fail to connect. The constraint is maximumPoolSize * application_instances <= max_connections. When this cannot be satisfied, put pgBouncer in transaction mode between the application pools and PostgreSQL. PgBouncer multiplexes hundreds of application connections onto a small number of actual backend connections.

Common Pitfalls / Anti-Patterns

Setting maximumPoolSize Too Large for max_connections

This is the most common pooling mistake. A 4-core database with max_connections = 100 cannot support 10 application instances each running maximumPoolSize = 20. That requires 200 backend connections and exceeds the PostgreSQL limit immediately. Calculate safe pool size as maximumPoolSize = floor(max_connections / application_instances). If you need more concurrency than this allows, use pgBouncer to multiplex.

Not Using leakDetectionThreshold in HikariCP

Without leak detection, connection leaks go unnoticed until the pool is exhausted. A connection held for minutes instead of milliseconds slowly drains the pool until requests start queuing. Set leakDetectionThreshold above your p99 query time but below connectionTimeout. This gives HikariCP enough signal to distinguish a slow query from a true leak.

Running pgBouncer in Wrong Pool Mode

Session mode holds a backend connection for the entire client session, appropriate for applications relying on session state. Transaction mode returns the connection after each transaction. Session-level state does not persist across transactions; SET LOCAL and transaction-level advisory locks work within one transaction, while prepared-statement behavior depends on pgBouncer version and configuration. Statement mode returns the connection after each individual statement, useful only for specific workloads. Choosing the wrong mode causes silent failures that only appear in production under specific conditions. Audit your application’s use of session-scoped features before choosing pool mode.

Ignoring Idle Timeout Settings

Connections sitting idle for hours consume memory on the database server and hold file descriptors without doing useful work. If your application has bursty traffic with long quiet periods, idle connections from a previous burst still hold resources when the next burst arrives. Set idleTimeout to match your traffic pattern. 10 minutes works for most web applications with steady traffic. For bursty workloads, shorter idle timeouts (5 minutes) reclaim resources faster between bursts.

Using Prepared Statements Across pgBouncer Transaction Mode

Server-side prepared statements are associated with PostgreSQL connections. In transaction mode, a later transaction may use a different connection, so prepared-statement behavior depends on client protocol and pgBouncer configuration. Current pgBouncer versions can track protocol-level prepared statements when max_prepared_statements is non-zero. Otherwise, use session mode or configure the client to avoid server-side preparation through the pooler.

Observability Checklist

HikariCP Metrics to Monitor

Track these metrics in production:

• hikaricp.connections.active — connections currently checked out by application threads
• hikaricp.connections.idle — connections in the pool not in use
• hikaricp.connections.pending — requests waiting for a connection
• hikaricp.connections.max — configured maximum pool size
• hikaricp.connections.min — configured minimum idle pool size
• hikaricp.connections.acquire — connection acquisition count
• hikaricp.connections.usage — elapsed time between checkout and return

threadsAwaitingConnection (via JMXMXBean) is the most critical metric — a consistently rising value means the pool is too small.

pgBouncer Commands to Run

Query the pgBouncer admin interface to check pool health:

# Pool status: num_waited, num_timeout show contention
psql -h localhost -p 6432 -U pgbouncer -c 'SHOW POOLS;'

# Client connections: connected clients vs available servers
psql -h localhost -p 6432 -U pgbouncer -c 'SHOW CLIENTS;'

# Server connections: active vs idle backend connections
psql -h localhost -p 6432 -U pgbouncer -c 'SHOW SERVERS;'

# Aggregate stats: total transactions, queries, bytes sent/received
psql -h localhost -p 6432 -U pgbouncer -c 'SHOW STATS;'

Connection Latency Histograms

Measure the full round-trip latency from application to database, broken down by connection checkout time and query execution time:

• Connection checkout time should stay below 5ms for local connections
• HikariCP exposes connectionTimeout violations as pool rejection events
• Track query execution time per connection to identify slow queries holding connections

Database Server Resource Monitoring

Each PostgreSQL backend connection consumes 5-10 MB of server memory even when idle. Track:

• pg_stat_activity count of active and idle connections
• Server memory used by backends: connections * 5-10 MB
• Database server available memory not consumed by connections
• Autovacuum workers queued due to connection pressure

Alerting Thresholds

Set alerts for these conditions:

• threadsAwaitingConnection > 0 for more than 30 seconds — pool saturation
• pgBouncer num_waited > 0 consistently — contention at proxy layer
• Connection checkout time above connectionTimeout / 2 — pool bottlenecking
• Database server memory consumption above 80% — pool may be oversized

Security and Compliance Notes

Database Credentials in Connection Pools

Never hardcode database credentials in application code or configuration files committed to source control. Store database passwords in a secrets manager (HashiCorp Vault, AWS Secrets Manager, Kubernetes Secrets) and inject them into the application environment at runtime. HikariCP accepts passwords via environment variables or external configuration, so credentials never appear in code. Rotate credentials regularly and update connection pool configurations atomically to avoid connection storms during rotation.

TLS/SSL for Database Connections

Configure database connections to use TLS/SSL to encrypt traffic between the application and the database. In HikariCP, add ssl=true or sslmode=require to the JDBC URL. For PostgreSQL, use sslmode=verify-full in production to validate the server certificate. Unencrypted connections expose query data, authentication tokens, and session state on the network. In pgBouncer, enable SSL with server_tls_ssl_mode = verify-full and client_tls_ssl_mode = require.

Connection Pool Authentication

pgBouncer authenticates client connections against its own user list (auth_file) or an external authentication source (auth_type). Use auth_type = md5 or auth_type = scram-sha-256 for password-based authentication. For higher security, integrate with an LDAP directory for centralized credential management. When using HikariCP with pgBouncer, give the application a separate credential from the application-to-pgBouncer credential so credentials can be rotated independently.

Limiting max_connections to Prevent Resource Exhaustion

Set max_connections in PostgreSQL to a value that leaves enough memory for query execution, shared buffers, and the operating system. Use: max_connections = floor((server_memory - os_memory - shared_buffers) / 10MB). Setting it too high allows runaway connection counts that cause PostgreSQL to swap or OOM. HikariCP’s maximumPoolSize and pgBouncer’s default_pool_size should stay well below max_connections under normal load, with headroom for connection spikes.

Audit Logging of Connection Pool Changes

Track changes to pool configuration in your change management system. When pool size is modified in production, the change should be reviewed, approved, and logged with a timestamp and the reason for the change. Unplanned pool size increases often indicate an underlying problem — a memory leak, slow queries, or connection leaks — that should be investigated rather than masked with a larger pool.

Compliance Considerations for Connection Metadata

Connection pool logs and metrics may contain sensitive metadata: usernames, query patterns, execution times, and connection IDs. When retaining connection pool data for debugging or performance analysis, ensure the retention policy complies with your data classification requirements. Mask or exclude query parameters containing PII from pool metrics. In regulated environments (PCI-DSS, HIPAA, SOC 2), connection metadata may be in scope for access controls and audit logging requirements.

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 changes physical connections between transactions; session-level state needs care, while transaction-scoped settings and locks work within a transaction
• 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

Related Articles

• Database replication explains read replicas and how connection proxies route database traffic.
• Capacity planning covers estimating database capacity and setting resource limits.
• API retries, timeouts, backoff, and circuit breakers explains how to set timeouts and handle temporary dependency failures.

Conclusion