The ADD COLUMN DEFAULT that rewrites your table (and the one that doesn't)

Two migrations. Same shape. One is instant, one rewrites every row in the table while holding a lock that blocks reads and writes.

ALTER TABLE users ADD COLUMN plan text DEFAULT 'free';
ALTER TABLE users ADD COLUMN api_key uuid DEFAULT gen_random_uuid();

Both pass CI. Your test database has a few hundred rows, so both finish in milliseconds. The difference only shows up against a real table.

What Postgres 11 changed

Before PG 11, ADD COLUMN ... DEFAULT always rewrote the table. Every row had to be physically updated to carry the new value.

PG 11 added a fast path: if the default is not volatile, Postgres evaluates it once at the time of the statement and stores the resulting value in the catalog (pg_attribute.attmissingval, flagged by atthasmissing), serving it for every pre-existing row without touching them. The column addition becomes a catalog write — instant, regardless of table size.

Note it stores the evaluated result, not the expression. The PG 11 release note describes the condition as "when the default value is a constant," which is narrower than the real rule and misleading — the actual test is whether the expression contains volatile functions. The ALTER TABLE notes state it correctly.

So on PG 11+:

• Non-volatile default → catalog-only. Instant.
• Volatile default → full table rewrite.
• PG 10 or older → full rewrite either way.

The lock is ACCESS EXCLUSIVE, not SHARE

This is the part people get wrong.

ALTER TABLE takes ACCESS EXCLUSIVE. That is the strongest lock Postgres has. It blocks everything — SELECT included. This is not a "writes block but reads continue" situation like a plain CREATE INDEX.

In the instant case, that lock is held for microseconds and nobody notices. In the rewrite case, it is held for the entire duration of the rewrite. On a large table, that is minutes during which your application cannot read the table at all.

The lock mode is identical in both cases. Only the duration differs, and the duration is what kills you.

Which functions are actually volatile

Here is the trap, and it is a genuinely confusing one: now() and CURRENT_TIMESTAMP are not volatile.

They are marked STABLE in pg_proc — they return the same value for the duration of a transaction. That is enough for the PG 11 fast path. So this is instant:

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

The functions that do force a rewrite are the genuinely volatile ones (provolatile = 'v'):

• gen_random_uuid(), gen_random_bytes() — and on PG 18, the new core spellings uuidv4() and uuidv7(). Note gen_random_uuid() only entered core in PG 13; on 12 and earlier it needs CREATE EXTENSION pgcrypto
• uuid_generate_v1(), uuid_generate_v1mc(), uuid_generate_v4() — but not v3/v5, which are deterministic hashes and therefore immutable
• random(), random_normal()
• clock_timestamp(), timeofday() — the wall-clock functions, which unlike now() advance within a transaction
• nextval(), currval()

If you want to check something not on this list:

SELECT proname, provolatile FROM pg_proc WHERE proname = 'gen_random_uuid';
-- provolatile: 'i' = immutable, 's' = stable, 'v' = volatile

Only 'v' forces the rewrite.

The safe pattern

Split the operation into three steps that each take a short lock:

SET lock_timeout = '5s';

-- 1. Add the column nullable, no default. Catalog-only, instant.
ALTER TABLE users ADD COLUMN api_key uuid;

-- 2. Set the default. Applies to NEW rows only. Also instant.
ALTER TABLE users ALTER COLUMN api_key SET DEFAULT gen_random_uuid();

-- 3. Backfill existing rows in batches, outside any long transaction.
UPDATE users SET api_key = gen_random_uuid()
WHERE api_key IS NULL
  AND ctid IN (SELECT ctid FROM users WHERE api_key IS NULL LIMIT 10000);
-- repeat until 0 rows updated

Step 2 is the piece people miss. SET DEFAULT on an existing column only affects future inserts — it never touches existing rows, so it never rewrites. New rows get their UUID from the moment step 2 commits; old rows get theirs from the backfill.

Each UPDATE batch locks only the rows it touches, and commits quickly. Your traffic keeps flowing throughout.

If the column needs to be NOT NULL, do that after the backfill completes, using the scan-free CHECK-constraint path on PG 12+ — covered in the lock modes post.

Set a lock_timeout

SET lock_timeout = '5s' is not decoration.

An ALTER TABLE waiting for ACCESS EXCLUSIVE queues behind any open transaction on the table — including a long-running SELECT. While it waits, every subsequent query queues behind it, because a pending exclusive lock request blocks new lock acquisitions that conflict with it.

So a migration that would have been instant can take your table down anyway, just by waiting behind an analytics query. A lock_timeout makes the migration fail fast and get out of the way instead of poisoning the queue.

Every DDL step in a production migration should have one.

What to look for in review

When you see ADD COLUMN ... DEFAULT in a diff, the question is only: is that default volatile?

• String or numeric literal → fine
• now(), CURRENT_TIMESTAMP → fine, despite looking dynamic
• gen_random_uuid(), random(), clock_timestamp(), nextval() → rewrite

And check the Postgres version, because on PG 10 and older none of this applies and every default rewrites.

Nothing in the SQL syntax marks the difference. The two statements at the top of this post are the same statement with a different word in the middle, and one of them is an outage.