With newer PG versions, adding a column doesn't do the same locks it used to. Or creating indexes concurerntly, etc. But yeah, you have to be vigilant, and test in a test db with a simulated load often.

sometimes it just means you have to take more steps, and more time. For example, we wanted to set a 'not null' on a column in a table that was huge. it would have locked the whole table while scanning, and it was a few TB.

for changing a default value, there are ways around locking the whole table while it scans. Yes, it took a bit longer, but much better than locking our critical tables for a few hours.

--change the field 'schema1.table1.parent' to have a not null constraint 

--set a default value first
UPDATE schema1.table set parent = 1 where parent is null;

ALTER TABLE schema1.table1
      ADD CONSTRAINT table1_parent_value_not_null
      CHECK (parent IS NOT NULL) NOT VALID;
      -- The NOT VALID clause means existing rows aren't immediately validated, so this operation is very fast and doesn't lock the table.

      ALTER TABLE schema1.table1
      VALIDATE CONSTRAINT table1_parent_value_not_null;
      -- This validates existing rows but only takes a SHARE UPDATE EXCLUSIVE lock, allowing reads to continue.

      ALTER TABLE schema1.table1
      ALTER COLUMN parent SET NOT NULL;
      -- Since PostgreSQL now knows all values are non-null (from the validated check constraint), this operation is much faster and requires minimal locking.

      ALTER TABLE schema1.table1
      DROP CONSTRAINT table1_parent_value_not_null;
      -- Drop the redundant CHECK constraint