Hi Sergei - I just used the recipe on my production database. I didn't observe all the expected benefits, I wonder if there were confounding factors or if I did something wrong. If you have time, I'd love to get your feedback. Let me know if you need more info. I'd love to write a blog post informing the world about this potentially game-changing feature!
Here are the commands I did, with some notes. All the columns are boolean. The table has about 8,600,000 rows. This (blocking operation) was not fast, perhaps 60-100 seconds. maybe running them individually would have been proportionally faster. but even then, not near-instant as expected. or, maybe running them together had some sort of aggregate negative effect, so running them individually would have been instant? I don't have much experience with such constraints. ALTER TABLE my_table ADD CONSTRAINT my_table_column1_not_null CHECK (column1 IS NOT NULL) NOT VALID, ADD CONSTRAINT my_table_column2_not_null CHECK (column2 IS NOT NULL) NOT VALID, ADD CONSTRAINT my_table_column3_not_null CHECK (column3 IS NOT NULL) NOT VALID, ADD CONSTRAINT my_table_column4_not_null CHECK (column4 IS NOT NULL) NOT VALID; as expected these took as long as a table scan, and as expected they did not block. ALTER TABLE my_table validate CONSTRAINT my_table_column1_not_null; ALTER TABLE my_table validate CONSTRAINT my_table_column2_not_null; ALTER TABLE my_table validate CONSTRAINT my_table_column3_not_null; ALTER TABLE my_table validate CONSTRAINT my_table_column4_not_null; SLOW (table scan speed) - didn't have timing on, but I think about same time as the next one. ALTER TABLE my_table ALTER COLUMN column1 SET NOT NULL; 01:39 SLOW (table scan speed) ALTER TABLE my_table ALTER COLUMN column2 SET NOT NULL; 00:22 - 1/4 time of table scan but still not instant like expected ALTER TABLE my_table ALTER COLUMN column3 SET NOT NULL; 20.403 ms - instant, like expected ALTER TABLE my_table ALTER COLUMN column4 SET NOT NULL; all < 100ms ALTER TABLE my_table DROP CONSTRAINT my_table_column1_not_null; ALTER TABLE my_table DROP CONSTRAINT my_table_column2_not_null; ALTER TABLE my_table DROP CONSTRAINT my_table_column3_not_null; ALTER TABLE my_table DROP CONSTRAINT my_table_column4_not_null;
