Configure database connection pool and Uvicorn workers for concurrent request handling #680
Description
Summary
The API hits SQLAlchemy's default QueuePool limit (pool_size=5, max_overflow=10) when many concurrent requests arrive (e.g. loading the MaveMD page), causing connection exhaustion. This is compounded by running Uvicorn with a single worker process.
Changes needed
Connection pool — Pool size and max overflow should be made configurable via DB_POOL_SIZE and DB_MAX_OVERFLOW environment variables in src/mavedb/db/session.py, with pool_pre_ping=True to avoid stale connections.
Deployment config — Set env vars and add --workers to Uvicorn in each environment:
- Staging (db.t3.small, ~45 max connections):
DB_POOL_SIZE=5, DB_MAX_OVERFLOW=10, --workers 2 - Production (db.m6g.large, ~683 max connections, 2 servers):
DB_POOL_SIZE=10, DB_MAX_OVERFLOW=20, --workers 4