SDB301[high]Rewrites

Column type change rewrites the table

Changing a column type usually rewrites the whole table under an ACCESS EXCLUSIVE lock.

Why it's risky in PostgreSQL

Most type changes force PostgreSQL to rewrite every row and rebuild indexes while blocking all access to the table. A few changes are metadata-only (for example widening varchar).

Add a new column with the new type, backfill in batches, then swap.

What triggers it, and the safer way

unsafe.sql

ALTER TABLE events ALTER COLUMN id TYPE bigint;

safer.sql

ALTER TABLE events ADD COLUMN id_big bigint;
-- backfill in batches, add triggers for new writes, then swap columns

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 SDB301 <why this is safe here>

Applies to PostgreSQL 14, 15, 16, 17.