The Database Isn’t Slow, It’s Full
It’s 2 AM. The app worked fine at dinner. Now every request returns a 500, the logs are a wall of timeouts, and docker stats says Postgres is using almost no CPU. The database isn’t busy. It’s full. It ran out of connection slots, and your app has been politely knocking on a locked door for ten minutes.
That’s a connection storm. The cause is almost never one bug. It’s arithmetic (pool size times workers times replicas), a restart that makes every container ask for connections at the same instant, and retry loops that turn a slow database into a dead one. Each piece has a config you can paste below. The fix is boring: do the math, set timeouts, retry with jitter, and stagger your boots.
The Math Nobody Does Until 2 AM
Postgres ships with max_connections = 100 (the docs say “typically” 100, because initdb can pick lower on kernels that can’t support it). Of those, superuser_reserved_connections (default 3) are held back for superusers. So your app gets about 97 slots, and everything else shares them: your migration tool, your backup job, the Grafana datasource, and that psql session you left open on Tuesday.
Now count what your app can ask for. Say a home-lab service runs 3 containers, each with 4 gunicorn workers, each worker with its own SQLAlchemy pool. SQLAlchemy’s create_engine defaults are pool_size=5 and max_overflow=10, so each worker can hold up to 15 connections. Bump pool_size to 10 because “more is faster” and you get 20 per worker.
containers x workers x (pool_size + max_overflow) = worst case3 x 4 x (5 + 10) = 180 (defaults)3 x 4 x (10 + 10) = 240 (the "I'll just raise it" config)Postgres gives you: 100 - 3 reserved = 97Both numbers blow past 97. Day to day you’d never notice, because most workers sit at one or two connections. Then a slow query piles up requests, every worker grows into its overflow, and the first 97 connections win. Number 98 gets an error.
Pools are per process. Gunicorn workers fork, and each one builds its own engine. Ten containers with two workers each is twenty pools, not ten. This is the number people forget.
MySQL and MariaDB have the same shape of problem. max_connections defaults to 151 in MySQL, and a refused client sees ERROR 1040 (08004): Too many connections. Accounts with CONNECTION_ADMIN (or the older SUPER) get one extra slot so an admin can still log in and kill sessions. Same math, same fix.
How to See It Happening
Postgres tells you exactly what’s going on, if you ask. Run this from a superuser session (or the reserved slots will save you):
SELECT count(*), stateFROM pg_stat_activityGROUP BY stateORDER BY count(*) DESC;A healthy app shows mostly idle with a few active. A storm shows active pinned high with long queries behind it, or a pile of idle in transaction, which is the nastiest one. A connection sitting in an open transaction holds locks and blocks vacuum, and its pool slot is dead weight.
Compare against the ceiling:
SHOW max_connections;SHOW superuser_reserved_connections;SELECT count(*) AS in_use FROM pg_stat_activity WHERE backend_type = 'client backend';When you hit the wall, the client sees one of two FATAL messages. I checked the Postgres source for both (branches 16 and 18):
FATAL: sorry, too many clients alreadyFATAL: remaining connection slots are reserved for roles with the SUPERUSER attributeFATAL: remaining connection slots are reserved for roles with privileges of the "pg_use_reserved_connections" roleThe first means every slot is gone. The second and third mean ordinary users are locked out and only the reserved slots remain. The wording is new: in Postgres 15 and earlier, the second message read “remaining connection slots are reserved for non-replication superuser connections”. Postgres 16 added a reserved_connections setting (default 0) and the pg_use_reserved_connections role, which is why there are two variants now. Grep your logs for either string. If you find one, you’ve found your storm.
The Restart Thundering Herd
The sneaky one comes next. Your database blips for 30 seconds (a restart, a backup stall, a disk hiccup). Every pool in every worker sees dead connections. When the database comes back, every worker tries to rebuild its pool at the same moment, and the ones that were mid-request retry on top of that. A database that just woke up gets hit by the full worst-case count in one second.
The same thing happens on docker compose up. All containers start together, each worker starts serving, and the first burst of requests fills every pool at once. (SQLAlchemy and node-postgres create connections lazily, so the herd arrives with the first traffic. Pools with an eager minimum size, like HikariCP or pgx’s MinConns, open them in the background at boot.)
Two cheap defenses. First, make the app wait for a healthy database. Compose supports this directly:
services: db: image: postgres:18 environment: POSTGRES_PASSWORD: example command: ["postgres", "-c", "max_connections=100"] healthcheck: test: ["CMD-SHELL", "pg_isready -U postgres"] interval: 5s timeout: 3s retries: 10 start_period: 10s
api: image: example/api:latest deploy: replicas: 3 depends_on: db: condition: service_healthy environment: DATABASE_URL: postgresql+psycopg://postgres:example@db:5432/postgres STARTUP_JITTER_MAX: "10"condition: service_healthy holds the api containers until pg_isready passes. It fixes the cold-boot case where the app starts before Postgres has finished initializing. It does nothing for a database that restarts while the app is running, so you still need retries (next section).
Second, stagger. Three replicas starting within the same second is a herd, even with a healthy database. A random sleep before the first query spreads them out:
import osimport randomimport time
def startup_jitter() -> None: """Sleep a random 0..N seconds so replicas don't open pools in lockstep.""" max_s = float(os.environ.get("STARTUP_JITTER_MAX", "0")) if max_s > 0: time.sleep(random.uniform(0, max_s))Call it once before you warm the pool. It’s ugly. It works.
Retry Amplification: How a Slow DB Becomes a Dead One
The database slows down. Queries that took 20 ms now take 2 seconds. Requests time out, and your code does what the tutorial said: catch the error, retry immediately. Now each failed request becomes two, then three. The database was running at 100% and now it’s getting 300%. It’s like a tow truck stuck in traffic that gets a second tow truck sent to help.
Retries are fine. Immediate retries with no limit are not. The standard fix is exponential backoff with jitter, and the AWS Architecture Blog post “Exponential Backoff And Jitter” is the reference. Their finding: plain exponential backoff still leaves clients retrying in synchronized waves, while adding random jitter spreads the calls out, cutting the total call count by more than half in their 100-client simulation. The “full jitter” variant picks a random sleep between zero and the capped exponential value:
sleep = random_between(0, min(cap, base * 2 ** attempt))In Python:
import randomimport time
from sqlalchemy.exc import OperationalError, TimeoutError as PoolTimeout
def with_retry(fn, attempts=5, base=0.1, cap=5.0): for attempt in range(attempts): try: return fn() except (OperationalError, PoolTimeout): if attempt == attempts - 1: raise # out of budget: fail loudly, don't loop forever time.sleep(random.uniform(0, min(cap, base * 2 ** attempt)))
# usagerows = with_retry(lambda: session.execute(text("SELECT 1")).all())Three details matter. The attempt count is capped, so a dead database produces an error, not an infinite loop. The sleep is random from zero, so 100 workers don’t wake up together. And only connection-level errors are retried: don’t retry a unique-constraint violation, or you’ll just fail the same way five times. Also retry only idempotent work, or a retry after a timeout can run a write twice.
Timeouts: Four Knobs That Matter
Without timeouts, a stuck query holds a pool slot forever. Without pool timeouts, requests queue forever behind it. Four timeouts break the chain at different points.
1. Pool acquire timeout. How long a request waits for a free connection from the pool. SQLAlchemy calls it pool_timeout (default 30 seconds). Thirty seconds is a long time to hold an HTTP request. Set it below your load balancer’s timeout so the app fails fast with a clear error.
2. Connect timeout. How long to wait for the TCP and auth handshake. node-postgres defaults connectionTimeoutMillis to 0, meaning no timeout, so a black-holed database hangs the request indefinitely. Set it.
3. statement_timeout. Postgres aborts any statement running longer than this. Default is 0 (disabled). A runaway query otherwise holds a slot until someone kills it by hand.
4. idle_in_transaction_session_timeout. Postgres terminates sessions that sit idle inside an open transaction. Default is 0 (disabled). The docs point out that these sessions hold locks and block vacuum from cleaning up dead rows.
You can set the last two per role instead of per connection, which survives any client bug:
ALTER ROLE app_user SET statement_timeout = '30s';ALTER ROLE app_user SET idle_in_transaction_session_timeout = '60s';The Postgres docs advise against setting statement_timeout globally in postgresql.conf, because it would also hit your migrations and backups. A per-role setting avoids that. Pick numbers from your slowest legitimate query, not from vibes.
Pool Configs You Can Paste
Here’s SQLAlchemy with every knob set on purpose. Defaults are from the SQLAlchemy 2.0 docs: pool_size=5, max_overflow=10, pool_timeout=30, pool_recycle=-1 (never), and pool_pre_ping off.
from sqlalchemy import create_engine
engine = create_engine( "postgresql+psycopg://app_user:secret@db:5432/app", pool_size=4, # steady-state connections per process max_overflow=2, # burst headroom, closed again when returned pool_timeout=5, # seconds to wait for a free connection, then raise pool_pre_ping=True, # test the connection on checkout, replace if dead pool_recycle=1800, # replace connections older than 30 min connect_args={ "connect_timeout": 5, "options": "-c statement_timeout=30000 -c idle_in_transaction_session_timeout=60000", },)Now redo the earlier math: 3 containers x 4 workers x (4 + 2) = 72. That fits under 97 with room for backups and a person with psql. We shrank the pools and the app got more stable, because a small pool queues requests briefly instead of piling work onto the database.
pool_pre_ping costs one cheap round trip per checkout and saves you from handing a dead connection to a request after a database restart. pool_recycle matters when something between the app and the database (a firewall, a NAT gateway) silently drops idle connections.
The same idea in node-postgres. The defaults from its docs: max is 10, idleTimeoutMillis is 10000, and connectionTimeoutMillis is 0 (no timeout):
import pg from "pg";
export const pool = new pg.Pool({ host: "db", user: "app_user", password: process.env.DB_PASSWORD, database: "app", max: 6, // hard cap per process idleTimeoutMillis: 30000, // close idle clients after 30 s connectionTimeoutMillis: 5000, // fail if no client within 5 s statement_timeout: 30000, // passed through to each client idle_in_transaction_session_timeout: 60000,});
pool.on("error", (err) => { console.error("idle client error", err.message);});The two Postgres timeouts work here because pool options are passed through to the client config, where node-postgres documents both. Note that node-postgres runs one process per container unless you cluster it, so the multiplier is just replicas times max.
Leaks: The Storm That Starts Slowly
Not every storm is a burst. Some are a slow leak. A code path takes a connection from the pool and never returns it: an exception skips the cleanup, or a session stays open across a long network call. Each leaked connection is one fewer slot. The pool shrinks by one an hour until, at 2 AM, it hits zero and every request waits pool_timeout seconds before failing.
The symptom is a connection count that climbs and never falls, even when traffic drops to nothing. Check it twice, ten minutes apart, at a quiet time. If the idle count only goes up, you have a leak.
The fix is structural. Use the context manager so the connection goes back even when the code raises:
from sqlalchemy import text
def get_user(engine, user_id: int): with engine.connect() as conn: # returned to the pool on exit, even on error return conn.execute( text("SELECT id, name FROM users WHERE id = :id"), {"id": user_id} ).first()Keep the transaction short too. Do the HTTP call to the payment API before you open the connection, not inside the block. A connection held during a slow third-party call is a connection the rest of your app can’t use, and idle_in_transaction_session_timeout is the backstop that kills the worst offenders.
Where a Pooler Fits
If the arithmetic still doesn’t fit (many small services, serverless functions, or a pile of apps sharing one database), shrinking pools only goes so far. A server-side pooler sits in the middle, accepts thousands of client connections, and multiplexes them over a few dozen real ones. PgBouncer is the usual choice, and I covered the modes and config in PgBouncer for Connection Pooling. Keep the app-side limits and timeouts anyway. A pooler moves the wall, it doesn’t remove it.
The Checklist
- Multiply: containers x processes x (pool_size + max_overflow). Keep it under
max_connectionsminus the reserved slots minus headroom for humans and jobs. - Set a short pool acquire timeout so requests fail fast.
- Set connect timeouts. Zero means forever.
- Set
statement_timeoutandidle_in_transaction_session_timeoutper role. - Retry with capped exponential backoff and full jitter, on idempotent work only.
- Gate boot on a healthcheck and add startup jitter.
- Watch
pg_stat_activitygrouped bystate, and alert before you hit the ceiling.
Your 2 AM self will thank you.
Common Questions
How many connections can Postgres handle?
Postgres handles 100 connections by default, set by max_connections, and 3 of those are reserved for superusers. Hardware limits the practical number, but raising the setting without reducing app pool sizes only moves the failure point. Most home-lab databases run fine with under 100 well-managed connections.
Should I just raise max_connections?
No, not as the first fix. Postgres allocates shared memory per allowed connection, and each active connection is a separate backend process competing for CPU and cache. Raising max_connections also needs a server restart. Shrink your app pools first, add a pooler if needed, then raise the limit deliberately.
What does “sorry, too many clients already” mean?
The error means Postgres has no free connection slots, so every backend slot is in use. The server rejects the new client with a FATAL error. Fix it by counting connections in pg_stat_activity, finding which service holds the most, and shrinking its pool or fixing its leak.
Does SQLAlchemy open all pool connections at startup?
No. SQLAlchemy’s QueuePool opens connections lazily, as requests ask for them, up to pool_size plus max_overflow. Connections above pool_size are closed when returned. Startup looks quiet, then the first traffic burst after a restart opens many connections at once.
Is the connection limit the same in MySQL and Postgres?
No. MySQL’s max_connections defaults to 151 (MariaDB matches), not 100, and a full server returns ERROR 1040 (08004): Too many connections. The same math applies: pool size times processes must fit under the limit. Accounts with CONNECTION_ADMIN or SUPER get one extra connection for diagnosis.
