Database Connection Pooling: PgBouncer and HikariCP Guide

In this tutorial, you'll learn about Database Connection Pooling: PgBouncer and HikariCP Guide. We cover key concepts, practical examples, and best practices to help you understand and apply this topic effectively.

Database connection pooling maintains a cache of reusable database connections to eliminate the overhead of TCP handshake, SSL negotiation, and authentication on every database interaction, enabling applications to scale to thousands of concurrent requests.

What You'll Learn

You will understand Connection Pool architectures, configure PgBouncer in Transaction mode, tune HikariCP for Spring Boot applications, apply Little's Law for pool sizing, detect connection leaks, and monitor pool health in production.

Why Connection Pooling Matters

Opening a database connection takes 10-70ms. For an application processing 1000 requests per second, that is 10-70 seconds of overhead per second. DodaZIP handles thousands of concurrent archive lookups; pooling reduced database connection overhead from 45ms to under 1ms per request.

Connection Pooling Learning Path

flowchart LR
  A[SQL Basics] --> B[Database Design]
  B --> C[Connection Pooling]
  C --> D[Replication]
  C --> E[Backup and Recovery]
  C:::current
  classDef current fill:#f90,color:#fff,stroke:#333,stroke-width:2px

Pooling Architectures

There are two main approaches to connection pooling:

In-Process Pooling (HikariCP, SQLAlchemy)

The pool lives inside the application process. Fastest option because there is no network hop.

flowchart LR
    App[Application
HikariCP Pool] --> DB1[(Database)]
    subgraph App
        P[Pool: 20 connections]
    end

External Pooling (PgBouncer, Pgpool-II)

A separate proxy process manages connections. Useful for multi-language environments and reducing total connections to the database.

flowchart LR
    S1[Service 1] --> PB[PgBouncer]
    S2[Service 2] --> PB
    S3[Service 3] --> PB
    PB --> DB[(Database)]

PgBouncer Configuration

PgBouncer is a lightweight connection pooler for PostgreSQL written in C.

Installation

# Ubuntu / Debian
sudo apt-get update && sudo apt-get install pgbouncer

# Verify installation
pgbouncer --version
# Expected: pgbouncer 1.22.x

Configuration

# /etc/pgbouncer/pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb
mydb_replica = host=10.0.1.10 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432

# Authentication
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

# Pooling mode: session, transaction, or statement
pool_mode = transaction

# Pool sizes
default_pool_size = 25
max_client_conn = 200
max_db_connections = 50

# Timeouts
server_idle_timeout = 600      # Close idle server connections after 10min
client_idle_timeout = 0        # Never close idle clients
query_timeout = 30             # Kill queries taking over 30 seconds

# Logging
log_connections = 1
log_disconnections = 1
stats_period = 60              # Reset stats every 60 seconds

# /etc/pgbouncer/userlist.txt
"app_user" "scram-sha-256$hashvalue"
"readonly_user" "scram-sha-256$anotherhash"

Pooling Modes

Running PgBouncer

# Start pgbouncer
sudo systemctl start pgbouncer
sudo systemctl enable pgbouncer

# Check status
sudo systemctl status pgbouncer

# Show pools
psql -h localhost -p 6432 -U app_user -d mydb -c "SHOW POOLS;"

# Show stats
psql -h localhost -p 6432 -U app_user -d mydb -c "SHOW STATS;"

SHOW POOLS output:

 database |   user   | cl_active | cl_waiting | sv_active | sv_idle | sv_used
----------+----------+-----------+------------+-----------+---------+---------
 mydb     | app_user |        15 |          0 |        15 |      10 |       0

HikariCP Configuration

HikariCP is the fastest Connection Pool for Java applications and the default in Spring Boot.

Spring Boot Configuration

# application.yml
spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/mydb
    username: app_user
    password: ${DB_PASSWORD}
    hikari:
      pool-name: DodaTechPool
      maximum-pool-size: 20
      minimum-idle: 5
      connection-timeout: 30000
      idle-timeout: 600000
      max-lifetime: 1800000
      leak-detection-threshold: 60000
      validation-timeout: 5000
      connection-test-query: SELECT 1

Programmatic Configuration (Java)

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

public class DatabaseConfig {

    public HikariDataSource createDataSource() {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:postgresql://localhost:5432/mydb");
        config.setUsername("app_user");
        config.setPassword(System.getenv("DB_PASSWORD"));
        config.setMaximumPoolSize(20);
        config.setMinimumIdle(5);
        config.setConnectionTimeout(30_000);
        config.setIdleTimeout(600_000);
        config.setMaxLifetime(1_800_000);
        config.setLeakDetectionThreshold(60_000);
        config.addDataSourceProperty("cachePrepStmts", "true");
        config.addDataSourceProperty("prepStmtCacheSize", "250");
        config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
        return new HikariDataSource(config);
    }
}

Pool Sizing Theory

Little's Law

The optimal pool size depends on request rate and query latency:

L = lambda x W
L = connections needed
lambda = requests per second
W = average query time in seconds

# Calculate optimal pool size
requests_per_second = 500
avg_query_time_seconds = 0.05
optimal_connections = requests_per_second * avg_query_time_seconds
print(f"Optimal pool size: {optimal_connections}")
# Output: Optimal pool size: 25

CPU-Based Formula

For databases that use one process/thread per connection (PostgreSQL):

# General formula
cpu_cores = 8
disk_spindles = 0  # SSD = 0, HDD = number of spindles
pool_size = (2 * cpu_cores) + disk_spindles
print(f"Recommended pool size: {pool_size}")
# Output: Recommended pool size: 16

Connection Leak Detection

A connection leak occurs when code borrows a connection but never returns it, eventually draining the pool.

Detecting Leaks with HikariCP

# Enable leak detection in application.yml
spring:
  datasource:
    hikari:
      leak-detection-threshold: 60000

When a connection is held longer than 60 seconds, HikariCP logs a stack trace:

WARN  - Connection leak detection triggered
Stack trace:
  java.lang.Exception
    at com.example.MyService.getConnection(MyService.java:42)
    at com.example.MyService.processOrder(MyService.java:55)

Preventing Leaks

// BAD: Connection may not be returned on exception
Connection conn = dataSource.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM orders");
// Missing: conn.close() in finally block

// GOOD: try-with-resources ensures closure
try (Connection conn = dataSource.getConnection();
     PreparedStatement stmt = conn.prepareStatement(
         "SELECT * FROM orders WHERE user_id = ?")) {
    stmt.setInt(1, userId);
    try (ResultSet rs = stmt.executeQuery()) {
        while (rs.next()) {
            // process row
        }
    }
}

Monitoring Pool Health

Key Metrics

Prometheus Metrics (HikariCP + Micrometer)

management:
  endpoints:
    web:
      exposure:
        include: health,metrics,prometheus
  metrics:
    export:
      prometheus:
        enabled: true

# Check pool metrics
curl http://localhost:8080/actuator/metrics/hikaricp.connections.active

Output:

{
  "name": "hikaricp.connections.active",
  "measurements": [{"statistic": "VALUE", "value": 12}],
  "availableTags": [{"tag": "pool", "values": ["DodaTechPool"]}]
}

Common Connection Pooling Errors

1. Setting Pool Size Too Large

More connections than CPU cores causes context switching overhead. A pool of 200 on 8 cores is slower than a pool of 16.

2. Not Using Transaction Pooling Mode

Session mode in PgBouncer holds connections open between HTTP requests. Transaction mode returns connections to the pool after each Transaction.

3. Forgetting to Configure Connection Timeout

Without connection_timeout, a request can block forever waiting for a pool connection. Always set a timeout of 5-30 seconds.

4. Ignoring maxLifetime

Database servers, firewalls, and load balancers close idle connections. Set max_lifetime to 30 minutes (less than any intermediate timeout).

5. Connection Leaks in Exception Paths

Code paths that throw exceptions before close() leak connections. Always use try-with-resources or finally blocks.

6. Not Preparing Statements

Statement preparation (prepared statements, cachePrepStmts=true) reduces query planning overhead on reused connections.

7. Pooling Per Service vs Central Pooling

For Microservices, each service maintaining its own pool can overwhelm the database. Use PgBouncer as a central pooler to cap total connections.

Practice Questions

1. What is the difference between session mode and Transaction mode in PgBouncer?

Session mode keeps the database connection for the entire client session. Transaction mode returns it to the pool after each Transaction completes.

2. How do you calculate the optimal pool size using Little's Law?

Multiply the request arrival rate (requests/second) by the average query execution time (seconds). Example: 500 req/s x 0.05s = 25 connections.

3. What is a connection leak and how do you detect it?

A leak occurs when code borrows but never returns a connection. Enable leak-detection-threshold in HikariCP to log stack traces of leaked connections.

4. Why does a pool of 200 connections perform worse than 20?

Only 8 connections (one per CPU core) can execute simultaneously. The other 192 cause context switching overhead, increasing latency for all queries.

5. Challenge: Diagnose pool exhaustion.

Your application shows "Connection is not available, request timed out after 30000ms" during peak hours. Active connections are at 100% of max. What do you investigate? Answer: Check for connection leaks (enable leak detection), identify slow queries holding connections (pg_stat_activity), verify pool sizing with Little's Law, check for batch jobs running during peak, review connection timeout settings, and consider adding a read replica for read queries.

FAQ

Try It Yourself

Set up PgBouncer with PostgreSQL:

1. Install PostgreSQL and PgBouncer on a local machine or Docker
2. Configure PgBouncer in Transaction mode with pool_size=10
3. Create a test script that opens and closes 100 connections
4. Run it with and without PgBouncer
5. Measure total execution time for both approaches
6. The PgBouncer version should be 10-50x faster

What's Next

You have learned how to configure PgBouncer and HikariCP, size pools correctly, detect leaks, and monitor pool health. Apply pool sizing theory to your application and set up leak detection before issues reach production.