Add NOT NULL constraint to large table without table scan

Is there a way to prevent a full table scan during the alter table statement?

At this time there is no supported, safe way to do that with PostgreSQL.

Some kind of ALTER TABLE ... ADD CONSTRAINT ... CONCURRENTLY would be nice, but nobody's implemented it. Same with the alternative of adding a NOT VALID constraint that still affects new rows, and that you then VALIDATE later - it'd be good, and it's something everyone knows is needed but nobody's had the time or funding to add yet.

In theory you could directly modify the system catalogs to add the constraint if you know it is true and valid. In practice, well, it's generally not a great idea.

So no, there isn't really a way.

One potential alternative is to create a check constraint using NOT VALID, then validating the check constraint later. This method requires holding an ACCESS EXCLUSIVE lock only for the duration to create the constraint, which should be on the order of milliseconds. The VALIDATE command will perform a time-consuming full table scan to validate the constraint, but it holds a less restrictive SHARE UPDATE EXCLUSIVE lock.

As for trade-offs, I've been unable to find any documentation that mentions internal mechanism differences between a standard NOT NULL constraint and a check constraint that validates a column is not null. I recall digging up a forum post that eluded to a potential performance difference, but have lost the link, so this is unconfirmed.

ALTER TABLE table ADD CONSTRAINT table_value_not_null_check CHECK (value IS NOT NULL) NOT VALID;

ALTER TABLE table VALIDATE CONSTRAINT table_value_not_null_check;

Sources:

https://www.postgresql.org/docs/9.4/static/sql-altertable.html https://www.postgresql.org/docs/9.4/static/explicit-locking.html