Add NOT NULL constraint to large table without table scan

All we need is an easy explanation of the problem, so here it is.

Trying to add a NOT NULL constraint to a table with 1 billion rows. I cannot afford a table lock for more than a couple of seconds. Is there a way to prevent a full table scan during the alter table statement? I created an index on the column hoping it would be used but that doesn’t seem to work. May be a check constraint? Other options? Thank you!

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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.

Method 2

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply