17

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!

ypercubeᵀᴹ
  • 97,895
  • 13
  • 214
  • 305
Volker Hauf
  • 173
  • 1
  • 4
  • What on earth are you running that you cannot afford a table lock for more than a couple of seconds?The Universe software? – Mihai Jun 08 '14 at 18:24
  • 4
    :) collecting real time data but only for a very small part of the universe. – Volker Hauf Jun 09 '14 at 05:55
  • 1
    Just 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 May 20 '15 at 21:23
  • 2
    As of PG 12, we now have the ability to concurrently add NOT NULL! https://dba.stackexchange.com/a/268128/12659 – John Bachir Jun 02 '20 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 Jun 02 '20 at 14:20

2 Answers2

11

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

graywolf
  • 5
  • 3
Marcus
  • 111
  • 1
  • 3
  • 1
    The benchmark discussing the difference between a standard NOT NULL constraint and a check constraint is here: https://dba.stackexchange.com/questions/158499/postgres-how-is-set-not-null-more-efficient-than-check-constraint – stereoscott Mar 02 '18 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: https://dba.stackexchange.com/a/268128/137516 – phiresky Jan 17 '22 at 16:13
11

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.

Craig Ringer
  • 56,343
  • 5
  • 158
  • 190
  • 1
    Thank 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 Jun 09 '14 at 06: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 Dec 18 '14 at 20:54
  • 5
    For the record: The NOT VALID feature has since been added for CHECK and FK constraints. Related: http://dba.stackexchange.com/questions/75613/disable-all-constraints-and-table-checks-while-restoring-a-dump/75635#75635 or http://dba.stackexchange.com/questions/158499/postgres-how-is-set-not-null-more-efficient-than-check-constraint – Erwin Brandstetter Dec 20 '16 at 00:37
  • 1
    You still have to VALIDATE the 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 Dec 20 '16 at 11:30
  • 1
    As of PG 12, we now have the ability to concurrently add NOT NULL! https://dba.stackexchange.com/a/268128/12659 – John Bachir Jun 02 '20 at 14:19