Connection Pools in Async FastAPI: Sizing asyncpg + SQLAlchemy for a Real Product

Opening a PostgreSQL connection is expensive: a TCP handshake, TLS negotiation, authentication, and a forked backend process on the server holding several megabytes before your first query runs. Do that per request and a modest traffic spike becomes a connection storm — latency climbing with no CPU to blame, then outright refused connections once Postgres's own ceiling is hit. In a synchronous app that ceiling arrives slowly, one blocked thread at a time. In an async app it arrives faster and quieter: a single worker process can have hundreds of coroutines in flight, each perfectly happy to ask the database for a connection at the same instant.

The problem isn't "should I pool connections" — that part is settled. It's that a pool has knobs, the defaults hide assumptions that don't hold for an async workload, and the failure modes when those assumptions are wrong don't look like a database problem. They look like the application randomly getting slow, or a background task silently starving a live request of the connection it needed.

What a connection pool is

A pool is a set of already-open connections the application borrows and returns, rather than opening and closing a connection per unit of work. The mechanics fit in four rules:

• A request needing the database borrows a connection; when the work ends, the connection goes back to the pool — still open, ready for the next borrower.
• pool_size is how many connections the pool keeps alive permanently.
• max_overflow is how many extra connections may be opened under pressure; they're closed again when the spike passes. Real ceiling = pool_size + max_overflow.
• If all of them are busy, the next borrower waits (up to a timeout) — and that wait is your first symptom: latency climbing with no CPU to blame.

The ceiling matters because PostgreSQL has its own hard limit (max_connections, default 100), shared by every process that connects: each API worker's pool, migration tooling, one-off scripts, a developer's psql shell. Pools are a per-process promise, and Postgres holds the global budget — the pool can only ever manage its own slice of it.

The alternatives

Before settling on a specific pool configuration, the real options were:

• No pool — open a connection per request. Simplest to reason about, and fine for a script or a low-traffic internal tool. Under real concurrency it's the connection-storm scenario above: every request pays the full connection setup cost, and a burst of traffic can exceed max_connections outright, taking the whole service down rather than just slowing it.
• A large, generous pool sized for peak concurrency. Tempting in an async app where hundreds of coroutines can be "in flight" at once — but sizing the pool to the number of concurrent requests rather than the number of connections actually needed at any instant wastes Postgres's global budget and multiplies badly the moment there's more than one worker process.
• External pooling (PgBouncer or similar) in front of the database. Moves pooling to a single shared layer so many application processes stop competing for Postgres's own connection budget directly. The right answer at higher scale, but it's an extra piece of infrastructure to run and operate, and unnecessary complexity for a single service that can size its own pool sensibly.
• A small, tuned per-process pool with overflow headroom. Keeps a modest number of connections warm, allows a bounded burst above that, and stays well under the database's global limit even with a few processes running. More tuning upfront, but no extra infrastructure and no wasted capacity.

The fourth option was the right trade-off: the traffic pattern didn't justify running a separate pooling layer, but the default "just pick a big number" approach doesn't survive contact with an async workload and multiple worker processes doing the same math independently.

The decision

The pool was sized small and deliberately, then paired with settings that handle the specific ways connections go stale rather than just the happy path:

• A modest steady-state pool. An async worker interleaves many requests over few connections, because a connection is only borrowed for the milliseconds a query is actually running, not for the request's whole lifetime. A handful of warm connections comfortably covers normal traffic — sizing for "requests in flight" rather than "queries actually running concurrently" is the mistake that leads to oversized pools.
• Overflow headroom well below the database's global limit. A burst budget several times the steady-state size gives a hard per-process ceiling that still leaves room for migrations, scripts, and additional application instances without approaching Postgres's max_connections.
• Pre-ping on checkout. Before handing out a pooled connection, the pool sends a trivial round-trip. If the connection died while idle — a database restart, a firewall dropping a quiet TCP session — the pool discards it and opens a fresh one, instead of the next request being the one that discovers the connection is dead.
• A recycle age on every connection. No connection is reused past a fixed idle age, regardless of pre-ping. Infrastructure sitting between the application and the database — NAT gateways, load balancers — can silently kill long-idle connections; recycling means nothing in the pool is ever old enough for that to matter.
• Session objects that don't refresh themselves after commit. Left at its default, an ORM session re-queries an object's attributes the next time they're touched after a commit. In synchronous code that's just an extra query; in async code it can mean a query firing outside the session's own lifecycle, against a connection that's already been returned to the pool. Disabling that refresh keeps object access after commit safe and predictable.

Sizing the pool was only half the decision — the other half was making sure connections were borrowed and returned correctly. That meant one session per request, created fresh for the request and guaranteed to close when the request ends regardless of whether the handler succeeded or raised. Every data-access call downstream receives that same session, so the lifecycle stays simple: request in, session created, work happens, session closed, connection back in the pool. Nobody holds a connection across requests, and nobody has to remember to return one manually.

A connection pool doesn't fix bad connection discipline — it just gives bad discipline more rope before something breaks.

That discipline mattered most in the places specific to an async stack, where the failure modes are subtler than in synchronous code:

• Holding a session open across a slow await. An outbound call in the middle of database work — sending an email, calling another service — keeps the borrowed connection hostage for that call's full latency. The fix is structural: side effects that don't need the transaction run after commit, not interleaved with it.
• Background tasks competing with live requests for the same pool. A burst of post-commit background work, each opening its own session, draws from the same pool as incoming requests. The overflow headroom exists specifically to absorb that kind of burst without starving live traffic.
• Sharing one session across concurrent coroutines. An async session is not safe to use concurrently; running two queries on the same session at once through a gather-style pattern interleaves into corrupted state rather than an error you'd notice immediately. One session per logical flow, and any real parallel work gets its own session.
• Migration tooling using the same pooled engine as the running application. A one-shot migration process doesn't benefit from a warm pool of several connections — it needs exactly one connection, opened and closed cleanly, and configuring it separately from the application's engine avoids it either fighting for pool slots or leaving a connection open after it's done.

One trade-off worth naming honestly: none of these numbers are process-count-aware on their own. The pool ceiling multiplies by however many worker processes are running, so the same configuration that's comfortably under Postgres's limit with one worker can approach it with several. That math has to be redone by hand whenever the deployment topology changes — it isn't something the pool configuration protects against by itself.

The outcome

With a small steady-state pool and bounded overflow, the application stayed well under the database's connection limit even with room for migrations and multiple processes running side by side, instead of the two competing for the same budget. Pre-ping and connection recycling turned "the database restarted" and "a firewall silently dropped an idle connection" from user-facing errors into non-events the pool handled on its own. And because every request borrows exactly one session with a guaranteed close, connection leaks — the class of bug that shows up as a slow resource exhaustion hours after deploy rather than an immediate crash — became structurally difficult to introduce rather than something that had to be caught in review.