The different sorts of locks and when they’re used are mentioned in the doc in
Table-level Locks. For instance, Postgres 11’s
ALTER TABLE may acquire a
SHARE UPDATE EXCLUSIVE,
SHARE ROW EXCLUSIVE, or
ACCESS EXCLUSIVE lock.
Postgres 9.1 through 9.3 claimed to support two of the above three but actually forced
Access Exclusive for all variants of this command. This limitation was lifted in Postgres 9.4 but
ADD COLUMN remains at
ACCESS EXCLUSIVE by design.
It’s easy to check in the source code because there’s a function dedicated to establishing the lock level needed for this command in various cases:
Concerning how much time the lock is held, once acquired:
- When the column’s default value is NULL, the column’s addition should be very quick because it doesn’t need a table rewrite: it’s only an update in the catalog.
- When the column has a non-NULL default value, it depends on PostgreSQL version: with version 11 or newer, there is no immediate rewriting of all the rows, so it should be as fast as the NULL case. But with version 10 or older, the table is entirely rewritten, so it may be quite expensive depending on the table’s size.