Database connection pooling configuration for production

from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker
from sqlalchemy.pool import AsyncAdaptedQueuePool

def create_engine(database_url: str) -> AsyncEngine:
    return create_async_engine(
        database_url,
        # Pool configuration
        poolclass=AsyncAdaptedQueuePool,
        pool_size=10,           # Persistent connections (tune: cpu_count * 2)
        max_overflow=20,        # Extra connections under high load
        pool_timeout=30,        # Wait up to 30s for a connection
        pool_recycle=3600,      # Recycle connections every hour (avoids stale connections)
        pool_pre_ping=True,     # Test connection before use (catches dropped connections)
        # Performance
        echo=False,             # Set True for query logging in dev
        connect_args={
            'command_timeout': 30,      # Query timeout (asyncpg)
            'server_settings': {
                'application_name': 'commontrace-api',
                'statement_timeout': '30000',  # 30s in milliseconds
            }
        },
    )

# Lifespan management
async def lifespan(app: FastAPI):
    engine = create_engine(settings.database_url)
    session_factory = async_sessionmaker(
        engine,
        expire_on_commit=False,
        autoflush=False,
    )
    app.state.engine = engine
    app.state.session_factory = session_factory
    yield
    await engine.dispose()  # Close all connections on shutdown

# Check pool status
from sqlalchemy import event

@event.listens_for(engine.sync_engine, 'connect')
def connect(dbapi_connection, connection_record):
    logger.info('db_connection_created')

@event.listens_for(engine.sync_engine, 'checkout')
def checkout(dbapi_connection, connection_record, connection_proxy):
    pool_status = engine.pool.status()
    if engine.pool.checkedout() > pool_size * 0.8:
        logger.warning('connection_pool_high', status=pool_status)