← all rules

ADD COLUMN serial

Why it's unsafe

ADD COLUMN with a serial type (e.g. bigserial) creates a sequence and rewrites every existing row under an ACCESS EXCLUSIVE lock.

Safe rewrite

Add a plain nullable integer column (e.g. bigint), create the sequence and backfill existing rows in batches, then ALTER COLUMN ... SET DEFAULT nextval(...) and add NOT NULL via the safe two-step — do not add serial directly to a populated table.

Unsafe

ALTER TABLE users ADD COLUMN seq serial;

Safe

-- add a plain nullable column (no rewrite), then a sequence default for new rows
ALTER TABLE users ADD COLUMN seq bigint;
CREATE SEQUENCE users_seq_seq OWNED BY users.seq;
ALTER TABLE users ALTER COLUMN seq SET DEFAULT nextval('users_seq_seq');
-- backfill existing rows in batches, then add NOT NULL via the safe two-step

Related

• add-column-identity — Adding a GENERATED ... AS IDENTITY column creates a sequence and rewrites every row under an ACCESS EXCLUSIVE lock.
• prefer-bigint-primary-key — A small-integer primary key overflows its id space; use bigint/bigserial/identity.