The one thing to understand first
Most forms of ALTER TABLE take an ACCESS EXCLUSIVE lock — the strongest table lock, conflicting with every other lock mode including the ACCESS SHARE that a plain SELECT takes. While held, no session can even read the table. On a busy table this turns a “quick schema change” into a full outage.
The damage isn’t the lock you take — it’s the queue that forms behind a DDL statement that is itself waiting. A single slow query in front of your ALTER can freeze every new read of the table, so the whole art of safe DDL is never letting a strong-lock request block.
The hidden danger: the lock queue
The worst part is not the lock itself but the queue. PostgreSQL grants lock requests roughly in order. When your ALTER requests ACCESS EXCLUSIVE, it must wait for existing readers to finish — and meanwhile every new query queues behind your waiting ALTER, even simple SELECTs that would not normally conflict with each other. One long-running query in front of your ALTER can therefore stall the entire application: