How can I set a column to NOT NULL without locking the table during a table scan?

Another answer from another postgresql contributor.

PostgreSQL will not even try to use any indexes during execution of "alter table set not null". It is just not implemented.

Proper implementation of index scan is difficult part. We cannot just do something like this query

select exists(select from foos where bar1 is null)

from alter table command for various reasons. Such feature will require big piece of code (and, probably, fragile code under some edge cases), much work, only for limited use cases. Something what developers do not like. Actually, pgsql-hackers community does not like how NOT NULL is stored in system catalog. It would be cleaner with a redesign of this part of the catalog. After that, it would be possible to do SET NOT NULL NOT VALID with a short lock and table validation without an exclusive lock. Similar to alter table .. add constraint ... not valid + alter table .. validate constraint for check constraint or foreign key. But such redesign is much more work, and there is no one who wants to do it.

But I have good news: in PostgreSQL 12 (and above) scan the entire table is not the only option. alter table set not null can prove correctness of NOT NULL by existing check constraints. So, one can do:

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

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

-- 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;

That was my patch. Yeah, this looks like a workaround. But, fortunately, it was merged and now it is possible to set not null without a long exclusive lock.

I looked at the source code (function ATRewriteTable in src/backend/commands/tablecmds.c), and PostgreSQL always uses a sequential scan of the table to verify NOT NULL constraints.

So creating indexes won't speed up the execution.