Columns
ADD COLUMN ... GENERATED ALWAYS AS IDENTITY
Lock mode ACCESS EXCLUSIVE
Risk HIGH
Blocks reads, writes, other DDL
pgfence rule
add-column-identity ALTER TABLE orders ADD COLUMN seq_no bigint GENERATED ALWAYS AS IDENTITY;What this does
Adding an IDENTITY column backfills a sequence-derived value into every existing row, a full table rewrite under ACCESS EXCLUSIVE for the whole operation.
Safe rewrite
Add the column first, backfill and attach identity behavior separately
ALTER TABLE orders ADD COLUMN IF NOT EXISTS seq_no bigint;
-- Backfill existing rows out-of-band in batches before adding identity semantics.