Does ADD COLUMN with a DEFAULT now() rewrite the table?

Volatile column default forcing a table rewrite

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

What happens

Adding a column with a constant default is instant on modern Postgres, so teams assume all defaults are. A volatile default must be evaluated per existing row, so Postgres rewrites the whole table under ACCESS EXCLUSIVE, reads and writes blocked for the duration.

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 ... DEFAULT clock_timestamp() (volatile)	15.4 s	15.2 s	15.2 s	AccessExclusiveLock, ShareLock, rewrite
Safe	ADD COLUMN ... DEFAULT now() (stable)	5 ms	3 ms	2 ms	AccessExclusiveLock

Fires on

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

The safe pattern

Add the column nullable (instant), backfill the volatile values in batches from a job, then enforce NOT NULL via a validated CHECK if needed. A non-volatile default (a constant, or now(), stable within the statement) stays metadata-only on Postgres 11+; only volatile defaults like gen_random_uuid() or random() force the rewrite.

SET lock_timeout = '5s';
ALTER TABLE users ADD COLUMN uid uuid;
-- backfill from a job, in batches:
--    UPDATE users SET uid = gen_random_uuid()
--    WHERE id IN (SELECT id FROM users WHERE uid IS NULL LIMIT 5000);
-- then enforce NOT NULL via a validated CHECK (see BV011)

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 (2)

random default

ALTER TABLE users ADD COLUMN shard int DEFAULT (floor(random() * 16));

uuid default

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

Stays silent (2)

constant default

ALTER TABLE users ADD COLUMN status text DEFAULT 'active';

now default

ALTER TABLE users ADD COLUMN created_at timestamptz DEFAULT now();

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

← BV006 BV008 →