Blogmark

During Gülçin Yıldırım Jelínek's talk "Anatomy of Table-Level Locks in PostgreSQL", she mentioned that the common "Expand and Contract" schema migration approach to applying not null to an existing column will still involve an Access Exclusive table lock. This is because PostgreSQL needs to do a full table scan to ensure there are no instances of a null.

She goes on to mention that we can do better than that. We can further reduce locking with an additional step. If we first add an invalid not null check constraint for the column and eventually validate it after backfilling the entire table, then the alter table statement for adding the not null constraint will only need a Share Update Exclusive table lock. This means we can generally still perform SQL operations that read and update data.

According to the alter table docs:

SET NOT NULL may only be applied to a column provided none of the records in the table contain a NULL value for the column. Ordinarily this is checked during the ALTER TABLE by scanning the entire table; however, if a valid CHECK constraint is found which proves no NULL can exist, then the table scan is skipped.

Here is the relevant commit and commit message:

If existing CHECK or NOT NULL constraints preclude the presence
of nulls, we need not look to see whether any are present.

Which was added in the PostgreSQL 12 release.

This approach is also recommended by the strong_migrations gem for Rails.