[joevandyk]

ALTER TABLE always creates the AccessExclusive lock.

If you add a 'not null default something' to the column, it'll rewrite the whole table, which can take some time. And since the table has the AccessExclusive lock, this is what will block reads/writes.

Doesn't matter for small tables.

For large tables, you want to add the column without a not null or default, commit that transaction, then populate the data, then add the not null and default constraint.

[fdr]

You are right: in the fast case of "NULL" the amount of physical work amounts to diddling catalogs around rather than copying potentially gigabytes of data.

So in any case a lock is taken, but I've never seen anyone get too bent out of shape over that momentary mutual exclusion to deform the table's type (exception: in transactional DDL where cheap and expensive steps are inter-mingled).

[einhverfr]

More specifically in the multistage work flow, you are only holding the lock while writing the changes to the system catalogs which is not a significant period of time.