implementing scaling a FastAPI application using PostgreSQL and Alembic for migrations
I'm optimizing some code but I'm currently working on a FastAPI application that interfaces with a PostgreSQL database... As the user base grows, I'm working with performance optimization, especially when executing background jobs that heavily rely on database interactions. I've set up Alembic for migrations, but I'm worried about the potential impact on the database while scaling. For instance, when trying to run concurrent background tasks using `asyncio`, I notice that the database locks up and I get errors like `psycopg2.errors.DeadlockDetected`. Here’s how I’ve set up my database session: ```python from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine from sqlalchemy.orm import sessionmaker DATABASE_URL = "postgresql+asyncpg://user:password@localhost/dbname" engine = create_async_engine(DATABASE_URL, echo=True) session_local = sessionmaker(bind=engine, class_=AsyncSession, expire_on_commit=False) ``` In my background task, I use: ```python async def background_task(): async with session_local() as session: async with session.begin(): # Perform some queries await session.execute(...) ``` I've tried adding connection pooling by tweaking the connection pool size in SQLAlchemy, but it doesn't seem to alleviate the locking issues. I also looked into using `asyncio.gather()` for concurrent execution but that only made the deadlocks worse. Is there a recommended approach to handle migrations and concurrent database access in a scalable FastAPI app? Should I consider switching to a different migration strategy, or is there a way to optimize the current Alembic setup to minimize conflicts? This issue appeared after updating to Python stable. Hoping someone can shed some light on this. This is happening in both development and production on Linux. I'm open to any suggestions.