The Slow Query Log

ADD COLUMN is instant, until it isn't: what still takes an AccessExclusiveLock

"ADD COLUMN ... DEFAULT is instant since PG11" has been repeated so often it's become a migration habit people stop checking. It's true for the specific case Postgres actually optimized, and false for several cases that look identical in the migration file. A team that trusts the generalization finds out the difference during a deploy, when a column addition that was "instant" in staging holds an AccessExclusiveLock on a hot table in production for the length of a table rewrite.

What PG11 actually changed

Before PG11, adding a column with a non-null default meant rewriting every existing row to physically store that default value, because the table's on-disk format assumed every row had a value for every column. PG11 changed this for the common case: the new column's default is stored once in the table's metadata (pg_attribute.atthasmissing / attmissingval), and any row read before being updated gets the default value supplied at read time, not written to disk. The ALTER TABLE itself takes an AccessExclusiveLock on the table either way — that part never changed — but the lock is held for milliseconds instead of however long a full table rewrite takes, because there's no rewrite.

That optimization has conditions. It applies when the default expression is not volatile — not only to a literal constant. It does not automatically apply to every variant that looks similar.

Case 1: volatility, not "is it a constant", decides the rewrite

-- Fast since PG11: now() is STABLE, not VOLATILE. Its value doesn't
-- change within a single transaction, so Postgres can evaluate it
-- once at ALTER TABLE time and store that one result in the table's
-- metadata default, the same way it stores a literal constant.
ALTER TABLE orders ADD COLUMN created_at timestamptz DEFAULT now();

-- Still rewrites the whole table: clock_timestamp(), random() and
-- gen_random_uuid() are VOLATILE — by definition, Postgres has to
-- assume each call can return a different value, even within the
-- same statement. There's no single value to put in the metadata
-- default, so it falls back to writing an actual value into every
-- row, which means visiting and rewriting every row.
ALTER TABLE orders ADD COLUMN touched_at timestamptz DEFAULT clock_timestamp();
ALTER TABLE orders ADD COLUMN uuid uuid DEFAULT gen_random_uuid();

The metadata-default trick works for any default whose declared volatility is STABLE or IMMUTABLE, because either one guarantees the same result for every row at the moment the ALTER TABLE runs. VOLATILE is the only category that forces the fallback. This is why now() is cheap but gen_random_uuid() is not, even though both look like "a function call, not a constant" if you're only reading the SQL and not checking pg_proc.provolatile.

Case 2: adding a NOT NULL constraint later still scans the table

ALTER TABLE orders ADD COLUMN region text;              -- instant
UPDATE orders SET region = 'unknown' WHERE region IS NULL; -- separate, batchable
ALTER TABLE orders ALTER COLUMN region SET NOT NULL;      -- scans the table

Splitting the column add from backfilling values is the right pattern for avoiding a long lock during the add — but the final SET NOT NULL still requires a full table scan to verify no row violates the constraint, and it holds AccessExclusiveLock for the duration of that scan (not a rewrite, just a read-only verification pass, but still exclusive and still blocking). As of PG12, you can avoid the scan at SET NOT NULL time if you first add a CHECK (region IS NOT NULL) NOT VALID constraint, validate it with VALIDATE CONSTRAINT (which takes only ShareUpdateExclusiveLock — concurrent reads and writes continue), and only then run SET NOT NULL, which Postgres recognizes is already proven by the validated check constraint and skips re-scanning.

Case 3: a foreign key or a generated column

ALTER TABLE orders ADD COLUMN customer_id bigint
  REFERENCES customers(id);                       -- metadata-only add,
                                                    -- but FK validation scans
                                                    -- both tables

ALTER TABLE orders ADD COLUMN total_cents bigint
  GENERATED ALWAYS AS (price_cents * qty) STORED;  -- rewrites: every row
                                                    -- needs a computed,
                                                    -- stored value now

A STORED generated column is, by definition, a per-row computed value that must exist on disk for every row — there's no metadata shortcut, because unlike a constant (or a STABLE/IMMUTABLE default), the value isn't the same for every row. Adding a foreign key constraint adds the column instantly but then validates the relationship by scanning both tables unless you add it NOT VALID first and validate separately, same pattern as the NOT NULL case above.

What to actually check before trusting "it's instant"

ALTER TABLE takes an AccessExclusiveLock on the table for any of these operations — that part is constant and non-negotiable for DDL on the table structure. What changes is how long Postgres holds it, and that's determined entirely by whether the operation can be satisfied with a metadata change or requires visiting rows. Test the actual duration against production-sized data, not a staging table with a thousand rows — a rewrite that takes 4ms on staging can take 40 minutes on a 200GB production table, and the lock is held for the full 40 minutes either way.

Rule of thumb: if the default's function volatility is VOLATILE (check \df+ function_name or pg_proc.provolatile), if you're adding NOT NULL without a pre-validated check constraint, or if the column is a STORED generated column, assume a full table scan or rewrite and plan the migration window (or the NOT VALID + VALIDATE split) accordingly — don't assume PG11's optimization covers you by default.