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!
-
What on earth are you running that you cannot afford a table lock for more than a couple of seconds?The Universe software?Mihai– Mihai2014-06-08 18:24:18 +00:00Commented Jun 8, 2014 at 18:24
-
4:) collecting real time data but only for a very small part of the universe.Volker Hauf– Volker Hauf2014-06-09 05:55:26 +00:00Commented Jun 9, 2014 at 5:55
-
1Just thoughts... That's weird it uses full table scan if you have an index. If it would be using the index, as index lookup for a non-existing value should be lighting fast... so the lock will not be a problem.kan– kan2015-05-20 21:23:12 +00:00Commented May 20, 2015 at 21:23
-
2As of PG 12, we now have the ability to concurrently add NOT NULL! dba.stackexchange.com/a/268128/12659John Bachir– John Bachir2020-06-02 14:20:01 +00:00Commented Jun 2, 2020 at 14:20
-
(btw, the question that this question is marked a duplicate of is actually about something else - if an admin sees this, could you undo that so that a real answer for PG 12+ can be added)John Bachir– John Bachir2020-06-02 14:20:43 +00:00Commented Jun 2, 2020 at 14:20
Add a comment
|
2 Answers
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.
-
1Thank you! In case somebody wants to implement: While adding constraints concurrently would be fantastic, just looking at indexes before scanning the entire table would already be very helpful.Volker Hauf– Volker Hauf2014-06-09 06:02:45 +00:00Commented Jun 9, 2014 at 6:02
-
In the off chance that the column on which you want to add 'not null' is unique, you might be able to use the technique described here: http://stackoverflow.com/a/20006502.Brian Hahn– Brian Hahn2014-12-18 20:54:14 +00:00Commented Dec 18, 2014 at 20:54
-
6For the record: The
NOT VALIDfeature has since been added forCHECKandFKconstraints. Related: dba.stackexchange.com/questions/75613/… or dba.stackexchange.com/questions/158499/…Erwin Brandstetter– Erwin Brandstetter2016-12-20 00:37:51 +00:00Commented Dec 20, 2016 at 0:37 -
1You still have to
VALIDATEthe constraint later though, there's no way to force it to be considered valid. You can, however, twiddle the catalogs to lie about that much more easily than creating a constraint from whole cloth correctly.Craig Ringer– Craig Ringer2016-12-20 11:30:03 +00:00Commented Dec 20, 2016 at 11:30 -
1As of PG 12, we now have the ability to concurrently add NOT NULL! dba.stackexchange.com/a/268128/12659John Bachir– John Bachir2020-06-02 14:19:39 +00:00Commented Jun 2, 2020 at 14:19
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
-
1The benchmark discussing the difference between a standard
NOT NULLconstraint and a check constraint is here: dba.stackexchange.com/questions/158499/…stereoscott– stereoscott2018-03-02 18:07:49 +00:00Commented Mar 2, 2018 at 18:07 -
You can also convert the CHECK constraint to a real NOT NULL afterwards without locking the table for a large amount of time since PG 12: dba.stackexchange.com/a/268128/137516phiresky– phiresky2022-01-17 16:13:50 +00:00Commented Jan 17, 2022 at 16:13