ADD COLUMN with IDENTITY

Check name: AddIdentityColumnCheck

Lock type: ACCESS EXCLUSIVE (full table rewrite)

Bad

Adding an identity column (GENERATED BY DEFAULT AS IDENTITY or GENERATED ALWAYS AS IDENTITY) to an existing table causes a full table rewrite. PostgreSQL must call nextval() for every existing row to populate the new column — a volatile operation that bypasses the PG 11 constant-default optimization. The rewrite acquires an ACCESS EXCLUSIVE lock, blocking all reads and writes for its duration.

-- Both forms cause a full table rewrite:
ALTER TABLE users ADD COLUMN id BIGINT GENERATED BY DEFAULT AS IDENTITY;
ALTER TABLE users ADD COLUMN seq INTEGER GENERATED ALWAYS AS IDENTITY;

Note: GENERATED AS IDENTITY on CREATE TABLE is safe — there are no existing rows to rewrite, and it is the recommended alternative to SERIAL/BIGSERIAL.

Good

Add the column nullable first, backfill in batches outside the migration, then set the default and NOT NULL separately:

-- Step 1: Create a sequence
CREATE SEQUENCE users_id_seq;

-- Step 2: Add the column without default (fast, no rewrite)
ALTER TABLE users ADD COLUMN id BIGINT;

-- Step 3: Backfill existing rows in batches (outside migration)
UPDATE users SET id = nextval('users_id_seq') WHERE id IS NULL;

-- Step 4: Set default for future inserts
ALTER TABLE users ALTER COLUMN id SET DEFAULT nextval('users_id_seq');

-- Step 5: Set NOT NULL (safe on Postgres 11+ once all rows have a value)
ALTER TABLE users ALTER COLUMN id SET NOT NULL;

-- Step 6: Set sequence ownership
ALTER SEQUENCE users_id_seq OWNED BY users.id;