Unexpected Slow Performance in a FastAPI App with PostgreSQL during Bulk Inserts
I'm experiencing significant slowdowns in my FastAPI application when performing bulk inserts into a PostgreSQL database... I am using SQLAlchemy 1.4 and asyncpg for the database connection. The performance degrades notably when the number of records exceeds 1000, taking over 10 seconds for completion, but it works fine for smaller batches. Here's the code snippet for my bulk insertion logic: ```python from fastapi import FastAPI from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionmaker, session from sqlalchemy import insert DATABASE_URL = "postgresql+asyncpg://user:password@localhost/dbname" engine = create_async_engine(DATABASE_URL, echo=True) Base = declarative_base() app = FastAPI() class DataModel(Base): __tablename__ = 'data' id = Column(Integer, primary_key=True) value = Column(String) async def bulk_insert(data_list): async with AsyncSession(engine) as session: stmt = insert(DataModel) await session.execute(stmt, data_list) await session.commit() @app.post("/bulk-insert") async def bulk_insert_endpoint(data_list: List[Dict[str, Any]]): await bulk_insert(data_list) return {"message": "Data inserted successfully!"} ``` I've tried using `session.execute()` with different batching sizes but found no significant performance improvement. Additionally, I've confirmed that the database indices are correctly set, and there are no constraints that would cause locking. I also ran `EXPLAIN ANALYZE` on the insert statement, but it didn't show anything that indicated why the performance drops so drastically. The network latency is also minimal since both the app and the database are hosted on the same server. Is there a recommended way to optimize bulk inserts in FastAPI with PostgreSQL? Any configurations or code adjustments that could help improve the performance would be greatly appreciated! Iām also open to using alternative libraries if needed. My development environment is Linux. My team is using Python for this mobile app. Any advice would be much appreciated.