Hermes Wiki
Developer/Languages/Python/DatabaseAccess/Fundamentals/sqlalchemy-2-async-and-connection-pooling

SQLAlchemy 2.0 Async and Connection Pooling

Concept

SQLAlchemy 2.0 unified its sync and async APIs around the same Core/ORM constructs, but running it under asyncio (via create_async_engine + AsyncSession) changes how connections are pooled and released compared to classic sync SQLAlchemy 1.x. A sync engine defaults to QueuePool: a fixed-size pool of DBAPI connections handed out on checkout and returned on close. An async engine instead defaults to AsyncAdaptedQueuePool — the same queue semantics, adapted so checkout/checkin cooperate with the event loop instead of blocking a thread. The pool still holds real DBAPI connections (via an async driver like asyncpg or psycopg's async mode); what changes is who is allowed to wait on them and how.

Two pool knobs matter most in practice: pool_size (steady-state connections kept open) and max_overflow (extra connections allowed temporarily above pool_size under burst load, closed once returned). A request that can't get a connection within pool_timeout seconds raises TimeoutError rather than hanging forever — a deliberate fail-fast rather than unbounded queuing. pool_recycle forces connections older than N seconds to be discarded and reopened, which matters for databases (and load balancers/proxies like PgBouncer or cloud DB endpoints) that silently drop idle connections after a timeout the driver has no way to detect until it tries to use one.

Tradeoffs

Approach Benefit Cost
Sync SQLAlchemy + QueuePool, one connection per request thread Simple mental model, mature tooling, works with any DBAPI driver Each idle-but-blocked request thread ties up an OS thread; scaling concurrency means scaling threads (and thread-pool size), not cheap under high fan-out
Async SQLAlchemy (AsyncSession) + AsyncAdaptedQueuePool A blocked-on-I/O request doesn't hold an OS thread — the event loop serves other coroutines while awaiting a query Every ORM call site must be awaited correctly; lazy-loading a relationship outside an active session/await context raises MissingGreenlet instead of silently working like sync SQLAlchemy would
App-level pool only (SQLAlchemy pool, no external pooler) One less moving part to operate pool_size is per-process — N app workers × pool_size connections must fit under the database's real connection ceiling, which is easy to blow past when autoscaling adds workers
App-level pool + PgBouncer/RDS Proxy in front Decouples "connections the app thinks it needs" from "connections Postgres actually opens," survives worker autoscaling and connection storms after a redeploy Adds a network hop and an extra service to operate; PgBouncer's transaction-pooling mode breaks session-level features (advisory locks, SET statements, prepared statement caching) unless the app avoids them

When to use / when not to

  • Use create_async_engine/AsyncSession when the service is already asyncio-native (FastAPI, aiohttp) and most of its I/O is already async — mixing sync SQLAlchemy calls into an async framework blocks the event loop on every query.
  • Don't migrate an existing sync SQLAlchemy 1.x codebase to async purely for a hoped-for throughput win if the service is CPU-bound or already thread-pool-backed (e.g. classic WSGI/Flask under gunicorn sync workers) — the sync model is simpler and the win doesn't materialize without genuinely concurrent I/O-bound load.
  • Put a connection pooler (PgBouncer, RDS Proxy) in front once the deployment can scale worker count independently (Kubernetes HPA, Fargate autoscaling) — a fixed pool_size per worker stops being a safe assumption the moment worker count is elastic.
  • Tune pool_recycle to a value comfortably under whatever idle-connection timeout sits between the app and the database (managed Postgres services and load balancers commonly default to 300-600s) — without it, the first query after an idle period intermittently fails with a stale-connection error that looks like a flaky database.

Common pitfall

Sizing pool_size by guessing a "reasonable" number instead of computing it from the real ceiling: (number of app workers) × (pool_size + max_overflow) must stay under the database's max_connections minus headroom for admin/replication connections and any other services sharing the database. This is invisible in local dev (one worker, low concurrency) and only surfaces in production during a deploy or autoscaling event, when connection counts spike across every worker simultaneously and the database starts rejecting new connections — the classic "worked in staging, fell over during the release" incident shape.

Engineering Lens

The interesting design question isn't "sync or async SQLAlchemy" in the abstract — it's whether the rest of the request path is already async. An async ORM layer bolted onto an otherwise-sync service buys nothing and adds the MissingGreenlet-class footguns for free. The pooling question is the more universal one: a connection pool is a scarce shared resource with a hard ceiling set by the database, not the application, and its size must be reasoned about as a fleet-wide budget (workers × pool_size) rather than a per-process default left at whatever the library ships. That's the same failure mode as an under-provisioned thread pool or semaphore anywhere else in a distributed system — it just shows up as TimeoutError: QueuePool limit exceeded instead.

Sources

Hermes Wiki