SDB305[high]Rewrites
Identity column added to an existing table
Adding an identity column rewrites the table.
Why it's risky in PostgreSQL
Every existing row needs a generated value, so PostgreSQL rewrites the table under a strong lock.
What triggers it, and the safer way
ALTER TABLE audit ADD COLUMN id bigint GENERATED ALWAYS AS IDENTITY;ALTER TABLE audit ADD COLUMN id bigint;
-- backfill from a sequence in batches, then add the identitySuppress it when you're sure
Put this comment on the line before the statement. The reason is required and shows in the report.
-- shadowdb:allow SDB305 <why this is safe here>Applies to PostgreSQL 14, 15, 16, 17.