Does adding a column lock the table?

It takes the strongest table lock, but on a modern version it holds it for a moment rather than for a rewrite. Adding a column with no default, or with a constant one, is a catalog change: the existing rows are left alone and the new value is remembered separately. The danger is not the duration of the work. It is how long the command waits to start.

Why a fast statement causes a long outage

The lock cannot be granted while anyone is reading the table, so the command waits. While it waits, every new query that needs the table queues behind it, including ordinary reads that would not have conflicted with each other at all. One report running for two minutes therefore turns a millisecond schema change into two minutes of a table that answers nothing.

This is the single most common way a routine migration becomes an incident, and it looks nothing like the cause: the graph shows hundreds of blocked queries and one harmless-looking ALTER TABLE in the middle of them.

The guard is a lock timeout set on the session running the migration. With it, the command gives up quickly instead of accumulating a queue, and the migration tool retries. Without it, the command is indefinitely patient at everybody else’s expense. Finding what is blocking a query is how to confirm this is what happened, and the root of the tree will be the long query rather than the migration.

The variants that really do rewrite

Not every column addition is cheap. A default that is evaluated per row rather than fixed, such as one calling a function that returns something different each time, forces every row to be written. So does adding a column of a type that changes the row layout in a way the missing-value mechanism cannot represent. A rewrite of a large table under that lock is a maintenance window, not a deployment step.

Adding a constraint has its own rules and they are more forgiving than people expect: a foreign key or check constraint can be added unvalidated first and validated afterwards under a weaker lock, which splits one long outage into a short one and a slow background pass.

Dropping a column is instant and does not reclaim the space, which is worth knowing before anyone schedules a rewrite to get it back.

A wide row that has just grown a column also changes update behaviour, since there may no longer be room on the page for the cheap kind of update: that is HOT updates and fillfactor. For the lock levels themselves and what conflicts with what, lock contention and blocking trees has the table.