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!
Trying to add a
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;