To the poor lambs who believe that "asynchronous is fast": A walk through hell with SQLAlchemy 2.0 + FastAPI
"If we just write `async def`, our API will be extremely fast."
For those of you who believe this and have no doubts, I will tell you one thing. How many users are you going to let that code lead astray in production? **
I once went crazy and implemented full async right after a new feature was released, instantly depleted the connection pool, and then rolled back in a cold sweat amidst a barrage of late-night monitoring alerts. The countless `ConnectionTimeout` characters that appeared in the DB log at that time still appear in my nightmares.
Async isn't magic. It's a "double-edged sword" of simply IO multiplexing. If you can't get it right, put down that keyboard now and get back to syncing.
1. “Time Bomb” called Dependency (DI)
It is common practice to manage sessions using FastAPI's `Depends`, but many people do not understand why `AsyncSessionLocal` is used.
# database.py
from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker, AsyncSession
# Don't decide pool_size arbitrarily. Talk to your DB's max_connections.
engine = create_async_engine(
DATABASE_URL,
pool_size=20,
max_overflow=10,
pool_pre_ping=True # If you don't include this, you will die when restarting the DB
)
AsyncSessionLocal = async_sessionmaker(
engine,
expire_on_commit=False, # If you set this to True, a re-query will run the moment you access the attribute after committing, and you will see hell
class_=AsyncSession
)
async def get_db():
async with AsyncSessionLocal() as session:
try:
yield session
await session.commit()
exception Exception:
await session.rollback()
raise
finally:
await session.close()
What happens if you forget `expire_on_commit=False`? SQLAlchemy considers objects to be "expired" after a commit. In that state, the moment you touch the attribute with another function, a SELECT statement is automatically issued behind the scenes. If you run it outside of an async context, your app will immediately crash with a `MissingGreenlet` error. While you're sleeping, users are bombarded with 500 errors.
2. “Excessive tombstone” called Repository pattern
"Create a Repository layer for a clean architecture"? Don't say anything bad, just stop.
SQLAlchemy's ORM has a powerful feature called `Identity Map` (manages the same objects uniquely). Wrapping it pointlessly in the Repository layer and reusing sessions all over the place kills this benefit. As a result, memory efficiency decreases and debugging difficulty increases.
If it's a personal project, ``Repo should be thin and direct''** is justice.
class UserRepository:
def __init__(self, db: AsyncSession):
self.db = db
async def get_user_with_posts(self, user_id: int):
# Load necessary data "explicitly"
stmt = select(User).where(User.id == user_id).options(selectinload(User.posts))
result = await self.db.execute(stmt)
return result.scalar_one_or_none()
Don't feel pleasure in abstraction. If you don't have complex business logic, a Repository is sufficient as just a "place for queries."
3. Why is the N+1 problem fatal if it is “asynchronous”?
With synchronous processing, even if N+1 occurs, you can just say, ``Well, it's a little slow.'' However, if you do this asynchronously, the IO waits to the DB will overlap for the number of loops, which will quickly eat up the connection pool.
Check out the hellish log when you forget `selectinload`:
[DEBUG] SELECT ... FROM user WHERE id = 1;
[DEBUG] SELECT ... FROM posts WHERE user_id = 1; <-- End of N loop
[DEBUG] SELECT ... FROM posts WHERE user_id = 2;
[DEBUG] SELECT ... FROM posts WHERE user_id = 3;
...
[ERROR] sqlalchemy.exc.TimeoutError: QueuePool limit of size 20 overflow 10 reached
The only way to stop this ``flood of mysterious queries'' is to not believe in lazy loading in ORM and to write ``what to load'' into the code using `selectinload` and `joinedload`.
4. Don't say it's "faster" without a profiler
I'll show you the results I measured in a local container environment (Docker + PostgreSQL). This is the average processing time for 100 requests measured with `cProfile` and `pyinstrument`.
| Processing details | Synchronous (psycopg2) | Asynchronous (asyncpg) |
|---|---|---|
| Simple PK search (100 times) | 1.25s | 0.82s |
| Heavy aggregation + external API cooperation | 4.80s | 1.10s |
The conclusion is clear. Using async for anything other than "processing dominated by IO waits" will only increase overhead. If CPU-intensive computational processing is involved, desynchronization can actually be harmful.
Visualize bottlenecks by running `pyinstrument` to see where your app is stuck. Writing code based on intuition is not a professional job.
Finally
Asynchronous processing is a tool to take your code to the next level, but it's also the knife that kills careless engineers.
- Understand the life cycle of `get_db`.
- Don't forget the `expire_on_commit` trap.
- Don't slack off on your data loading strategy (`selectinload`).