[gwy]

I believe the AccessExclusive is only if you are setting to NOT NULL, such as "ALTER TABLE my_table ADD COLUMN my_col boolean NOT NULL DEFAULT false"

We were just down for 56 hours due to our 3rd party platform vendor applying that type of update.

AFAICT the only workaround is to do it in steps: set the DEFAULT, fill existing rows with the default, then apply the NOT NULL constraint (which will still lock it for a full table scan to check the validity of the constraint).

[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.