SDB302[high]Rewrites

Column added with a volatile default

A default like random() or clock_timestamp() forces a table rewrite.

Why it's risky in PostgreSQL

Since PostgreSQL 11, adding a column with a constant or stable default is instant. A volatile default (clock_timestamp(), random(), gen_random_uuid(), a serial column's nextval()) still has to be computed per row, which rewrites the table.

What triggers it, and the safer way

unsafe.sql

ALTER TABLE users ADD COLUMN token uuid DEFAULT gen_random_uuid();

safer.sql

ALTER TABLE users ADD COLUMN token uuid;
ALTER TABLE users ALTER COLUMN token SET DEFAULT gen_random_uuid();
-- backfill existing rows in batches

Suppress it when you're sure

Put this comment on the line before the statement. The reason is required and shows in the report.

suppression

-- shadowdb:allow SDB302 <why this is safe here>

Applies to PostgreSQL 14, 15, 16, 17.