Connection pool sizing is not a tuning knob

A service I maintain started returning 504s under a load that it had handled comfortably a month earlier. Nothing about the query plans had changed. The obvious move was to raise the connection pool limit, which had already been raised twice before by someone else for the same reason. It was sitting at 200.

Lowering it to 12 removed the timeouts entirely. The p99 dropped from just over four seconds to about ninety milliseconds. This is not a clever trick; it is what happens when you stop asking the database to do two hundred things at once on eight cores.

Why more connections make things slower

Postgres runs one backend process per connection. Those processes are not free: each one costs memory, each one competes for CPU, and each one takes locks that other backends then wait on. Once the number of active backends exceeds the number of cores by any meaningful margin, you are not adding capacity, you are adding context switching.

The useful mental model is that the database has a fixed amount of real concurrency available, roughly bounded by cores and by how much of your workload is disk-bound. Everything beyond that is a queue. The only question is where the queue lives. If the pool is small, the queue lives in your application, where it is cheap, observable, and bounded. If the pool is large, the queue lives inside Postgres, where every waiting item is also consuming resources.

Requests queue somewhere regardless of pool size; you only choose where.
Queueing in the application is cheap. Queueing in the database is not.
A pool timeout is a much better failure mode than a lock timeout.
The number to start from is cores, not expected request volume.

Waiting in line costs nothing. Waiting in line while holding a table lock costs everyone.

Working out a starting number

The formula people usually cite is connections = ((core_count * 2) + effective_spindle_count). On modern SSD-backed instances the spindle term is close to meaningless, so in practice I start at roughly two to three times the core count of the database instance, divided across every service that connects to it. That last part is the one that gets forgotten: the pool limit is per process, and if you run six replicas of a service with a pool of 50 each, you have configured 300 connections, not 50.

Here is the pool setup I ended up with, using sqlx:

use std::time::Duration;
use sqlx::postgres::PgPoolOptions;

pub async fn connect(url: &str) -> Result<sqlx::PgPool, sqlx::Error> {
    PgPoolOptions::new()
        // Small on purpose. See the note about replica count.
        .max_connections(12)
        .min_connections(2)
        // Fail fast rather than piling up in-flight requests.
        .acquire_timeout(Duration::from_secs(3))
        .idle_timeout(Duration::from_secs(600))
        .max_lifetime(Duration::from_secs(1800))
        .connect(url)
        .await
}

The acquire_timeout matters as much as the size. Without it, a saturated pool turns into unbounded latency, which is indistinguishable from an outage but much harder to alert on. With a three second timeout, saturation shows up immediately as a specific error type that you can count.

What I would do differently is measure first. The signal I should have looked at on day one was the ratio of time spent waiting to acquire a connection versus time spent executing the query. If acquisition dominates, the pool is too small. If execution time grows as concurrency grows while the work per query stays constant, the pool is too large and the database is thrashing. That single ratio would have answered in five minutes what took most of an afternoon.