Why PostgreSQL ADD COLUMN NOT NULL DEFAULT now() should not pass review on sight

Adrian Van (GitHub: Fanduzi)

Last week a change ticket landed with one statement:

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

The developer said: add a column, give it a default, old rows will not be NULL. The ticket was clean. The style check passed. Someone had already booked a low-traffic window.

I stopped it. The SQL is valid. The ticket still does not say which PostgreSQL version, whether the default is STABLE or VOLATILE, or whether every old row should share the ALTER's timestamp. Get those wrong and a large heap takes a long ACCESS EXCLUSIVE lock, a WAL spike, and as much as another copy of the table on disk. It looks like an add-column. It can run like a rebuild.

Adding a column is not always cheap

PostgreSQL does not treat every ADD COLUMN the same.

A nullable column with no default is cheap on current versions: catalog only, heap barely touched. PostgreSQL 11 also made a non-volatile default cheap. The value is evaluated once, stored in the catalog (atthasmissing / attmissingval), and returned for existing rows. relfilenode does not change. The exclusive lock is short.

DEFAULT 0 is that path.

DEFAULT now() is not a constant. It is a function call. That is the first reason a reviewer should pause. The second is volatility. The ALTER TABLE notes are specific:

• A non-volatile default is stored in metadata. No rewrite.
• A volatile default rewrites the table and its indexes. The documented example is clock_timestamp().

now() is an alias for transaction_timestamp(). It is STABLE: one value for the whole transaction. On PostgreSQL 11 and later, NOT NULL DEFAULT now() usually takes the catalog path. clock_timestamp(), timeofday(), and random() are VOLATILE. Those still rewrite.

Before PostgreSQL 11, a default of any kind rewrote the table.

So the one-liner is three different operations:

Default	Typical PostgreSQL 11+ behavior
Constant (0, 'x')	Catalog only
STABLE function (now())	Catalog only. Every old row reads as the ALTER's timestamp.
VOLATILE function (clock_timestamp())	Table + index rewrite

A rewrite changes relfilenode. It holds ACCESS EXCLUSIVE for the whole build. Disk can approach 2× table size while the new heap is written. Rewriting forms of ALTER TABLE are not MVCC-safe: a concurrent transaction whose snapshot started before the rewrite can see the table as empty.

That is not a style question. It is "will production rebuild this heap, and what timestamp do the old rows get."

Why tickets still sail through

Change tickets look at grants, backups, and the window. They do not always remember that DEFAULT now() and DEFAULT 0 are not the same class of work, or that now() and clock_timestamp() differ.

A style checker is happy. Indentation is fine. now() is just a function call. Passing a linter is not an assessment of lock time.

Online schema-change tools answer how to apply a change. They do not answer whether this shape should run.

Another miss, same family: a change split into several "small" statements. On MySQL, two ALTER TABLEs against the same table can be two rebuilds. Each line looks harmless. The ticket passes them one by one. Nobody reviews the pair.

Same class of legal-but-expensive DDL

I stop these on sight too. None of them are syntax errors. All of them are expensive when the table is large.

CHECK without NOT VALID. Adding a CHECK takes ACCESS EXCLUSIVE and scans the table. Safer: ADD CONSTRAINT … NOT VALID, then VALIDATE CONSTRAINT (that validation takes SHARE UPDATE EXCLUSIVE).

ALTER TABLE orders ADD CONSTRAINT amount_positive CHECK (amount >= 0);

CREATE INDEX without CONCURRENTLY. The default build blocks writes. On a live table that is the wrong default.

DELETE without WHERE. Everyone already treats that as a red line. That is why the ALTER comes first in this note. A bare DELETE is hard to sneak through a ticket. DEFAULT now() is easy to click through.

What I want written on the ticket

If the table is small and the window is real, a rewrite can be fine. Write that down. Do not assume it from the SQL text.

Check relfilenode before and after on a clone if you are unsure:

SELECT relfilenode FROM pg_class WHERE oid = 'public.users'::regclass;

If it changed, the statement rewrote the heap.

Check volatility if the default is a function:

SELECT proname, provolatile
FROM pg_proc
WHERE proname IN ('now', 'clock_timestamp', 'transaction_timestamp');
-- i = immutable, s = stable, v = volatile

Then answer the semantic question. created_at on every old row equal to the time of the ALTER is usually a lie. If that is acceptable and you are on PostgreSQL 11+ with now(), the one-liner is cheap. Still write the version and the function name on the ticket.

If old rows should not share one timestamp, do not use the one-liner:

1. ALTER TABLE users ADD COLUMN created_at timestamptz; — catalog only.
2. Backfill in batches. Not one UPDATE of the whole table.
3. ALTER TABLE users ALTER COLUMN created_at SET NOT NULL; — scan, no rewrite. Or add NOT NULL … NOT VALID and VALIDATE CONSTRAINT.
4. ALTER TABLE users ALTER COLUMN created_at SET DEFAULT now(); — future inserts only.

If someone wrote clock_timestamp(), or the cluster is older than 11, plan a rewrite. Do not discover it in production.

An offline check that flags the shape

I also run the SQL text through an offline linter before the window. DeltaScope v0.490.0 (rule catalog 371: blocker 72, warning 142, notice 157) parses the statement and applies policy. It does not execute the SQL. It does not run EXPLAIN ANALYZE. Default dialect is MySQL; PostgreSQL needs --dialect postgresql. Without a database connection it does not check that the table exists.

deltascope audit \
  --dialect postgresql \
  --sql "ALTER TABLE users ADD COLUMN created_at timestamptz NOT NULL DEFAULT now()" \
  --format json

That hits ddl.pg.alter.add_column.non_null_default.rewrite.warn. Default level is warning. Verdict is review, not reject. Extra facts on the finding: not_null, has_default, default_kind (here function_call). The linter is conservative: a function-call default plus NOT NULL is enough to flag. It cannot see server version or provolatile from the text alone.

I would not default that rule to a hard fail. A 2k-row table in a maintenance window can rewrite. A few-hundred-million-row table with the same SQL is an incident. The tool marks the shape. Window and rollback stay a human call.

The same pass will also flag CHECK without NOT VALID and CREATE INDEX without CONCURRENTLY. That is all I use it for here. A reader who never installs it still has the rewrite lesson above.

Project: https://github.com/Fanduzi/DeltaScope

Site: https://deltascope.pages.dev/

License: Apache 2.0

If you do connect it to a database, use --ask-password, --password-env, or --password-file. Not --password.

Bottom line

The statement parses. The ticket is clean. You still stop and ask: which PostgreSQL version, which function, does every old row get the ALTER time, and can this table absorb a rewrite.

If the answer is fuzzy, it does not pass on sight.

---

Also published in Chinese: Juejin, MODB