1. The 2 AM Incident: When the Connection Pool Vanished
At 2:15 AM, PagerDuty alarms went off relentlessly. Every analytics endpoint started returning 504 Gateway Timeout. Application pods froze completely, container memory hit 95%, and logs were flooded with a dreaded error:
sqlalchemy.exc.TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30.00 (Background on this error at: https://sqlalche.me/e/20/3o7r)Our backend was built with FastAPI running async alongside SQLAlchemy 2.0. During daytime traffic of around 60 req/s, everything ran smoothly. However, right at 2:00 AM, a scheduled sync job for 50,000 orders fired concurrently with a massive burst of incoming partner webhooks. All 15 connections in the pool were drained in just 25 seconds. I quickly SSH’d in to check pg_stat_activity on PostgreSQL. More than 10 sessions were stuck in the idle in transaction state for several minutes.
2. Why Does Async Code Still Bottle Up Database Connections?
Digging into git history and tracing call stacks revealed three critical architectural flaws:
Mistake 1: Leaking Sessions Due to Sloppy Lifecycle Management
Several background tasks created an AsyncSession manually without using the async with context manager. When functions encountered transient network failures mid-execution, execution bailed out without closing the session. Connections hung indefinitely until hitting the server’s 30-second timeout, leaving no available slots for incoming API requests.
Mistake 2: Falling into the MissingGreenlet Trap with Lazy Loading
This is the most classic pitfall when introducing async into an ORM. When accessing an unloaded relationship (such as calling order.user), SQLAlchemy attempts to trigger a synchronous query under the hood. But on an async event loop, this immediately raises an exception:
sqlalchemy.exc.MissingGreenlet: greenlet_spawn has not been called in this context; can't call await_only() here.A developer had previously hotfixed this by wrapping the query inside asyncio.to_thread. This hack turned the async worker into a blocking sync thread. Consequently, service throughput plummeted from 1,100 req/s down to a pitiful 35 req/s.
Mistake 3: Memory Bloat Caused by Eager all() Ingestion
The batch job used result.scalars().all() to retrieve all 50,000 records at once. Python was forced to instantiate 50,000 ORM model instances simultaneously, blowing through over 1.8GB of RAM in seconds. Even worse, the database connection remained held throughout the entire 40-second processing loop instead of being returned to the pool immediately after fetching the data.
3. Common Band-Aid Fixes and Their True Costs
Before re-architecting the session lifecycle, the team evaluated several common temporary fixes:
- Maxing out
pool_sizeandmax_overflow: Boosting the configuration from 15 to 80 connections only delayed the crash by roughly 3 minutes. Each Postgres connection consumes around 5–10MB of RAM. Spawning excessive connections pushed the database server into memory saturation, hittingmax_connectionsand dragging down the co-located payment service as well. - Offloading Tasks to ThreadPoolExecutor: Falling back to a traditional synchronous engine distributed across threads burned excessive CPU overhead on OS thread context switching, nullifying the lightweight benefits of AsyncIO.
- Writing Raw SQL with the asyncpg Driver: While raw
asyncpgquery performance is outstanding, abandoning the ORM meant losing full type hint coverage, table relationship models, and Alembic’s autogenerated migrations.
4. The Permanent Solution: Optimizing AsyncSession and Queries in SQLAlchemy 2.0
To achieve peak performance while retaining the ORM’s productivity, we need to standardize three core areas: engine setup, session lifecycle, and relationship loading strategies.
Step 1: Configure the Engine and Factory to Guarantee Connection Cleanup
Use the asynchronous driver asyncpg and ensure expire_on_commit=False is enabled. This prevents object attributes from being expired post-commit, completely eliminating unexpected lazy-load triggers outside the active session:
from collections.abc import AsyncGenerator
from sqlalchemy.ext.asyncio import (
create_async_engine,
async_sessionmaker,
AsyncSession,
)
DATABASE_URL = "postgresql+asyncpg://app_user:[email protected]:5432/production_db"
engine = create_async_engine(
DATABASE_URL,
pool_size=10,
max_overflow=15,
pool_timeout=30,
pool_recycle=1800, # Recycle connections every 30 minutes to prevent silent socket disconnects
pool_pre_ping=True, # Verify connection health before checkout
echo=False,
)
AsyncSessionFactory = async_sessionmaker(
bind=engine,
class_=AsyncSession,
autoflush=False,
expire_on_commit=False,
)
# Standard context manager for FastAPI dependencies or background workers
async def get_db_session() -> AsyncGenerator[AsyncSession, None]:
async with AsyncSessionFactory() as session:
try:
yield session
await session.commit()
except Exception:
await session.rollback()
raise
Step 2: Eliminate MissingGreenlet with Explicit Eager Loading
In an async environment, every relationship that will be accessed must be eagerly loaded upfront. Use joinedload for 1-to-1 (or N-to-1) relationships and selectinload for 1-to-N relationships:
from sqlalchemy import select
from sqlalchemy.orm import selectinload, joinedload
from models import User, Order, Profile
async def get_user_with_details(session: AsyncSession, user_id: int):
stmt = (
select(User)
.options(
joinedload(User.profile), # 1-to-1 relationship: perform a LEFT JOIN within a single query
selectinload(User.orders) # 1-to-N relationship: run a separate SELECT ... WHERE id IN (...) query, much faster
)
.where(User.id == user_id)
)
result = await session.execute(stmt)
return result.scalar_one_or_none()
All required data is fetched in a single await call. No hidden synchronous I/O is triggered, completely banishing the MissingGreenlet error.
Step 3: Stream Data Batches Using yield_per
For batch jobs processing tens of thousands of rows, switch to stream_scalars() combined with the yield_per() execution option:
async def process_batch_orders(session: AsyncSession):
stmt = (
select(Order)
.where(Order.status == "PENDING")
.execution_options(yield_per=1000) # Fetch in batches of 1,000 rows using a server-side cursor
)
result = await session.stream_scalars(stmt)
async for order in result:
order.status = "PROCESSED"
await session.commit()
The monitoring dashboard reflected immediate improvements after the refactor:
- Connection pool usage stabilized at 4 to 6 active connections during peak traffic.
- Worker memory footprint plummeted from 1.8GB to under 260MB when handling large datasets.
- API P99 response times dropped dramatically from 2,800ms down to 72ms.
TimeoutErrorexceptions were entirely eliminated.
