Stored generated column added to an existing table

Warning — this works, but blocks traffic or rewrites data at scale.

What it catches

A STORED generated column must be computed for every existing row, so adding one rewrites the entire table under ACCESS EXCLUSIVE — the constant-default fast path does not apply.

Fires on

ALTER TABLE orders ADD COLUMN total_c numeric GENERATED ALWAYS AS (subtotal + tax) STORED;

Do this instead

Add a plain column instead: instant, then maintained by a trigger for new writes and backfilled in batches for existing rows. If the generated column is a hard requirement, schedule the rewrite in a maintenance window with a lock_timeout guard.

SET lock_timeout = '5s';
ALTER TABLE orders ADD COLUMN total_c numeric;
-- maintain via trigger on INSERT/UPDATE, backfill existing rows
-- in batches from a job

Catch this before it ships

This rule runs in the hosted service on Startup and above — add --remote with a team token, or use the GitHub Action: npx bolvrk check migration.sql

Browse all 69 rules

← BV023 BV025 →