Using Connection Pooling to Improve Database Performance

Intermediate·2 min read·

Fast recall

Quick revision

Core answer

Connection pooling reuses expensive connections; configure HikariCP with maximumPoolSize = (cores * 2) + 1, set connectionTimeout, and monitor pool wait time to detect saturation early.

Interview Answer2 min

Connection pooling reuses database connections across requests — creating a new connection involves a TCP handshake, TLS negotiation (if enabled), and database authentication, which takes 10-50ms; for a service handling thousands of requests per minute, that overhead becomes significant.

What HikariCP does

HikariCP creates a fixed number of database connections at startup and keeps them authenticated and ready — when a request needs the database, it borrows a connection from the pool, uses it, and returns it
Which means the connection overhead occurs once at startup, not on every database call; at 1,000 req/min, that's 1,000 × 10ms = 10 seconds of connection overhead saved per minute without pooling

Key configuration parameters

maximumPoolSize: the ceiling on connections; HikariCP's guidance: (CPU cores x 2) + effective spindle count; for a 4-core database server, roughly 9-10 connections; a pool of 100 queues at the database CPU without improving throughput
minimumIdle: connections kept alive when idle; set equal to maximumPoolSize for fixed-size pools; lower values save connections during idle periods but cause pool scaling on traffic spikes
connectionTimeout: how long a thread waits for a connection from the pool before throwing an exception; default 30 seconds is too long for production HTTP requests; set 3-5 seconds to fail fast

Monitoring pool health

Micrometer exposes HikariCP metrics automatically: hikaricp.connections.active (in use), hikaricp.connections.idle (available), hikaricp.connections.pending (threads waiting for a connection)
pending > 0 consistently means threads are waiting — either increase maximumPoolSize (if I/O-bound) or reduce query duration; pending connections add latency to every request that waits

PgBouncer for PostgreSQL at scale

At very high connection counts (hundreds of application instances), PostgreSQL's process-per-connection model becomes expensive — PgBouncer sits between the application and PostgreSQL, multiplexing many application connections to a few PostgreSQL connections
Transaction-mode pooling in PgBouncer holds the PostgreSQL connection only for the duration of a transaction, allowing one PostgreSQL connection to serve many application connections serially

Interview-ready guide

Interview answer

What HikariCP does

HikariCP creates a fixed number of database connections at startup and keeps them authenticated and ready — when a request needs the database, it borrows a connection from the pool, uses it, and returns it
Which means the connection overhead occurs once at startup, not on every database call; at 1,000 req/min, that's 1,000 × 10ms = 10 seconds of connection overhead saved per minute without pooling

Key configuration parameters

maximumPoolSize: the ceiling on connections; HikariCP's guidance: (CPU cores x 2) + effective spindle count; for a 4-core database server, roughly 9-10 connections; a pool of 100 queues at the database CPU without improving throughput
minimumIdle: connections kept alive when idle; set equal to maximumPoolSize for fixed-size pools; lower values save connections during idle periods but cause pool scaling on traffic spikes
connectionTimeout: how long a thread waits for a connection from the pool before throwing an exception; default 30 seconds is too long for production HTTP requests; set 3-5 seconds to fail fast

Monitoring pool health

Micrometer exposes HikariCP metrics automatically: hikaricp.connections.active (in use), hikaricp.connections.idle (available), hikaricp.connections.pending (threads waiting for a connection)
pending > 0 consistently means threads are waiting — either increase maximumPoolSize (if I/O-bound) or reduce query duration; pending connections add latency to every request that waits

PgBouncer for PostgreSQL at scale

At very high connection counts (hundreds of application instances), PostgreSQL's process-per-connection model becomes expensive — PgBouncer sits between the application and PostgreSQL, multiplexing many application connections to a few PostgreSQL connections
Transaction-mode pooling in PgBouncer holds the PostgreSQL connection only for the duration of a transaction, allowing one PostgreSQL connection to serve many application connections serially

03 · Deep dive