Default values on new columns #2
Description
最近看到一篇文章Five Mistakes Beginners Make When Working With Databases，其中有一個不解的是 4. Default values on new columns
No matter how long you’ve been at it you won’t get the perfect schema on day 1. It’s better to think of database schemas as continuously evolving documents. Fortunately, it’s easy to add a column to your database, but: it’s also easy to do this in a horrific way. By default, if you just add a column it’ll generally allow NULL values. This operation is fast, but most applications don’t truly want null values in their data, instead they want to set the default value.
If you do add a column with a default value on the table, this will trigger a full re-write of your table. Note: this is very bad for any sizable table on an application. Instead, it’s far better to allow null values at first so the operation is instant, then set your default, and then, with a background process go and retroactively update the data.
真的會這樣嗎? 還是只有 Postgres 會這樣? (因為看該 blog 好像只有這個分類)
MySQL and Oracle 都會. 只是做的當下會不會lock table.
所以後來加的欄位，我們盡可能不要設定default value.
不過base on 你的資料量大小。如果太小，可能full table write 也只是1-2秒就搞定了
看起來就是要動 scheme 要小心，盡量要避免 lock table 的情況