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

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 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.

Most connection pools create Connection proxy objects using reflection — every prepareStatement() call, every setAutoCommit() flip, every commit and rollback goes through a runtime-generated proxy class that calls Method.invoke(). That is slow. HikariCP instead compiles a custom subclass of your driver’s PGConnection (or MySQL’s MysqlConnection) at startup, binding only the methods your code actually calls. Method calls on a HikariCP connection look like normal Java method calls, not reflection calls. The cost is paid once at pool initialization; the benefit applies to every connection checkout for the lifetime of the JVM.

HikariCP also uses the simple query protocol for its internal housekeeping commands (SELECT 1, SHOW pool_size). PostgreSQL’s extended query protocol has more framing overhead per packet, so using simple query for health checks saves a few bytes per round-trip. It also sets SO_RCVBUF and SO_SNDBUF to minimal values so the kernel does not buffer beyond what low-latency connections need — preferring direct syscall reads over intermediate buffers.

Connections die. The database restarts, a firewall drops a TCP keepalive, pg_terminate_backend gets called. Most pools check connection health lazily, when a request tries to borrow one. HikariCP runs a background thread that continuously tests idle connections with a lightweight SELECT 1 — no prepareStatement call needed, so the test path is as fast as possible. If a connection fails, it gets removed before any request ever sees it.

The pool’s underlying ConcurrentBag uses a hybrid structure: a central ArrayList of Connection objects plus per-thread ThreadLocal queues. The common case — a thread borrowing and returning its own connections — stays entirely in the thread-local queue with no synchronization. When a thread’s queue is empty, it steals from another thread’s queue in O(1) time without atomic operations. The hot path (acquire → use → return) has zero synchronized blocks on the critical loop.

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 catch: anything requiring a session-scoped resource — SET LOCAL, temporary tables, advisory locks, prepared statements — breaks in transaction mode because those resources are tied to the physical connection, which changes after every transaction.

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
# 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.

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 (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.

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