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?
[ Parent
| on HN ]
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.