Add column and set default may be a safe action about django-pg-zero-downtime-migrations HOT 5 CLOSED

I use next definition for safe migration: no select, insert, update, delete operation will be affected (fail or timeout) during migration and deployment.

In case of ADD COLUMN DEFAULT NULL:

• old code works fine with old schema
• old code will insert nulls to new schema, no fail there
• new code works fine with new schema

NULL means column values can be nullable that semantically correct, so ADD COLUMN DEFAULT NULL should be considered as safe migration.

In case of ADD COLUMN DEFAULT NOT NULL:

• old code works fine with old schema
• old code will try to insert nulls to new schema and will fail
• new code works fine with new schema

So ADD COLUMN DEFAULT NOT NULL is unsafe migration.

Looks previously this two cases were considered as same and was handled same way, so they should be separated.

You correct that in postgres 11+ no table rewrite happens and only schema changes is safe operation, this library documentation should be fixed.

from django-pg-zero-downtime-migrations.

Add column with default looks next https://github.com/tbicr/django-pg-zero-downtime-migrations/blob/master/tests/unit/test_schema.py#L241-L242:

ALTER TABLE "tests_model" ADD COLUMN "field" varchar(40) DEFAULT 'test' NULL;
ALTER TABLE "tests_model" ALTER COLUMN "field" DROP DEFAULT;

Lets consider two cases:

1. add column with default and null
   in this case inserts between migration and deployment will be reason of null values that can be not expected

2. add column with default and not null
   in this case all inserts between migration and deployment will be reason of fails that can be not accepted

so this is reason as in this lib adding column with default considered unsafe

in django 5 you can use https://docs.djangoproject.com/en/5.0/ref/models/fields/#db-default that is better way to use default, I'd strongly recommend

default explanation mentioned there https://github.com/tbicr/django-pg-zero-downtime-migrations/tree/master?tab=readme-ov-file#create-column-with-default

however option keeping default after migration and considered this operation as safe is interesting option

from django-pg-zero-downtime-migrations.

Thank you for your quick and thorough response - I understand it better now.
I have 2 follow-up questions:

1. I am wondering if the operation could still be considered safe, from a downtime prespective? Yes - there is still the possibility of NULL values in the code (in the case of between migration and deployment) but would it still be unsafe with respect to downtime? Or, is somehow having NULL values considered unsafe and could cause downtime?
2. The reason in row 8 for why it is unsafe:

because you spend time in migration to populate all values in table

Is this still true? I was thinking that since Postgres 11, it had been addressed:

adding a column with a constant default value no longer means that each row of the table needs to be updated when the ALTER TABLE statement is executed.

So since each row doesn't need to be updated when ALTER TABLE is executed, this would mean that the migration that generates this SQL would not need time to populate the values in the table, during the migration:

Instead, the default value will be returned the next time the row is accessed, and applied when the table is rewritten

Thank you in advance for your time and response!

from django-pg-zero-downtime-migrations.

Ok - great. The explanation provided makes sense to me, thank you.

I can leave this issue open, and the update can be created against it to track? Or if a new issue is preferred, feel free to close this out.

Thank you again for your response and explanations.

from django-pg-zero-downtime-migrations.

defaults was reworked in 0.16

from django-pg-zero-downtime-migrations.