FastAPI Migration Causes 6-Hour Database Lock

The catastrophic lock was not caused by FastAPI's async nature, but by a standard database migration practice hitting the harsh reality of a production workload. An `ALTER TABLE` statement in PostgreSQL, used to add a new column, acquired an `ACCESS EXCLUSIVE` lock. This is the most restrictive lock, blocking all reads and writes, effectively taking the table offline. The migration, which took only 30 seconds in a local test environment, queued up behind a long-running query in production. This created a domino effect: the migration waited for the query, and every subsequent application query then piled up behind the migration's lock request, leading to a complete outage. The post-mortem revealed a key preventative measure: setting a `lock_timeout` in the migration script. By configuring a short timeout (e.g., 5 seconds), the migration would have failed fast instead of waiting indefinitely, preventing the cascading failure and allowing for a retry during a quieter period. This incident highlights a critical gap between typical development environments and production systems. Local and staging tests often lack the concurrent user traffic, long-running transactions, and sheer data volume that can turn a seemingly benign schema change into a major incident. Simulating real-world concurrency is essential to de-risk deployments. The revised migration strategy involved breaking the change into smaller, less disruptive steps. This included creating the column, followed by a separate, batched process to backfill the data in smaller transactions. This approach avoids holding a single, long-lived lock on a critical table. While FastAPI is known for high performance due to its asynchronous (ASGI) architecture, this event shows that at scale, system stability often bottlenecks on the database. Even the fastest framework can be brought to a standstill by database-level locking and contention issues that are not surfaced during performance testing.