CodePark/ questions / Migrations & schema changes
START PRACTICINGPRACTICE
Backend question · Databases & SQL › Migrations & schema changes

Why is this sequence safer than a single ALTER COLUMN ... SET NOT NULL on a large, busy PostgreSQL table?

ALTER TABLE orders ADD CONSTRAINT orders_customer_nn
  CHECK (customer_id IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_nn;
ALTER TABLE orders ALTER COLUMN customer_id SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_customer_nn;

It avoids blocking reads and writes during a long table scan.

SET NOT NULL on its own scans the whole table to check for NULLs. During the scan, it holds an ACCESS EXCLUSIVE lock. This is the strongest lock: it blocks all reads and writes on the table. So on a big table, the app waits for the whole scan.

The safer sequence works in steps:

  • Add a CHECK (col IS NOT NULL) constraint as NOT VALID. This is quick. The constraint applies to new and updated rows, but PostgreSQL does not check the existing rows yet.
  • Run VALIDATE CONSTRAINT. This scans the table under a weaker SHARE UPDATE EXCLUSIVE lock, which still allows normal reads and writes.
  • Run SET NOT NULL. Since PostgreSQL 12, it skips its own scan when a valid CHECK constraint already proves there are no NULLs. So this step only needs a brief lock.
  • Drop the CHECK constraint. It is now redundant, because the column itself is NOT NULL.

The same NOT VALID then VALIDATE pattern works for adding foreign keys to large tables.

  • SET NOT NULL alone scans the whole table under an ACCESS EXCLUSIVE lock, which blocks reads and writes.
  • NOT VALID adds the constraint without checking the old rows.
  • VALIDATE CONSTRAINT uses a weaker lock that still allows reads and writes.
  • In PostgreSQL 12 and later, SET NOT NULL uses the valid CHECK constraint to skip the scan.
  • The same pattern works for adding foreign keys to large tables.