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.

The HikariCP documentation quotes a formula that came from the PostgreSQL wiki itself:

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.

The whole pool in one class

The mechanics show up without a database. Semaphore holds the permits for busy connections; tryAcquire with a timeout is the waiting queue and that same connection-timeout.

live example

import java.util.concurrent.Semaphore;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.atomic.AtomicInteger;

public class PoolDemo {
    static final Semaphore free = new Semaphore(2, true);
    static final AtomicInteger served = new AtomicInteger();
    static final AtomicInteger refused = new AtomicInteger();

    public static void main(String[] args) throws Exception {
        Thread[] requests = new Thread[6];
        for (int i = 0; i < requests.length; i++) {
            requests[i] = new Thread(PoolDemo::query);
            requests[i].start();
        }
        for (Thread t : requests) {
            t.join();
        }
        System.out.println("pool of 2, 6 requests, waiting no longer than 300 ms");
        System.out.println("got a connection: " + served.get());
        System.out.println("refused on timeout: " + refused.get());
    }

    static void query() {
        try {
            if (!free.tryAcquire(300, TimeUnit.MILLISECONDS)) {
                refused.incrementAndGet();
                return;
            }
            try {
                Thread.sleep(200);
                served.incrementAndGet();
            } finally {
                free.release();
            }
        } catch (InterruptedException e) {
            Thread.currentThread().interrupt();
        }
    }
}

Two connections, six concurrent requests, each holding a connection for 200 ms. Four of them make it: two take a connection straight away, two wait for one to be freed. The last pair would have to wait 400 ms — longer than the 300 ms allowed — and they are refused. A real pool behaves the same: nothing appears beyond the limit, the request queues and is refused on timeout.

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 — how many times a connection wasn't handed out in time; it grows on a leak or an undersized pool.

Two deserve an alert: pending > 0 for a minute, and any growth of timeout.

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": None},  # None disables; 0 means prepare right away
)

An alternative is PgBouncer: since 1.24 it caches prepared statements itself — max_prepared_statements defaults to 200. In 1.21–1.23 the feature existed but was off by default.

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. Two hundred connections on a four-core server perform worse than twenty: the time goes into context switching. The rule of thumb stays: 10–20 per instance.

Multiple pools to one database. If different parts of an application open their own pools to the same database, the connections add up. One pool per application.

A long operation inside a transaction. A call to an external service, file processing, a long loop — all of that inside an open transaction keeps the connection busy. Heavy work goes outside the transaction.

A replica for the "wrote — read immediately" scenario. Because of replication lag a fresh write may not have reached the replica yet. Such reads go to the master.

In short

• PostgreSQL starts a separate OS process per connection — about 10 MB plus context switching.
• Pool size is computed as (cores × 2) + disk devices; in practice that's 10–20 connections per instance.
• max = min-idle keeps the pool always full, with no warm-up at a peak; connection-timeout: 3s fails fast instead of hanging silently.
• max-lifetime: 30 min is kept below the balancer timeout, and leak-detection-threshold: 60s is never disabled — it's free leak hunting.
• PgBouncer is needed once instances number in the dozens; in transaction mode server-side prepared statements are disabled at the driver level.
• A read replica needs a pool of its own, and the "wrote — read immediately" scenario is not sent to it.

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.
• Replication — where the replica lag you must not rely on after a write comes from.