Database Connection Pooling: PgBouncer, ProxySQL, and HikariCP Compared

It was 2:47 AM when the on-call alert fired. A Node.js API server handling a flash sale had just fallen over under load. The postmortem revealed the root cause: every incoming HTTP request was calling pg.connect(), opening a brand-new TCP connection to PostgreSQL, performing a TLS handshake, sending login credentials, waiting for server-side session initialization, running a single query, and then closing the connection. At 50 requests per second this worked fine. At 800 requests per second—the flash sale peak—PostgreSQL hit its max_connections limit of 100, began rejecting new connections with FATAL: sorry, too many clients already, and the API returned 500s to every user trying to check out. No queries were slow. There was no bad index. The application logic was correct. The entire outage was caused by a missing connection pool.

Connection pooling is one of those infrastructure decisions that is invisible when it is working correctly and catastrophic when it is missing. The fix for that Node.js team was adding eight lines of configuration to their application's startup code. Understanding why those eight lines matter—what happens during connection establishment, how pool sizing affects throughput, and what trade-offs exist between client-side and server-side poolers—is the subject of this guide.

Background: The Hidden Cost of Opening a Connection

Most developers think of a database connection as a simple socket. In reality, establishing a new connection to PostgreSQL or MySQL involves a multi-stage protocol that consumes time on both the client and the server before a single query byte is sent:

1. TCP handshake: Three-way SYN / SYN-ACK / ACK exchange. On localhost this is sub-millisecond. Across a VPC or data center, it adds 0.5–5ms.
2. TLS handshake (if SSL/TLS is enabled): Certificate exchange, key agreement, and cipher negotiation. TLS 1.3 adds 1–3ms over LAN; TLS 1.2 with a full handshake adds 3–10ms.
3. Database authentication: The client sends a startup message with username, database name, and application_name. The server responds with an authentication challenge (md5, scram-sha-256, or similar). The client sends credentials. The server validates them. This is 1–3 round trips.
4. Server-side session initialization: PostgreSQL forks a new backend process for the connection. That process loads the search path, applies default GUC settings, and prepares internal data structures. On a loaded server this can take 5–15ms of wall-clock time.

Add these up and a "fresh" connection costs between 5ms and 30ms depending on network topology and server load. On PostgreSQL, each backend process also consumes roughly 5–10 MB of RAM for its working memory, buffer caches, and WAL writer state. Opening 500 connections from 10 application pods means PostgreSQL is spending 2.5–5 GB of RAM just to hold those connections alive, before a single byte of query data has been processed.

How Connection Pooling Works

A connection pool is a cache of already-established, fully authenticated database connections maintained by a managing process (either the application itself or a sidecar). When application code needs to run a query, it checks out a connection from the pool, uses it, and returns it. The connection is not closed; it goes back into the pool ready for the next request.

The pool lifecycle looks like this:

1. At application startup, the pool opens minimumIdle (or minPoolSize) connections. These connections go through the full TCP + TLS + auth handshake once.
2. When a request arrives, the pool hands out an idle connection in microseconds—no network round-trip required.
3. If all connections are in use, new requests wait in a queue up to connectionTimeout (typically 30 seconds) before returning an error.
4. The pool grows up to maximumPoolSize under sustained load.
5. Idle connections are returned to the server after idleTimeout to reclaim server-side resources.
6. The pool probes idle connections with a keepaliveQuery to detect stale connections before they are handed to application code.

The Three Pooling Modes

Server-side poolers (pgBouncer, ProxySQL) support multiple pooling modes, each defining when a server-side connection is returned to the pool:

• Session pooling: A server connection is assigned to a client for the entire duration of the client's session. The connection is released when the client disconnects. This is the safest mode—it fully supports prepared statements, SET commands, advisory locks, and all session-level state—but provides minimal multiplexing benefit because one client holds one server connection at all times.
• Transaction pooling: A server connection is assigned to a client only for the duration of a single transaction. The moment COMMIT or ROLLBACK is sent, the server connection returns to the pool. This is the mode that actually delivers the multiplexing benefit: 100 server-side connections can serve 1,000 application clients if those clients are not all in a transaction simultaneously. This is the recommended mode for most production deployments—with important gotchas covered below.
• Statement pooling: A server connection is released after every single statement, even within a transaction. This mode is incompatible with multi-statement transactions and is rarely used in practice outside of very specific read-only analytics workloads.

Pool Sizing Formula

The most common misconfiguration is setting pool size equal to the number of application threads. This is almost always too high. The HikariCP team derived the correct formula through empirical testing:

# HikariCP formula for optimal pool size:
#   pool_size = Tn * (Cm - 1) + 1
#
# Where:
#   Tn = number of application threads (or concurrent request handlers)
#   Cm = number of simultaneous database calls made per request
#        (how many queries run concurrently within a single request context)
#
# Example: 10 application threads, each making at most 1 DB call at a time
#   pool_size = 10 * (1 - 1) + 1 = 1
#   (yes, a pool of 1 is correct here — the threads don't overlap DB calls)
#
# Example: 10 application threads, each potentially making 2 concurrent DB calls
#   pool_size = 10 * (2 - 1) + 1 = 11
#
# Practical floor: add a few connections for background tasks and health checks
#   pool_size = max(formula_result, 10)

For PostgreSQL specifically, there is a second constraint: total connections across all application pods must not approach max_connections. A common formula for PostgreSQL RAM-constrained environments:

-- PostgreSQL max_connections recommendation based on available RAM
-- Each connection costs ~5-10 MB in shared memory overhead
-- Reserve ~20% of RAM for PostgreSQL shared_buffers and OS cache

-- Example: 16 GB server, 4 GB shared_buffers, 2 GB OS headroom
-- Available for connections: ~10 GB
-- Conservative estimate: max_connections = 10240 MB / 7 MB per connection ≈ 100

SHOW max_connections;
-- If running pgBouncer, set max_connections = (num_pgbouncer_pools * pool_size) + 20
-- for monitoring connections and replication slots

Client-Side vs Server-Side Pooling

Connection pooling can happen in two places: inside the application process, or in a dedicated process between the application and the database. The right answer often involves both.

Client-Side Pools

Client-side pools live inside the application process. They are fast (no extra network hop), easy to configure, and expose pool metrics directly to application observability tooling. The downside: each application pod maintains its own pool, so total connection count multiplies with pod count. Ten pods with a pool size of 20 means 200 server connections.

HikariCP (Java / JVM): The fastest and most production-tested client-side pool. Default for Spring Boot.

// Spring Boot application.properties
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.idle-timeout=600000
spring.datasource.hikari.max-lifetime=1800000
spring.datasource.hikari.keepalive-time=60000
// HikariCP formula result: if each request makes 1 DB call at a time,
// pool_size = threads * (1 - 1) + 1, but HikariCP recommends minimum ~10
// for headroom. Set to match your concurrency profile.

pg (Node.js): The standard PostgreSQL client for Node. Pool via pg.Pool.

// Node.js — pg Pool configuration
const { Pool } = require('pg');

const pool = new Pool({
  host: process.env.DB_HOST,
  port: 5432,
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  ssl: { rejectUnauthorized: true },

  max: 20,                // maximum pool size (connections)
  min: 2,                 // minimum idle connections to keep warm
  idleTimeoutMillis: 30000,   // close idle connections after 30s
  connectionTimeoutMillis: 5000, // throw if no connection available in 5s
  maxUses: 7500,          // recycle a connection after 7500 uses (avoids memory leaks)
});

// Always release connections back to pool — never let them leak
async function queryWithPool(sql, params) {
  const client = await pool.connect();
  try {
    const result = await client.query(sql, params);
    return result.rows;
  } finally {
    client.release(); // critical: this returns the connection to the pool
  }
}

psycopg3 (Python): The modern PostgreSQL adapter for Python, with built-in async pool support.

// Python — psycopg3 async connection pool
import asyncio
from psycopg_pool import AsyncConnectionPool

pool = AsyncConnectionPool(
    conninfo="host=localhost dbname=mydb user=app password=secret sslmode=require",
    min_size=2,
    max_size=20,
    max_waiting=50,           # queue depth before returning PoolTimeout
    max_idle=300,             # seconds before shrinking idle connections
    reconnect_timeout=5,      # seconds to retry on connection failure
    open=False,               # don't open connections at import time
)

async def startup():
    await pool.open()
    await pool.wait()         # block until min_size connections are ready

async def run_query(sql: str, params: tuple):
    async with pool.connection() as conn:
        async with conn.cursor() as cur:
            await cur.execute(sql, params)
            return await cur.fetchall()

database/sql (Go): The standard library pool. Simple but effective for most use cases.

// Go — database/sql pool configuration
import (
    "database/sql"
    "time"
    _ "github.com/lib/pq"  // or pgx/v5/stdlib
)

db, err := sql.Open("postgres", dsn)
if err != nil { panic(err) }

db.SetMaxOpenConns(20)                  // maximum open connections
db.SetMaxIdleConns(5)                   // maximum idle connections to keep warm
db.SetConnMaxLifetime(30 * time.Minute) // recycle connections after 30 min
db.SetConnMaxIdleTime(5 * time.Minute)  // close idle connections after 5 min

// Verify the pool is healthy at startup
if err := db.PingContext(ctx); err != nil {
    panic("database pool not reachable: " + err.Error())
}

Server-Side Pools

Server-side poolers sit between the application and the database. All application pods connect to the pooler, which maintains a smaller set of real database connections. Total server connections remain bounded regardless of horizontal scaling. Server-side poolers also survive application restarts without closing database connections.

Tool Comparison

Key Takeaways

Working with JusDB on Connection Pooling

Getting connection pooling right is one of those foundational infrastructure decisions that pays dividends across every workload running on your database. Done correctly, it is transparent—you never think about it. Done incorrectly, it manifests as mysterious timeouts, FATAL connection errors under load, memory pressure on the database server, and incidents at the worst possible times.

JusDB works with engineering teams at every stage of this problem: designing the initial pool architecture for a new application, diagnosing connection exhaustion incidents on existing production systems, migrating from client-side pools to pgBouncer as pod counts grow, and tuning pgBouncer configuration for transaction-mode compatibility with specific ORMs and frameworks (Prisma, SQLAlchemy, GORM, Hibernate, and others each have their own nuances).

If you are seeing connection errors under load, struggling to size pools correctly for a Kubernetes deployment, or evaluating whether pgBouncer, ProxySQL, or RDS Proxy is the right fit for your architecture, we can help.

Database Consulting Services Talk to a Database Expert

We also run a focused connection pooling review engagement—auditing your current pool configuration, identifying misconfigurations, stress-testing pool sizing assumptions, and delivering a prioritized set of changes before your next scaling event, not after your next outage.

Related Reading

• pgBouncer for PostgreSQL: Complete Configuration and Tuning Guide — deep-dive into pgBouncer deployment, pool modes, pool_mode per-database overrides, and admin console commands
• ProxySQL MySQL Load Balancing Guide — how ProxySQL handles connection multiplexing, read/write splitting, query routing rules, and failover for MySQL and Aurora
• Mastering AWS RDS max_connections for MySQL and PostgreSQL — how RDS calculates default max_connections from instance class memory, and how to tune it alongside RDS Proxy