Tackling WebSocket Latency: My Dive into Async SQLAlchemy in FastAPI

Building real-time systems is always a balancing act. You want responsiveness, sure, but you also need reliability and the ability to scale. I learned this firsthand on a project recently: a real-time attendance management system. The goal was simple: GPS-verified student attendance, delivered via WebSockets, with sub-50ms sync for over 300 concurrent connections. It sounded ambitious, but FastAPI and WebSockets seemed like a perfect fit.

Everything looked good in development. Initial load tests with a few dozen simulated connections passed with flying colors. But once it hit production and hundreds of students started using it simultaneously, especially during peak check-in times, things started to wobble. Latency spiked. Attendance records were delayed. And, worst of all, WebSocket connections would occasionally drop without a clear error.

The Problem: Latency and Dropped Connections

The system was designed to allow students to mark their attendance through a web interface, which would then send their GPS coordinates to a FastAPI backend via a WebSocket. The backend would verify the location, record the attendance in a database, and then broadcast a confirmation back to the student and possibly to an admin dashboard.

The core promise was real-time feedback. Students needed to know their attendance was recorded instantly. Admins needed an up-to-the-second view. When the system started seeing high concurrent usage—think 200-300 students trying to check in within a 5-minute window—that promise shattered. Latency crept up, sometimes reaching several seconds. Users would see 'connecting...' messages, or worse, their attendance wouldn't register, forcing retries.

This wasn't just an inconvenience; it was a critical failure for a system built around real-time accuracy.

The Investigation

My first step was to check the usual suspects. I logged into the Linux VPS hosting the application. CPU usage wasn't maxed out, but it was higher than expected given the actual database writes. RAM was fine. Network I/O seemed stable enough. No obvious resource starvation at the OS level.

Next, I dug into the application logs. FastAPI was logging WebSocketDisconnect events, but these were largely symptoms, not causes. They told me connections were dropping, but not why they were dropping. There were no immediate application-level exceptions or traceback dumps indicating a bug in the code itself, which was frustrating.

I started thinking about the architecture. FastAPI is asynchronous. WebSockets are inherently asynchronous. Python's asyncio is designed for high concurrency with I/O-bound tasks. So, what could be blocking? My mind immediately went to the database.

The system used SQLAlchemy with a MySQL backend. Each attendance submission involved a database write (inserting a new attendance record) and sometimes a read (verifying student ID or class schedule). Under heavy load, hundreds of these operations could be hitting the database simultaneously.

I suspected database contention. Not necessarily that the database itself was slow, but how the application was interacting with it. I recalled how FastAPI's dependency injection often provides a database session, typically using yield to manage its lifecycle. If my database operations within the WebSocket handler were synchronous, even though the handler itself was async def, they would block the asyncio event loop.

Imagine a single lane highway. Even if cars (requests) are arriving asynchronously, if the toll booth (database operation) takes a long time and only one car can pass at a time, you get a traffic jam. This was the most plausible explanation for the latency spikes and subsequent connection drops (due to timeouts or the server being too busy to respond).

The Root Cause: Synchronous SQLAlchemy Blocking the Event Loop

My suspicion was correct. While FastAPI handlers were async def, the SQLAlchemy setup was largely synchronous. I was using create_engine and sessionmaker without the asyncio extensions. This meant that every session.add(), session.commit(), and session.refresh() call, even if wrapped in an async def function, was a blocking I/O operation. It was effectively saying, "Hey event loop, just wait here while I talk to the database," for every single request.

With 300 concurrent WebSocket connections, if even a fraction of them hit the database synchronously, the event loop would grind to a halt. Other WebSocket connections, waiting for their turn on the loop, would time out, leading to the WebSocketDisconnect errors and the frustrating user experience.

This is a classic asyncio pitfall: having an async function doesn't automatically make everything inside it non-blocking. Any traditional blocking I/O call will still block the event loop unless explicitly offloaded to a thread pool (e.g., via run_in_threadpool) or, preferably, replaced with an asynchronous equivalent.

The Solution: Migrating to Async SQLAlchemy

The fix was clear: I needed to make the database interactions truly asynchronous. SQLAlchemy provides sqlalchemy.ext.asyncio specifically for this purpose. This extension allows SQLAlchemy to work seamlessly with asyncio by using asynchronous database drivers like asyncpg (for PostgreSQL) or aiomysql (for MySQL).

Here's how I refactored the database setup and usage:

1. Update Database Engine and Session

First, I changed the engine creation to use create_async_engine and the session factory to AsyncSession.

Old (Synchronous-like) Setup:

# database.py (old)
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, declarative_base

SQLALCHEMY_DATABASE_URL = "mysql+mysqlconnector://user:password@host/dbname" # Example

engine = create_engine(SQLALCHEMY_DATABASE_URL)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()

def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

New (Asynchronous) Setup:

# database.py (new)
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
from sqlalchemy.orm import sessionmaker, declarative_base
from typing import AsyncGenerator

# Note: Use an async driver, e.g., 'mysql+aiomysql' for MySQL
SQLALCHEMY_DATABASE_URL = "mysql+aiomysql://user:password@host/dbname" # Example

# create_async_engine is crucial here
async_engine = create_async_engine(
    SQLALCHEMY_DATABASE_URL,
    echo=False, # Set to True for SQL logging
    pool_size=10, # Configure connection pooling
    max_overflow=20 # Allow for temporary overflow connections
)

AsyncSessionLocal = sessionmaker(
    async_engine,
    class_=AsyncSession,
    expire_on_commit=False
)
Base = declarative_base()

async def get_async_db() -> AsyncGenerator[AsyncSession, None]:
    async with AsyncSessionLocal() as session:
        yield session

Notice the mysql+aiomysql prefix for the URL, indicating an asynchronous MySQL driver. For PostgreSQL, it would be postgresql+asyncpg.

2. Update Database Operations in Handlers

Next, I had to ensure all database interactions within my async def WebSocket endpoints and other API routes were properly awaited.

Old (Blocking) Usage:

# main.py (old, simplified)
from fastapi import APIRouter, Depends, WebSocket, WebSocketDisconnect
from sqlalchemy.orm import Session
from .database import get_db, SessionLocal # assuming old setup

router = APIRouter()

@router.websocket("/ws")
async def websocket_endpoint(websocket: WebSocket, db: Session = Depends(get_db)):
    await websocket.accept()
    try:
        while True:
            data = await websocket.receive_json()
            # This 'db' object is synchronous, blocking the event loop
            new_attendance = Attendance(student_id=data["student_id"], ...)
            db.add(new_attendance)
            db.commit()
            db.refresh(new_attendance)
            await websocket.send_json({"status": "recorded"})
    except WebSocketDisconnect:
        print("Client disconnected")
    finally:
        db.close() # handled by yield in get_db, but shown for clarity

New (Non-blocking) Usage:

# main.py (new, simplified)
from fastapi import APIRouter, Depends, WebSocket, WebSocketDisconnect
from sqlalchemy.ext.asyncio import AsyncSession # Use AsyncSession
from .database import get_async_db # Use new async dependency

router = APIRouter()

@router.websocket("/ws")
async def websocket_endpoint(websocket: WebSocket, db: AsyncSession = Depends(get_async_db)):
    await websocket.accept()
    try:
        while True:
            data = await websocket.receive_json()
            
            # ALL database operations must now be awaited
            new_attendance = Attendance(student_id=data["student_id"], ...)
            db.add(new_attendance)
            await db.commit() # Await commit
            await db.refresh(new_attendance) # Await refresh

            # For fetching data, use session.execute and await
            # result = await db.execute(select(Attendance).where(Attendance.id == new_attendance.id))
            # fetched_attendance = result.scalar_one_or_none()

            await websocket.send_json({"status": "recorded"})
    except WebSocketDisconnect:
        print("Client disconnected")
    # The 'async with AsyncSessionLocal()' in get_async_db handles closing the session automatically

The difference is subtle but critical: every interaction with the db object that involves I/O now requires an await. This tells the asyncio event loop that it can switch to another task while waiting for the database response, preventing the blocking behavior.

After deploying these changes, the difference was immediate and stark. Latency dropped back to the promised sub-50ms range, even under heavy load. The WebSocketDisconnect errors plummeted, only occurring when a client genuinely closed the connection. The system felt snappy and reliable, just as intended.

Lessons Learned

This experience hammered home a few crucial points about building high-performance asynchronous applications:

1. async Doesn't Mean Non-Blocking By Default: Just declaring a function async def doesn't magically make all code within it asynchronous. If you perform blocking I/O operations (like traditional requests calls, time.sleep(), or synchronous database drivers), you will block the event loop. Always ensure your I/O-bound code uses truly asynchronous libraries or is explicitly offloaded.
2. Database is Often the Bottleneck: In many web applications, the database is the slowest component. How your application interacts with it under load is critical. Asynchronous database drivers and careful connection pooling are essential for scalability.
3. Monitoring Beyond CPU/RAM: While system resource monitoring is a good first step, application-level metrics (request latency, error rates, queue depths) are invaluable for pinpointing bottlenecks. If I had better application-level latency metrics from the start, I might have identified the database interaction as the culprit sooner.
4. Embrace Native Async: While run_in_threadpool can save you in a pinch by moving blocking code off the event loop, native asynchronous drivers (like asyncpg, aiomysql, or sqlalchemy.ext.asyncio) are generally the superior solution for long-term performance and maintainability, as they avoid the overhead of thread management and context switching.

This refactor was a significant step in making the attendance system robust. It reinforced the principle that understanding the underlying concurrency model of your framework is as important as knowing its API. Don't just async def and hope for the best; truly understand what makes your code non-blocking.