Hi Sergei - I just used the recipe on my production database. I didn't observe all the expected benefits, I wonder if
therewere confounding factors or if I did something wrong. If you have time, I'd love to get your feedback. Let me know
ifyou 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;