PostgreSQL NOT NULL can be added without locking

There’s bit of an interesting rabbit hole with NOT NULL constraints vs locking up your DB.

It starts with “is a NOT NULL check contraint worse than a NOT NULL”?

The docs hint at some difference: https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-NOT-NULL

SO thread: https://dba.stackexchange.com/questions/158499/postgres-how-is-set-not-null-more-efficient-than-check-constraint

Which points to https://dba.stackexchange.com/questions/66840/add-not-null-constraint-to-large-table-without-table-scan

Which finally points to https://dba.stackexchange.com/questions/267947/how-can-i-set-a-column-to-not-null-without-locking-the-table-during-a-table-scan/268128#268128

The big problem is adding a NOT NULL constraint can badly lock up the table and other sessions.

Using a constraint with NOT VALID and then validating later is a work around.

There’s a third step you can do: add a NOT NULL after this an PostgreSQL will do a no-lock update.

First add the constraint:

-- short-time exclusive lock
alter table foos 
  add constraint foos_not_null 
  check (bar1 is not null) not valid;

Next validate it:

-- seqscan, but without exclusive lock, concurrent sessions can read/write
alter table foos validate constraint foos_not_null;

Finally add not null and drop constraint:

-- exclusive lock, but foos_not_null proves: there is no NULL in this column
-- so this alter table would be fast
alter table foos alter column bar1 set not null;
-- not needed anymore
alter table foos drop constraint foos_not_null;

There is also an approach of adding a NOT NULL with a default value, PostgreSQL optimizes this case: https://brandur.org/postgres-default

ALTER TABLE users
    ADD COLUMN credits bigint NOT NULL DEFAULT 0;