Episode 28 · Too many clients already

Connection pooling

Black Friday, 10:14: StayHub autoscaled from 4 to 12 app servers, and 1 in 5 requests now fails with "sorry, too many clients already". More servers made it worse.

Why your app keeps a few database connections open and shares them, and why adding more connections often makes things slower.

30 min intermediateConnection poolingConnection pool sizing (Little's law)Serverless connection stormsFinding the bottleneckThroughputAutoscaling

Problem
Hundreds of app instances and thousands of concurrent requests all need to talk to one database.
Why the simple system fails
Opening a connection per request costs milliseconds and server memory; letting every instance open as many as it likes exhausts max_connections, and a database juggling hundreds of active queries gets slower, not faster.
Engineering concept
Keep a small number of long-lived connections and share them. Size the total from the database's capacity, not from the number of clients, and let excess requests wait briefly in a queue.
Technology
In-process pools (node-postgres, HikariCP, SQLAlchemy), PgBouncer or a managed proxy in transaction mode, serverless-friendly drivers and proxies.
Trade-offs
Small pools queue requests; large pools overload the database. External poolers add a hop and restrict session features. Serverless needs a pooler outside the functions.

The problem

2 min

Black Friday, 10:14. Traffic is three times a normal day.

Omar · On-call SRE
Autoscaling took us from 4 to 12 app servers. 20% of requests fail: too many clients already.
Karim · CTO
More servers made it worse? That's backwards.
Lina · Senior engineer, your mentor
Last episode of the module: you lead this one. Where would you start?

The big idea

4 min

More servers, more errors

The big idea

A database runs only as many queries at once as it has cores. Cap its connections near what the work needs, and let the excess wait in the app tier, where waiting is cheap.

A kitchen with 8 cooks doesn't cook faster with 300 tables seated at once: orders pile up and everyone waits longer. The is the host keeping a short line at the door.

How it works

5 min

What a connection costs, and what a pool does

1
Open
A connection is an open, authenticated session. In PostgreSQL each one is a separate process: a few ms and several MB to open.
2
Reuse
A connection pool keeps a few long-lived connections open and lends them to requests, one at a time.
3
Wait
If all are busy, the next request waits briefly in the app's queue instead of opening another.
4
Run
An 8-core database truly runs about 8 queries at once. Hundreds of active ones just share the same cores.
5
Cap
A like PgBouncer is a small proxy that shares a fixed number of real connections among many clients, however many servers connect.

At 10:20 someone raised max_connections to 500. The errors stopped, but about 300 queries ran at once on 8 cores, fighting over CPU and locks: p99 went from 80 ms to 2.1 s.

: busy connections ≈ queries per second × seconds each holds one. StayHub's peak: 1,200/s × 5 ms ≈ 6. A pooler with 30 real connections is plenty, and stays 30 with 4 servers or 40.

Watch a decision being made

4 min

Worked example

You size StayHub's connections (Lina checks)

Stop the "too many clients" errors, bring p99 back under 100 ms, and make sure autoscaling can't break the database again.

1Count asked-for, compute needed
Asked for: 12 × 20 = 240, more during rolling deploys when old and new servers overlap. Needed: 1,200 queries/s × 5 ms ≈ 6; with headroom, 30.
Why: Lina's check: "Good, you counted the deploy overlap." The total grows with servers, the need grows with work.
2Put a cap in front
PgBouncer in transaction mode (a real connection is lent per transaction), default_pool_size = 30. App pools drop to 10 with a 2 s acquire timeout.
bash
```
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 30
```
Why: The database-side total is fixed whatever the server count; excess requests wait in the cheap app tier.
3Check the session features
Search the code for session-level SET, LISTEN and session advisory locks; replace SET with SET LOCAL.
Why: The next transaction may run on another real connection, so session state silently vanishes. "This is the step people skip."
4Write the budget, then measure
200 = 30 PgBouncer + 10 admin/migrations + 10 replication/monitoring + 150 spare. Replay Black Friday: 0 errors, p99 90 ms, ~25 active queries.
Why: A written budget makes the next service ask for a slice instead of quietly taking one; the replay proves both goals.

In real life

3 min

A pooler, or no connections at all?

GitLab.com: a central PgBouncer layer

Publicly documents running PgBouncer in front of PostgreSQL
Long-lived app servers share a fixed set of real connections

Fits many long-lived servers sharing one database.

Serverless apps with an HTTP database driver

Providers like Neon publicly offer HTTP/WebSocket drivers for functions
No persistent connections; less control over long transactions

Fits bursts of thousands of short-lived functions.

What goes wrong

3 min

Raising max_connections to fix "too many clients"

More concurrent queries on the same cores: latency rises for everyone.

Instead

A pooler and small per-server pools.

Holding a connection during slow external calls

A 500 ms payment call holds it 100× longer than a 5 ms query.

Instead

Release before network calls; keep transactions short.

Serverless functions connecting straight to PostgreSQL

Each concurrent invocation opens its own: a .

Instead

Go through a pooler or HTTP driver, pool size 1, capped concurrency.

Trade-offs

3 min

Option	Fits when	You pay
In-process pool only	A few long-lived servers, total well under the limit	Total grows with servers; no global cap
External pooler	Autoscaling, several services, any serverless	A component to run; session-feature limits
HTTP data API	Very bursty serverless or edge	Provider-specific; less transaction control

Monday after Black Friday. Deciding the permanent setup.

Karim · CTO
No new components. A 32-core database with max_connections 500. One click.
Lina · Senior engineer, your mentor
About four times the database bill, and the total still grows with every server.
Karim · CTO
And PgBouncer is one more thing that can fall over at 3 a.m.

Your job as tech lead

4 min

Claude Code can write the code for this. You still own the decisions, and you have to know what good looks like to accept or reject its work.

Decisions you own

The connection budget: 200 = 30 pooler, 10 admin and migrations, 10 replication and monitoring, 150 spare. New services get their share from you.
Pool sizes from arithmetic: ~1,200 queries/s × ~5 ms ≈ 6 busy connections, so 30 shared ones is plenty.
How long a request may wait for a connection before failing fast (2 s), and that no code holds one while waiting on something slow.

Tell Claude Code

On Black Friday we autoscaled to 12 servers with pools of 20: 240 connections against max_connections 200. Raising it to 500 sent p99 from 80 ms to 2.1 s. Plan first, including rollback. Put PgBouncer in transaction mode in front, server pool 30; app pools 10 with a 2 s acquire timeout; max_connections back to 200. List code using session features. Then load test 600 requests/s on 12 servers: DB connections, pool wait time, p99.

Check before you accept

With the pooler, the database sees at most 30 app connections however many servers autoscaling adds.
Claude lists code using SET, LISTEN/NOTIFY or advisory locks that would break in transaction mode.
Every connection is released, including error paths.

Red flags

It fixes "too many clients" by raising max_connections again.
It sets pool sizes by "the more the better", with no arithmetic.
It retries failed connections immediately in a loop, turning a spike into a storm.

Check yourself

2 min

Q1/3Reasoning

Your API runs 2,500 queries per second against PostgreSQL. Each query holds a connection for 6 ms on average. On average, how many connections are busy at once?

connections

How confident are you?

Next episode, new module: changing free cancellation from 24 to 48 hours takes six days and eleven files, and Karim asks what "architecture" actually means.

Finished?

Mark it complete to update your skill map.

Next: What is software architecture?

Go deeper (optional)You don't need this to understand the lesson. Open it when a project needs the details: Technical deep dive, Visual diagram, Analogy, Why it matters, Check your understanding, Practice ladder, Explain it yourself.

Technical deep dive

Sizing the pool

Worked example

You size StayHub's connections (Lina checks)

Stop the "too many clients" errors on Black Friday, bring p99 back under 100 ms, and make sure autoscaling to any number of servers can't break the database again.

1Count what's asked for
12 servers × pool max 20 = 240 possible connections; PostgreSQL allows 200. During a rolling deploy, old and new servers overlap, so it can briefly be even more.
Why: Lina's check: "Good, you counted the deploy overlap." The total grows with servers, not with work, so the next scale-up breaks it again.
2Compute what's needed
Little's law: ~1,200 queries/s × 5 ms average hold ≈ 6 busy connections. With headroom for bursts and slow queries: 30.
Why: The database can only run about 8 queries truly in parallel on 8 cores. Needing 6 on average means 30 real connections is generous, not tight.
3Put a cap in front
Run PgBouncer in transaction mode with default_pool_size = 30. Apps connect to PgBouncer; each app pool drops to 10 with a 2 s acquire timeout.
Why: The database-side total is now fixed at 30 whether we run 4, 12 or 40 servers. Excess requests wait in the cheap app tier.
4Check the session features
Search the code for session-level SET, LISTEN, and session advisory locks. Replace SET with SET LOCAL inside transactions.
Why: In transaction mode, the next transaction may run on a different real connection, so session state silently vanishes. Lina's check: "This is the step people skip."
5Write the budget down
max_connections 200 = 30 PgBouncer + 10 migrations/admin + 10 replication/monitoring + 150 spare. Lower max_connections back from 500.
Why: A written budget means the next service that wants the database asks for a slice instead of quietly taking one.
6Measure under load
Replay Black Friday traffic: 0 connection errors, p99 90 ms, about 25 active queries at the database at peak.
Why: The numbers prove both goals: no errors, and the database is no longer drowning in concurrent work.

import { Pool } from "pg";

// One pool per process, created once at startup (not per request!).
export const pool = new Pool({
  connectionString: process.env.DATABASE_URL, // points at PgBouncer :6432
  max: 10,                        // per server; PgBouncer caps the DB side
  idleTimeoutMillis: 30_000,      // close idle connections
  connectionTimeoutMillis: 2_000  // fail fast if the pool is exhausted
});

export async function confirmBooking(id: string) {
  const client = await pool.connect();      // manual checkout for a transaction
  try {
    await client.query("BEGIN");
    await client.query("UPDATE bookings SET status = 'confirmed' WHERE id = $1", [id]);
    await client.query("COMMIT");
  } catch (e) {
    await client.query("ROLLBACK");
    throw e;
  } finally {
    client.release();                       // ALWAYS release, or the pool leaks
  }
}

Leaked connections (missing release) slowly drain the pool until every request waits for the acquire timeout.

bash

# pgbouncer.ini (illustrative)
[databases]
stayhub = host=10.0.1.5 port=5432 dbname=stayhub

[pgbouncer]
listen_port = 6432
pool_mode = transaction      ; lend a server connection per transaction
max_client_conn = 2000       ; cheap client-side connections
default_pool_size = 30       ; real PostgreSQL connections per db/user pair
reserve_pool_size = 5        ; short burst allowance
server_idle_timeout = 60

Pool mode	Server connection is held for	Works with	Breaks
Session	The whole client session	Everything (SET, temp tables, LISTEN, advisory locks)	Little multiplexing: barely better than no pooler
Transaction	One transaction	Most web apps	Session state across transactions: SET, session advisory locks, LISTEN; prepared statements depending on pooler version and driver
Statement	One statement	Simple autocommit workloads	Multi-statement transactions

Transaction mode is the usual choice for web traffic. Check your driver and pooler docs for prepared-statement support.

Visual diagram

API pods ×20 (pool 10 each)

Functions (0 → 2,000)

PgBouncer (transaction mode)

Wait queue

PostgreSQL (8 vCPU)

Pool metrics

Click a component

Many app instances and serverless functions share a small number of real database connections through a pooler.

Analogy

In a reservations officeWith a database

In a reservations office8 agents answering callsWith a database8 CPU cores
In a reservations officeInstalling a new phone line per callWith a databaseNo pool: a new connection per request
In a reservations office10 lines always connected, short holdWith a databaseA connection pool
In a reservations office400 lines ringing for 8 agentsWith a databaseAn oversized pool
In a reservations officeA switchboard sharing 20 linesWith a databasePgBouncer

Where the analogy breaks: a caller on hold hears music and knows they're waiting. A request in a pool queue just looks slow, so you need to measure the wait to notice it.

Why it matters

Connection problems cause some of the most confusing outages.

Autoscaling adds 30 app copies, each with a pool of 20. Suddenly the database says "too many clients" and refuses everyone.

A marketing email triggers 2,000 functions at once, each opening its own connection. The database refuses them, they retry, and it gets worse. That's a .

Or code holds a connection while waiting on a slow payment API. The pool empties, and every page times out, even ones that never touch payments.

If you understand pools, you fix the cause instead of raising the limit and making it worse.

Check your understanding

Q1/3Debugging

During a sale, the HPA (Horizontal Pod Autoscaler) scales your API from 8 to 30 pods. New pods crash-loop with FATAL: sorry, too many clients already. Each pod has a pool max of 10; max_connections is 200. What's the best fix?

How confident are you?

Practice ladder

Step 1 · Trace

Trace a deploy's connection count

Scenario Warm-up

Before Black Friday's fix, StayHub ran 6 app servers, each with an in-process pool of max 20, connecting straight to PostgreSQL (max_connections = 200, of which 15 are reserved for admin, replication and monitoring).

A rolling deploy starts new servers before stopping old ones, 3 at a time. Pools open connections as traffic arrives, and at the busiest moment each pool is full.

Trace the connection count through the deploy (before, during the worst overlap, after) and say whether and when errors appear.

Step 2 · Complete

Finish the connection budget

Estimation Practice

StayHub is adding a second service, the Hotel Partners API, which will share the same PostgreSQL (max_connections = 200, 8 cores). Lina's budget has gaps. Fill in A, B and C, showing your math.

Consumer	Rate	Avg hold	Busy (Little's law)	Pool given
Guest app (via PgBouncer)	1,200 queries/s	5 ms	6	30
Partners API (via PgBouncer)	300 queries/s	20 ms	A	B
Migrations, admin	-	-	-	10
Replication, monitoring	-	-	-	10
Total real connections				C

Then say whether the database has room, and one risk to watch.

Step 3 · Solve

Webhooks from serverless functions

Design Practice

The Hotel Partners team wants to receive channel-manager webhooks (availability updates) with serverless functions. Partners push in bursts: up to 1,500 webhooks in 10 seconds when a big chain updates its rates. Each webhook runs 3 short queries (~4 ms each) in one transaction.

A first prototype opens a PostgreSQL connection directly inside each function. In a test burst, the database hit "too many clients" and the partner's system started retrying, making it worse.

Design how these functions should talk to the database, with numbers.

Step 4 · Decide

Session features vs transaction mode

Decision Stretch

After moving to PgBouncer in transaction mode, two features break:

The finance export runs SET statement_timeout = '10min' at the start of its session, then runs several long queries. Now some of them time out at the default 30 s.
A nightly cleanup job uses pg_advisory_lock(42) (session-level) so two copies never run at once. Last night, two copies ran together.

Options: (A) switch the whole pooler back to session mode; (B) rewrite both features for transaction mode; (C) give these two jobs a direct connection or a separate session-mode pool, outside the main one.

Write a decision: "We chose X over Y because…, accepting the risk of…", covering both features.

Explain it yourself

Your team wants to raise max_connections from 200 to 500 because of 'too many clients' errors. Explain why that's risky and what you'd do instead.

0 words