6 ms·
> Make it easy to make a nullable field non-nullable, not requiring a problematic and immediate full table scan. It is not easy, but at least it is very viable
by giovannibonetti 6y ago
> Make it easy to make a nullable field non-nullable, not requiring a problematic and immediate full table scan.
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
- zozbot234 6y agoIf that's feasible, shouldn't Postgres do it automatically when adding that constraint (perhaps optionally, denoted by something like CONCURRENTLY)? Where's the catch?
- desas 6y agoThe 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.
- yc12340 6y ago> 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?