Scaling FastAPI WebSockets: Database Connection Management Under Load

I built a real-time attendance management system a while back. The core idea was simple: students check in via a web app, their GPS location gets verified, and the attendance data syncs in sub-50ms. FastAPI handled the backend, WebSockets for the real-time bits, and SQLAlchemy for persistence. It also had a cron job for automated bulk-absent processing, which marked students absent if they didn't check in by a certain time.

Everything worked great in development and during initial small-scale tests. We were hitting those sub-50ms sync times consistently. But then it went live, and things got interesting.

The Problem

When the system hit peak usage – think 300+ students trying to check in around the same time, usually first thing in the morning – the real-time aspect started to crumble. WebSocket connections would sometimes hang. Users would see 'connecting...' or stale attendance data for a good 5-10 seconds, sometimes more. That sub-50ms target was a distant memory. The automated bulk-absent processing, which runs on a schedule, also started to fail intermittently or take an absurdly long time, sometimes coinciding with these morning slowdowns.

It was clear something was bottlenecking the system, and it felt like database interactions were at the heart of it.

The Investigation

My first instinct was to check the server resources. CPU and RAM usage were well within limits. Network latency was stable. The problem wasn't the hardware.

I dug into the application logs. That's where I started seeing a pattern: occasional sqlalchemy.exc.TimeoutError or OperationalError messages. These pointed directly to the database. Sometimes, when using an async PostgreSQL driver, I'd even see asyncpg.exceptions.TooManyConnectionsError. These errors meant one thing: the application couldn't get a database connection when it needed one.

Next, I monitored the database itself. Sure enough, the active connection count was spiking dramatically during peak times, often hitting the configured maximum number of connections allowed by the PostgreSQL server. Even worse, looking at active queries, I noticed some long-running UPDATE or INSERT statements, usually related to the bulk-absent processing task. This task, running in the background, was holding onto database connections for extended periods.

My hypothesis formed quickly: the default SQLAlchemy connection pool wasn't cutting it. It was too small for the burst of concurrent WebSocket traffic and the resource-intensive background task hitting the same database at the same time.

Root Cause

The root cause boiled down to two main issues:

1. Insufficient Connection Pool Size: The default SQLAlchemy create_engine settings were too conservative for a system with 300+ concurrent users plus background jobs. When many requests came in simultaneously, the pool quickly exhausted its available connections.
2. Resource Contention from Background Tasks: The bulk-absent processing task, while critical, was implemented in a way that could hog database connections. It would grab a session, process a large batch of students, and hold that session open for the entire duration, preventing real-time requests from getting a connection.

Effectively, the real-time system was getting starved of database connections because the pool was too small and a background task was using up a significant chunk of it for too long.

The Solution

The fix involved a few key adjustments to SQLAlchemy's configuration and a slight refactor in how database sessions were managed.

1. Tuning the Connection Pool

I started by adjusting the create_engine parameters to better suit the expected load. I moved from SQLite (which is fine for dev but not production concurrency) to PostgreSQL early on, so these settings are for a production-grade setup.

Here's what the database.py looked like after the changes:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

# Using PostgreSQL for production-grade concurrency
SQLALCHEMY_DATABASE_URL = "postgresql://user:password@host/dbname"

engine = create_engine(
    SQLALCHEMY_DATABASE_URL,
    pool_size=20,          # Number of connections to keep open in the pool (increased from default)
    max_overflow=10,       # Allow 10 extra connections if needed during spikes
    pool_timeout=30,       # Wait up to 30 seconds for a connection before timing out
    pool_recycle=3600,     # Recycle connections every hour to prevent stale ones
    # For async drivers like asyncpg with SQLAlchemy 2.0 style:
    # future=True,
    # poolclass=AsyncAdaptedQueuePool # if using asyncpg driver
)

SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

# Dependency to get a DB session
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

• pool_size: I bumped this up from the default (often 5-10) to 20. This ensures more connections are readily available.
• max_overflow: This allows SQLAlchemy to temporarily create up to 10 additional connections beyond pool_size if demand spikes. This handles sudden bursts without immediately hitting a hard limit.
• pool_timeout: This is important. It sets a limit (30 seconds) on how long a request will wait for an available connection from the pool. If it can't get one in that time, it errors out, preventing indefinite hangs.
• pool_recycle: Recycling connections every hour helps prevent issues with stale connections or database server-side timeouts.

2. Ensuring Explicit Session Management

While FastAPI's Depends often handles session closing, it's critical to ensure it's robust, especially in concurrent WebSocket handlers and background tasks that might not strictly follow the request-response cycle.

My get_db dependency was already structured correctly, using a try...finally block to ensure db.close() is always called, returning the connection to the pool. This is fundamental for efficient pooling.

# app/dependencies.py (or wherever your get_db is)
from sqlalchemy.orm import Session
from .database import SessionLocal

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

3. Refactoring Background Tasks

For the bulk-absent processing, I ensured it acquired and released its database session explicitly and efficiently. Instead of holding a session open for the entire, potentially long-running batch, I made sure it committed and closed its session after processing a reasonable chunk, or at the very least, after its full execution.

# app/tasks.py
from sqlalchemy.orm import Session
from app.database import SessionLocal
from app.models import Student, Attendance # Example models

async def process_bulk_absents():
    db: Session = SessionLocal() # Get a fresh session for this task
    try:
        # Example: Find students who haven't checked in by a certain time
        print("Starting bulk absent processing...")
        absent_students = db.query(Student).filter(Student.last_check_in < some_timestamp).all()

        for student in absent_students:
            # Create attendance record for absent
            absent_record = Attendance(student_id=student.id, status="absent", timestamp=datetime.now())
            db.add(absent_record)
        
        db.commit() # Commit the entire batch
        print(f"Processed {len(absent_students)} absent records.")

    except Exception as e:
        db.rollback()
        print(f"Error in bulk absent processing: {e}")
    finally:
        db.close() # Ensure the session is always closed, returning the connection

This explicit db.close() in the finally block for background tasks is just as important as in the API dependencies. It prevents a long-running task from indefinitely holding onto a connection.

Lessons Learned

1. Defaults are for Development: Default database connection pool settings are rarely adequate for production, especially with high concurrency or mixed workloads (real-time + batch).
2. Monitor Your Database: Don't just monitor your application servers. Keep an eye on your database connection counts, active queries, and query execution times. They are often the first place bottlenecks show up.
3. Explicit Session Management: Always ensure your database sessions are properly closed and connections returned to the pool. FastAPI's Depends helps, but background tasks or custom logic need careful handling.
4. Isolate Workloads (When Possible): If you have very different database usage patterns (e.g., fast, small real-time queries vs. long-running batch updates), consider separate connection pools or even separate database instances. For this project, tuning the single pool was sufficient, but it's a good pattern to keep in mind.

After these changes, the system became much more stable. The sub-50ms sync times returned, even under heavy load, and the bulk-absent processing ran reliably without impacting the real-time experience. It was a good reminder that performance often comes down to careful resource management, especially when dealing with shared resources like database connections.