Re:Feature Casualties of Large Databases
by yc12340 on Tuesday December 01, 2020 @11:18PM
(#25273199)
> First you add a not null check NOT VALIDATED - Then you add a not null constraint (it will require a full table scan, but with minimal locking) - Afterwards you can remove the unnecessary not null check
This is still quite long and complicated. What stops Postgres from automatically tracking whether a column has any nulls on each insert?
Re:Feature Casualties of Large Databases
by zozbot234 on Tuesday December 01, 2020 @08:21PM
(#25272237)
If that's feasible, shouldn't Postgres do it automatically when adding that constraint (perhaps optionally, denoted by something like CONCURRENTLY)? Where's the catch?
The catch is the time you need to wait / migrate the newly invalid data.
All that NOT VALID does is add the validation for inserts and updates but does not check the existing data. This does let it get away with a less restrictive level of locking.
However there may be data which is not valid for a long long time, the "concurrently" query could be running forever.
It is not easy, but at least it is very viable to add not null constraints to Postgres 12+ with minimal locking: - First you add a not null check NOT VALIDATED - Then you add a not null constraint (it will require a full table scan, but with minimal locking) - Afterwards you can remove the unnecessary not null check