Migration of Django field with default value to PostgreSQL database

Question

https://docs.djangoproject.com/en/1.10/topics/migrations/

Here it says: "PostgreSQL is the most capable of all the databases here in terms of schema support; the only caveat is that adding columns with default values will cause a full rewrite of the table, for a time proportional to its size.

"For this reason, it’s recommended you always create new columns with null=True, as this way they will be added immediately."

I am asking if I get it correct. From what I understand, I should first create the field with null=True and no default value then migrate it and then give it a default value and migrate it again, the values will be added immediately, but otherwise the whole database would be rewritten and Django migration doesn't want to do the trick by itself?

Answer 1

It's also mentioned in that same page that:

In addition, MySQL will fully rewrite tables for almost every schema operation and generally takes a time proportional to the number of rows in the table to add or remove columns. On slower hardware this can be worse than a minute per million rows - adding a few columns to a table with just a few million rows could lock your site up for over ten minutes.

and

SQLite has very little built-in schema alteration support, and so Django attempts to emulate it by:

Creating a new table with the new schema Copying the data across Dropping the old table Renaming the new table to match the original name

So in short, what that statement you are referring to above really says is

postgresql exhibits mysql like behaviour when adding a new column with a default value

The approach you are trying would work. Adding a column with a null would mean no table re write. You can then alter the column to have a default value. However existing nulls will continue to be null

Answer 2

The way I understand it, on the second migration the default value will not be written to the existing rows. Only when a new row is created with no value for the default field it will be written.

I think the warning to use null=True for new column is only related to performance. If you really want all the existing rows to have the default value just use default= and accept the performance consequence of a table rewrite.