3 ms·
The article does address this, it still has to check the DB for NULLs after you're done. Less downtime, but for huge tables it does take a significant amount of
by gcbirzan 8y ago
The article does address this, it still has to check the DB for NULLs after you're done. Less downtime, but for huge tables it does take a significant amount of time.
- anarazel 8y agoThis optimization applies only for new columns with a stable default. Therefore a table scan is not required to check for NULLs, as either the default is NULL or not.
- gcbirzan 8y agoI was talking about the way to do it before. You could create a NULL-able column, add the default in batches, add the default/make it non-NULL-able. But, the last part took some time (not in the range of ms) for large tables.
- pilif 8y ago> But, the last part took some time (not in the range of ms) for large tables create a temporary index covering that column. Then it will be in the range of ms.
- pilif 8y agoyou can create a temporary conditional index on that table, then the check will be instantaneous. There is a `create index concurrently` that won't cause locks on the table. With this the re-check for nulls when adding the not null constraint will not take any time at all.