Does adding a stored generated column rewrite the table?

Stored generated column added to an existing table

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

What happens

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.

Why it is dangerous on a populated table

It completes, but the time it holds its lock or rewrites rows scales with the table, and on a busy one that is long enough for traffic to queue.

Measured

Both forms ran against the same table (20M rows, 1.7 GB) on Postgres 18.6, with one session inserting a row and another reading one row every 50 ms. Worst wait is the slowest of those probe calls while the statement ran. This run is from Sep 16, 2026; the whole benchmark has every scenario and how to run it.

	Statement	Time	Worst write wait	Worst read wait	Lock
Fires	ADD COLUMN ... GENERATED ALWAYS AS (...) STORED (rewrite)	18.3 s	18.2 s	18.2 s	AccessExclusiveLock, ShareLock, rewrite
Safe	ADD COLUMN total_cents bigint, computed by the application or a later backfill	3 ms	2 ms	1 ms	AccessExclusiveLock

Fires on

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

The safe pattern

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

Fixtures

The rule ships with these files and the test suite runs them on every change: the first set must fire, the second must stay silent.

Fires (1)

generated stored

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

Stays silent (1)

on new table

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

How to check locally

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. No install, nothing leaves your machine:

npx bolvrk check migration.sql --remote

Browse all 135 rules

← BV023 BV025 →