Is it safe to use default value with not null when adding a new column?

We have a Rails app powered by Postgresql v11.4 where I want to add a new column with a default value and a not null constraint like below:

ALTER TABLE "blogs" ADD "published" boolean DEFAULT FALSE NOT NULL

I know adding a new column with a default value is safe. However, is it still safe when combined with NOT NULL? Or will it lock the database? Thanks!

That statement is safe.

An ALTER TABLE will never lock "the database", it will only lock the table.

Postgres will not actually rewrite the table because you provided a constant value (false) for the default value. So adding the column is actually done in a few milliseconds because Postgres only stores the information that a new column is available. When that column is accessed (and no value is available) then it will use the default value from the column's definition. Only when a row is updated, the new row will contain an actual value for the column.

If the default value was an expression that could potentially be different for each row, Postgres would rewrite the table to physically put the default column into all rows. But still, that would only lock the table, not the database.

From Postgres documentation about ALTER TABLE: "When a column is added with ADD COLUMN and a non-volatile DEFAULT is specified, the default is evaluated at the time of the statement and the result stored in the table's metadata. That value will be used for the column for all existing rows. If no DEFAULT is specified, NULL is used. In neither case is a rewrite of the table required."

And tested in Postgres v.11: dbfiddle.uk

StackExchange Database Administrators Q#312033, answer score: 7