The default that rewrites, and the one everybody warns you about

Adding a column with a default used to rewrite the whole table. Since PostgreSQL 11 it usually does not — and the advice most people are still repeating is about the wrong function.

11 minutes · fast defaults · volatility · IMMUTABLE · STABLE · VOLATILE

What changed in PostgreSQL 11

Before version 11, adding a column with any default rewrote the entire table: the new value had to be physically written into every existing row. On a large table that is minutes of ACCESS EXCLUSIVE, and it is where the folklore comes from.

Since 11, PostgreSQL stores a non-volatile default once, in the catalog, and hands it back for any row that predates the column. No rows are written. The statement is a catalog update and takes about as long as one.

Try this

ALTER TABLE orders ADD COLUMN status_v2 text NOT NULL DEFAULT 'new';

Watch: The cost class on the statement. Forty-one million rows, and nothing was written.

The one everybody gets wrong

You have almost certainly read that ADD COLUMN … DEFAULT now() will lock your table for minutes. It is repeated in style guides, linters and code review comments.

Predict

On PostgreSQL 16, does `ALTER TABLE orders ADD COLUMN shipped_at timestamptz DEFAULT now()` rewrite the table?

Try this

ALTER TABLE orders ADD COLUMN shipped_at timestamptz NOT NULL DEFAULT now();

Watch: The cost class. Then run the next one and compare.

Try this

ALTER TABLE orders ADD COLUMN seen_at timestamptz NOT NULL DEFAULT clock_timestamp();

Watch: One word different. Six minutes of ACCESS EXCLUSIVE.

The rule is volatility, not the function's name. `now()` and `current_timestamp` are STABLE. `clock_timestamp()`, `random()`, `gen_random_uuid()` and `nextval()` are VOLATILE, and every one of them rewrites.

The catch nobody mentions

DEFAULT now() not rewriting is good news for your lock. It is not necessarily good news for your data.

Every pre-existing row gets the same value: the instant the migration ran. A created_at backfilled this way says every order in the shop's history was placed the afternoon you deployed. That is often wrong and always worth deciding on purpose.

MongoDBThere is no default to add: documents written before the field existed simply do not have it, and the readers have to cope. That is the schemaless drift track, and it trades this problem for a different one.

The database

The same one every claim above was made about. Nothing here is graded — run whatever you like.

Reading your progress…

Answer the 1 prediction above first.

All tracks