← all rules

ADD COLUMN GENERATED ... STORED

Why it's unsafe

ADD COLUMN ... GENERATED ALWAYS AS (...) STORED computes and writes the value for every existing row, rewriting the table under an ACCESS EXCLUSIVE lock.

Safe rewrite

Add a plain nullable column, backfill the computed value in batches, and keep it current with a trigger or in application code — do not add a STORED generated column directly to a populated table.

Unsafe

ALTER TABLE users ADD COLUMN full_name text GENERATED ALWAYS AS (first || ' ' || last) STORED;

Safe

-- add a plain nullable column (no rewrite)
ALTER TABLE users ADD COLUMN full_name text;
-- backfill in batches: UPDATE users SET full_name = first || ' ' || last;
-- keep it current with a trigger (or compute it in application code)

Related

• add-column-volatile-default — Adding a column with a volatile DEFAULT rewrites every existing row under an ACCESS EXCLUSIVE lock.