Connection pooling
Opening a Postgres connection is more expensive than people think. Most apps gloss over this because the framework hides it, but if you actually crack open the network trace, you’ll find something like a dozen round trips and a TLS handshake hiding behind a one-line query() call. Connection pooling is the standard answer — keep the expensive setup work amortized across many queries instead of paying it on every one — but as soon as your app fans out to multiple processes, the obvious in-process pool stops being enough, and you start needing an external pooler. This post walks through the three rungs of that ladder, in the order you usually meet them.
What’s expensive about a database connection
Every fresh connection to Postgres requires a TCP three-way handshake, a TLS handshake (if you’re using SSL, which you should be), an authentication round trip, and a startup message exchange to set up the session. That’s typically five to ten network round trips and a non-trivial amount of CPU on both ends, before you’ve sent a single query. On the database side, every connection also allocates a backend process — Postgres is a process-per-connection database — which costs roughly 10 MB of memory plus assorted internal structures. None of that is free.
Connection pooling is the trick that makes the cost go away in the steady state. You open a fixed number of connections up front, keep them open, and hand them out to whoever wants to run a query. When the query finishes, the connection goes back into the pool to be reused. The expensive setup work happens once per connection, not once per query, and from your application’s perspective pool.query(...) is just as fast as if the database were sitting on the other side of a function call.
In-process pooling with pg.Pool
The simplest form of pooling lives inside your application process. In Node.js with the pg driver, you import Pool instead of Client, configure how many connections you want, and the library handles the rest:
import { Pool } from 'pg';
async function initPool(config: DatabaseConfig): Promise<Pool> {
const poolConfig = {
host: config.host,
port: config.port,
database: config.dbname,
user: config.username,
password: config.password,
ssl: config.ssl,
max: 100,
};
return new Pool(poolConfig);
}max: 100 means this pool will hold up to 100 open TCP connections to Postgres. When your app calls pool.query(...), the library either hands you an idle connection from the pool or — if all 100 are busy and you haven’t hit the max — opens a new one. When you release the connection, it goes back into the pool for the next query. You can also do per-connection setup at this layer: setting a default statement_timeout, picking a transaction isolation level, and so on, so that every checked-out connection comes pre-configured.
It’s worth doing one refinement on top of this if you’re running against Aurora or RDS with read replicas. Two pools rather than one — a writer pool pointed at the cluster’s writer endpoint, and a read-only pool pointed at the reader endpoint:
pool = initPool(config);
readonlyPool = initPool(readonlyConfig);The reason is asymmetric. Writes are expensive on the primary — they go through the storage quorum, hold row locks, generate WAL — and the primary is your single point of write availability. The more you can keep off the writer, the more headroom it has when write traffic spikes. Routing read-only queries to the reader pool offloads exactly that load, at the cost of a few milliseconds of replication lag, which is fine for anything that isn’t strictly read-after-write. Some reads still have to stay on the writer because they’re part of a write transaction, but you can route the rest.
When the in-process pool stops being enough
The thing the in-process pool doesn’t solve is what happens when your app has more than one process. Container orchestrators don’t run one Node process — they run a fleet. If you set max: 100 and your ECS service scales to 5 tasks, you now have 500 open TCP connections to Postgres, even at idle. Scale to 20 tasks for a traffic spike and you have 2,000.
Postgres has a hard ceiling on this called max_connections. The default is in the low hundreds depending on the instance size, and even on a beefy RDS instance it tops out in the low thousands. Each connection costs memory on the database side regardless of whether it’s actively running a query — the backend process is just sitting there, idle, holding state. Hit the limit and new connection attempts get rejected outright with too many connections, which means your app starts erroring not because the database is too busy but because it can’t even accept the request.
The fundamental issue is that the in-process pool only amortizes connection setup within one process. There’s no coordination across processes — each one assumes it has the database to itself. With one process that’s a fine assumption; with twenty it’s catastrophic.
External poolers: RDS Proxy and pgBouncer
The fix is to introduce a pooler that sits between your app processes and Postgres, with a much smaller pool of actual database connections that it multiplexes across all the incoming app connections. Two common options.
AWS RDS Proxy is the managed version. You stand up a proxy in front of your RDS instance, point your app’s pool at the proxy’s endpoint instead of the database’s, and the proxy handles the multiplexing. The code change is tiny:
async function initPool(config: DatabaseConfig): Promise<Pool> {
const poolConfig = {
port: config.port,
database: config.dbname,
user: config.username,
password: config.password,
ssl: config.ssl,
max: 100,
};
if (config.proxyEndpoint) {
poolConfig.host = config.proxyEndpoint;
poolConfig.ssl = poolConfig.ssl ?? {};
poolConfig.ssl.require = true;
}
return new Pool(poolConfig);
}The app’s max: 100 is now measured against the proxy, not against Postgres. The proxy might hold only 30 or 40 actual database connections behind it, even with hundreds of app-side connections open. Failover handling, IAM auth, and cert lifecycle all come along for free as part of the managed service.
pgBouncer is the self-hosted equivalent — a small, fast C process that does the same multiplexing. You run it as a sidecar or as its own deployment, point your app at it, and it maintains a small pool of upstream connections. The setting that makes it powerful is transaction pooling mode: a database connection is checked out only for the duration of a single transaction, then immediately returned to the upstream pool. With transaction pooling, 500 app-side connections might need only 20 to 30 actual database connections, because at any given moment most of those app connections are either idle or between transactions.
Transaction pooling does come with a constraint, which is worth knowing before you turn it on. Any feature that depends on connection-level state stops working as expected — session variables that aren’t SET LOCAL, prepared statements, advisory locks, LISTEN/NOTIFY. Anything that lives on the connection rather than the transaction can leak across users or quietly get lost. Most apps don’t lean on those features heavily, but if yours does, you either need to keep using session pooling mode (which gives you a much smaller connection-saving win) or refactor the code that depends on connection state.
The mental model
I think about this now as three rungs of a ladder, and you only climb to the next rung when the one below stops working.
The first rung is no pool at all — Client instead of Pool — which is fine for scripts and one-off jobs but pays the full setup cost on every query. Real apps don’t run this way.
The second rung is an in-process pool: pg.Pool in your Node process, HikariCP in your JVM app, or whatever your driver ships. This is the right answer for any service that fits into a small number of processes. Until you scale horizontally, the math works out.
The third rung is an external pooler in front of the database — RDS Proxy or pgBouncer — multiplexing across all your app processes. You climb to this rung when (number of app processes) × (per-process pool size) starts approaching the database’s max_connections. Often that’s also the moment you notice that most of those connections are idle most of the time, which is exactly the inefficiency the external pooler fixes.
The cost of getting this wrong is asymmetric. Underprovisioning the pool just makes queries queue, which is annoying but recoverable. Overprovisioning past max_connections brings the whole database down, at exactly the moments you most need it to stay up — traffic spikes, scaling events, recovery from a deploy. That’s why “pool well below your database’s limit” is the right default, and why the third rung exists at all.