Columns
ALTER COLUMN ... DROP NOT NULL
ALTER TABLE orders ALTER COLUMN customer_id DROP NOT NULL;What this does
Instant and metadata-only on Postgres 9 and later: no table scan, no rewrite. The MEDIUM risk is not about the operation itself but about exposure to the lock-queue problem: if a long-running transaction already holds any lock on the table, this brief ACCESS EXCLUSIVE request queues behind it and everything else queues behind that request in turn.
Safe rewrite
Set lock_timeout to bound the worst-case wait and avoid a lock-queue pileup
SET lock_timeout = '2s';
ALTER TABLE orders ALTER COLUMN customer_id DROP NOT NULL;