Columns
ADD COLUMN ... GENERATED ALWAYS AS (...) STORED
Lock mode ACCESS EXCLUSIVE
Risk HIGH
Blocks reads, writes, other DDL
pgfence rule
add-column-stored-generated ALTER TABLE orders ADD COLUMN total_cents bigint GENERATED ALWAYS AS (quantity * unit_price_cents) STORED;What this does
A stored generated column must compute and persist its value for every existing row, which is a full table rewrite under ACCESS EXCLUSIVE, the same class of operation as a volatile DEFAULT.
Safe rewrite
Add a regular column, compute the value with a trigger, backfill out-of-band
-- 1. Add a regular column:
ALTER TABLE orders ADD COLUMN total_cents bigint;
-- 2. Create a trigger to compute the value for new/updated rows.
-- 3. Backfill existing rows out-of-band in batches.