Why Database Connections Are Expensive
Every time a client connects to PostgreSQL, the server forks a new operating system process. This involves allocating memory for the process (typically 5-10 MB), setting up shared memory mappings, initializing per-connection state (transaction context, prepared statements, temp tables), and performing authentication (SSL handshake, password verification, LDAP lookup). On a busy server, establishing a connection can take 5-20 milliseconds — an eternity when your web request should complete in 50 ms.
The Connection Pool Pattern
A connection pool maintains a set of pre-established, idle database connections. When a client needs a connection, it borrows one from the pool. When done, it returns it. The TCP connection and backend process remain alive, eliminating the overhead of repeated connect/disconnect cycles. This is especially critical for modern web architectures where each HTTP request may need a database connection and you might serve thousands of requests per second.
PgBouncer: External Connection Pooler
PgBouncer is a lightweight connection pooler that sits between your application and PostgreSQL. It accepts client connections and multiplexes them onto a smaller number of actual PostgreSQL connections. PgBouncer itself uses only ~2 KB of memory per client connection (compared to ~10 MB per PostgreSQL backend).
PgBouncer Pooling Modes
-
Session Pooling: A server connection is assigned to a client for the entire session (until the client disconnects). The least aggressive mode — it limits the total number of connections but does not share them between clients. Use this when your application relies on session-level features (SET variables, temporary tables, LISTEN/NOTIFY).
-
Transaction Pooling: A server connection is assigned to a client only for the duration of one transaction. Between transactions, the connection returns to the pool and can serve other clients. This is the most common mode because it maximizes sharing. A pool of 20 PostgreSQL connections can serve hundreds of concurrent clients, as long as they are not all in transactions simultaneously.
-
Statement Pooling: A server connection is returned to the pool after every single SQL statement. The most aggressive mode, but it does not support multi-statement transactions. Rarely used because most applications need transactions.
Pool Sizing
The optimal number of PostgreSQL connections is surprisingly small. The formula from the PostgreSQL wiki is:
connections = (core_count * 2) + effective_spindle_count
For an 8-core server with SSDs (spindle count = 1): 8 * 2 + 1 = 17 connections. Having too many connections causes context switching overhead — the OS spends more time switching between processes than doing useful work. PostgreSQL's shared buffer management also degrades with too many concurrent writers. In practice, 20-50 connections is optimal for most workloads, even with thousands of concurrent application clients.
HikariCP: Application-Side Pooling
HikariCP is the fastest JDBC connection pool for Java applications. Unlike PgBouncer (which is a separate process), HikariCP runs inside the application JVM. It manages a pool of JDBC connections and provides features like connection validation (test-on-borrow), leak detection (warn if a connection is held too long), and metrics. Spring Boot uses HikariCP as its default connection pool.
PgBouncer in Production
A typical production setup for a web application handling 5,000 requests per second:
Without connection pooling:
- Each request opens a PostgreSQL connection: 5,000 connections/second
- Connection setup: ~10 ms each → 50 seconds of cumulative connection overhead per second
- PostgreSQL cannot handle 5,000 concurrent backends — it would exhaust memory and thrash the CPU with context switches
- Result: Application crashes under load
With PgBouncer (transaction pooling):
- PgBouncer accepts 5,000 client connections (2 KB each = 10 MB memory)
- It maintains a pool of 25 PostgreSQL connections (250 MB backend memory)
- Each request borrows a connection for ~2 ms (one transaction), then returns it
- At any instant, only ~10 of the 25 connections are active
- Result: Stable, fast performance with minimal resource usage
PgBouncer configuration (pgbouncer.ini):
[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp
[pgbouncer]
listen_port = 6432
pool_mode = transaction
max_client_conn = 10000
default_pool_size = 25
reserve_pool_size = 5
HikariCP configuration (Spring Boot):
spring:
datasource:
hikari:
maximum-pool-size: 20
minimum-idle: 5
connection-timeout: 30000
idle-timeout: 600000
max-lifetime: 1800000
leak-detection-threshold: 60000
Key insight: The HikariCP pool connects to PgBouncer (port 6432), not directly to PostgreSQL (port 5432). This gives you two layers of pooling: HikariCP manages connections within a single application instance, while PgBouncer manages connections across all application instances.