A migration runs ALTER TABLE users ADD COLUMN nickname… (Databases & SQL interview question)

The ALTER waits for an ACCESS EXCLUSIVE lock behind the analytics query, and every new query on users queues behind the waiting ALTER

The ALTER waits behind the analytics query, and every new query waits behind the ALTER. ADD COLUMN needs an ACCESS EXCLUSIVE lock. That lock conflicts with the lock the analytics query holds, so the ALTER must wait.

PostgreSQL queues lock requests in order. A new API query would not conflict with the analytics reader. But it still waits behind the pending ALTER. So all queries on users hang.

Fix: run DDL (schema changes such as ALTER) with a short lock_timeout, and retry. Then the ALTER gives up quickly instead of stalling traffic.

Explicit locking (https://www.postgresql.org/docs/current/explicit-locking.html)postgresql.org
ALTER TABLE (https://www.postgresql.org/docs/current/sql-altertable.html)postgresql.org