One Alter Two Outcomes

orders holds 412 million rows. Two migrations ship in the same release against PostgreSQL:

-- A
ALTER TABLE orders ADD COLUMN currency text NOT NULL DEFAULT 'USD';

-- B
ALTER TABLE orders ADD COLUMN imported_at timestamptz NOT NULL DEFAULT clock_timestamp();

A returned in 12 ms. B has held ACCESS EXCLUSIVE for forty minutes and every query against orders is queued behind it. What is different about B?

clock_timestamp() is invoked once per row and 412 million function calls dominate the runtime; the table itself is still updated in place.

'USD' is non-volatile, so it is evaluated once and stored in the catalog. clock_timestamp() is volatile, so each row needs its own value and the whole table is rewritten.

text is variable length and can be appended to existing tuples in place, while timestamptz is fixed width and has to be written into every tuple header.

B's NOT NULL has to be proven with a full table scan. A's constant default lets PostgreSQL skip that check.