django migrations on live database for large tables for postgresql

[pavel.t...@gmail.com]

Hi,

I found that django migrations don't designed for live updates on large tables for postgres, eg. index creation doesn't use concurrently or alter table operation can take ACCESS EXCLUSIVE lock for long time.

Before I mostly wrote custom migrations, but after one of downtime I decided to avoid most common cases at all and wrote postgres backend that helps me in it: https://github.com/tbicr/django-pg-zero-downtime-migrations. When I wrote it I found that it's a pretty easy extend core postgres backend, but a few things required rewrite on my side:

- https://github.com/django/django/blob/master/django/db/backends/base/schema.py#L152 used both for table columns creation and altering table add column, but not null constraint and index constraints can't be changed without whole method rewriting
- for one core sql statement I found more "safe" equivalent with two sql statements, but it's a bit hard to execute it, for example more save create invalid constraint and than validate it or create concurrently index and than create unique constraint

also I found that not all constraint names quoted in generated sql, for example add_unique_together got `ALTER TABLE "table_name" ADD CONSTRAINT table_name_f1_f2_hash_uniq UNIQUE ("f1", "f2")`, but expected `ALTER TABLE "table_name" ADD CONSTRAINT "table_name_f1_f2_hash_uniq" UNIQUE ("f1", "f2")`

So I just curious is anybody interested to make django migrations more reliable for large tables in prod or interested in some improvements highlighted above, that can simplify writing backends for this purpose?

I also will be appreciated if somebody can share another solutions for this purpose.

Paveł

[Aymeric Augustin]

Hello Pavel,

There's an accepted ticket about CREATE INDEX CONCURRENTLY: https://code.djangoproject.com/ticket/21039. A concrete proposal for adding this feature to Django would be interesting.

I'm not really sure about your two other suggestions. If you think they're improving the PostgreSQL backend, you're welcome to create a ticket and propose a pull request.

Keep in mind backwards-compatibility if changes are improvements in your use case but perhaps not for everyone else. For example, it may be better that index creating remain an atomic operation (so, not concurrent) by default.

Best regards,

-- 

Aymeric.

--
You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-develop...@googlegroups.com.
To post to this group, send email to django-d...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/421f001b-ca11-42bf-8735-0c6947b41988%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[pavel.t...@gmail.com]

Hello, Aymeric

Thanks for your reply.

CREATE INDEX CONCURRENTLY is a big part for zero downtime migrations support, but there are too many additional questions as should ALTER TABLE ADD COLUMN UNIQUE splitted to CREATE INDEX and constraint creation or should be handled FOREGN KEY, CHECK and NOT NULL constraints creation that can take ACCESS EXCLUSIVE for long time on huge table or is it accessible to avoid transactions in highlighted above cases?

Unfortunately I don't know what direction django planned to develop, so I can go different ways without support.

Is it a good idea to create ticket about zero downtime migrations support with detailed proposition of improvements and backward compatibility support?

Regards, Paveł

[Aymeric Augustin]

Hello Pavel,

Sorry for the delay in my answer.

Unfortunately I don't know django.db.migrations very well and I won't be able to help you with the details of a patch in this area.

I still think improving support for zero downtime migrations would be worthwhile. Writing a proposition in a ticket (or, if you're feeling really ambition, in a DEP) would be a good way to start.

Sorry I can't help more!

Best regards,

-- 

Aymeric.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/c14b53e6-3995-4c21-9993-dcaf8e22b825%40googlegroups.com.