Connection pooling in PostgreSQL — HikariCP, PgBouncer and the Wooldridge formula

← Back to the section

When an application opens a connection to PostgreSQL, the database doesn't just accept a TCP packet — it starts a separate operating-system process. Each such process takes about 10 MB of memory and requires a context switch on every query. Open 200 connections and the database creates 200 processes — a noticeable load even on a powerful server.

A connection pool solves this problem: it keeps a fixed number of open connections and hands them out to requests as needed.

Why "more connections" doesn't mean "faster"

Intuitively it seems: more connections — more parallel work — higher throughput. In practice that's not the case.

PostgreSQL processes queries in parallel, but the bottleneck is not the number of connections — it's the number of CPU cores. Switching between hundreds of processes is expensive, and contention over locks grows quadratically with the number of concurrent workers.

Researcher Brent Wooldridge formulated a formula for the optimal pool size:

connections = (number of cores × 2) + number of disk devices

For a modern server with an SSD (a single disk device) and, say, 4 cores:

connections = (4 × 2) + 1 = 9

In practice the working range is 10–20 connections per application instance. A pool of 20 connections on an 8-core server yields higher overall throughput than a pool of 100.

The max_connections budget

PostgreSQL has a max_connections parameter (default 100) — this is the total limit across all connections to all databases. If you have 10 application instances with 20 connections each, that's 200 total — already above the default. You need to either raise max_connections to 300–500 or put PgBouncer in front.

Key pool parameters

Four parameters matter regardless of language and library.

max = min-idle (keep the pool always full)

If you set a minimum of 5 and a maximum of 20, the pool will ramp up from 5 to 20 the moment a load spike hits. Those few seconds of "warm-up" add latency exactly when the load is already high. It's simpler to keep the steady number of connections equal to the maximum.

connection-timeout: 3s (better to fail fast)

If all connections are busy, the request queues up. 3 seconds of waiting is a good threshold: if the pool couldn't hand out a connection in that time, something is wrong. Failing fast is better than silently hanging.

max-lifetime: 30 min (refresh connections)

Over time a connection accumulates server-side state: cached queries, changed session parameters. After 30 minutes the pool closes the connection and opens a new one. This value should be less than the timeouts in the load balancer — otherwise the pool will keep connections that the balancer already considers dead.

leak-detection-threshold: 60s (detect leaks)

If a connection isn't returned to the pool within a minute, the driver logs a stack trace. This is a sign of one of three things: you forgot to close the connection, a slow HTTP call is made inside a transaction, or the transaction hung. Don't disable this parameter — it's free monitoring.

Configuration by language

spring:
  datasource:
    hikari:
      maximum-pool-size: 20
      minimum-idle: 20
      connection-timeout: 3000
      idle-timeout: 600000
      max-lifetime: 1800000
      leak-detection-threshold: 60000
      auto-commit: false

HikariConfig config = new HikariConfig();
config.setMaximumPoolSize(20);
config.setMinimumIdle(20);
config.setConnectionTimeout(3_000);
config.setMaxLifetime(1_800_000);
config.setLeakDetectionThreshold(60_000);
config.setAutoCommit(false);

DataSource ds = new HikariDataSource(config);

cfg, _ := pgxpool.ParseConfig("postgres://app:secret@localhost:5432/mydb")

cfg.MaxConns = 20
cfg.MinConns = 20
cfg.MaxConnLifetime = 30 * time.Minute
cfg.MaxConnIdleTime = 10 * time.Minute
cfg.HealthCheckPeriod = 30 * time.Second

// connection-timeout is set at the level of the request context:
// ctx, cancel := context.WithTimeout(ctx, 3*time.Second)

pool, _ := pgxpool.NewWithConfig(context.Background(), cfg)

import { Pool } from "pg";

const pool = new Pool({
  max: 20,
  min: 20,
  idleTimeoutMillis: 600_000,
  connectionTimeoutMillis: 3_000,
});

from psycopg_pool import ConnectionPool

pool = ConnectionPool(
    conninfo="host=localhost port=5432 dbname=mydb user=app password=secret",
    min_size=20,
    max_size=20,
    timeout=3.0,
    max_lifetime=1800.0,
    max_idle=600.0,
    open=True,
)

Monitoring the pool

The pool publishes metrics worth tracking:

• active — how many connections are busy right now.
• idle — how many are free.
• pending — how many threads are waiting for a connection. If this number is consistently above zero, the pool is too small or the transactions are too long.
• timeout — a counter of cases where a connection couldn't be handed out within the allotted time.

Set up an alert: if pending > 0 holds for a minute, you need to find out the cause. If timeout > 0, it's either a connection leak or an undersized pool.

HikariCP exports metrics via Micrometer (Spring Actuator). In Go you use pgxpool.Stat(), in Node — pool.totalCount / pool.idleCount / pool.waitingCount, in Python — pool.get_stats().

When you need PgBouncer

An application-level pool works well for a single service with a few instances. But if you have:

• dozens of instances of a single service,
• many different services on one PostgreSQL cluster,
• stateless functions (serverless, short-lived processes),

— the total number of connections from all instances starts to press against max_connections. This is where PgBouncer helps: it sits between the application and PostgreSQL and multiplexes thousands of client connections into dozens of real ones.

PgBouncer modes

transaction is the usual choice. It gives maximum utilization with minimal limitations.

Prepared statements and transaction mode

In transaction mode the PostgreSQL server doesn't keep prepared statements between transactions — after each transaction the connection goes to another client. You need to disable server-side prepared statements at the driver level.

spring:
  datasource:
    hikari:
      data-source-properties:
        prepareThreshold: 0

cfg.ConnConfig.DefaultQueryExecMode = pgx.QueryExecModeSimpleProtocol

// node-postgres does not use server-side prepared statements by default
// when calling pool.query() — no extra action is needed.

pool = ConnectionPool(
    conninfo="...",
    kwargs={"prepare_threshold": 0},
)

An alternative is PgBouncer version 1.21 and above, which can cache prepared statements itself.

Also: SET commands without SET LOCAL are lost after COMMIT. For LISTEN/NOTIFY you need a separate pool in session mode or a different mechanism (a message queue).

PgBouncer configuration example

[databases]
mydb = host=postgres-master port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction
default_pool_size = 20
max_client_conn = 1000
reserve_pool_size = 5

A typical ratio: the application keeps a pool of 50 connections to PgBouncer, and PgBouncer keeps 20 real connections to PostgreSQL. Fifty application threads can "think" in parallel, while at most 20 go to the database.

Read replica

If you have a read replica, it needs a separate pool. Don't route to the replica queries that just wrote data to the master: replication is asynchronous — usually milliseconds, but under load it can be several seconds.

@Configuration
public class DataSourceConfig {

    @Bean @Primary
    public DataSource routingDataSource(DataSource master, DataSource replica) {
        var routing = new TransactionRoutingDataSource();
        routing.setTargetDataSources(Map.of(
            DataSourceType.READ_WRITE, master,
            DataSourceType.READ_ONLY,  replica
        ));
        routing.setDefaultTargetDataSource(master);
        return routing;
    }
}

type DB struct {
    master  *pgxpool.Pool
    replica *pgxpool.Pool
}

func (db *DB) Pool(readOnly bool) *pgxpool.Pool {
    if readOnly {
        return db.replica
    }
    return db.master
}

const master  = new Pool({ host: "pg-master",  ...config });
const replica = new Pool({ host: "pg-replica", ...config });

export function getPool(readOnly: boolean): Pool {
    return readOnly ? replica : master;
}

master  = ConnectionPool(conninfo="host=pg-master  ...", min_size=20, max_size=20)
replica = ConnectionPool(conninfo="host=pg-replica ...", min_size=20, max_size=20)

def get_pool(read_only: bool) -> ConnectionPool:
    return replica if read_only else master

Common mistakes

Too large a pool. A pool of 200 connections on a 4-core server performs worse than a pool of 20 — because of context-switching overhead. Rule of thumb: 10–20 connections per instance.

Multiple pools to one database. If different parts of a single application create their own pools to the same database, the total number of connections multiplies. One pool per application.

A long operation inside a transaction. An HTTP call to an external service, file processing, a long loop — all of that inside an open transaction holds the connection. Move heavy operations outside the transaction.

A read replica for the "just wrote — read immediately" scenario. Because of replication lag, a fresh write may not appear on the replica immediately. For such scenarios — the master only.

In short

• PostgreSQL starts an OS process per connection (~10 MB + context-switching overhead).
• Optimal pool size by the Wooldridge formula: (cores × 2) + devices. For most services that's 10–20 connections.
• Keep max = min-idle: the pool is always full, no warm-up during peaks.
• connection-timeout: 3s — failing fast is better than silently hanging.
• max-lifetime: 30 min — refresh connections; the value should be less than the balancer timeout.
• leak-detection-threshold: 60s — don't disable it, it's free leak monitoring.
• PgBouncer is needed with dozens of instances, many services, or serverless workers.
• In transaction mode, disable server-side prepared statements at the driver level.
• A read replica is a separate pool; don't use it for "wrote → read immediately" scenarios.

Further reading

• Transactions in PostgreSQL — how long transactions double the load on the pool.
• Isolation levels — readOnly and routing to the replica.
• Locks — lock_timeout and long transactions.