Option to force ALGORITHM=INSTANT during ALTER migrations

[strokirk]

Description

With many MySQL ALTER statements now supporting Online DDL, it would be a great feature to be able to force Django to always add ALGORITHM=INSTANT where possible.

While MySQL mostly uses the "best algorithm it can", this is mostly to safeguard against MySQL silently downgrading to another algorithm, which can easily cause site-wide downtime. Better a failed migration than a locked database.

Would this be a reasonable feature to add? I'd be happy to try to make a PR if so, assuming I can understand where to make it.

(Essentially the same feature request as in: https://code.djangoproject.com/ticket/30773)

[adamchainz]

I can see how a safeguard feature would be useful.

The implementation I can think of: override the migrate command and add an option to enable the feature like --force-algorithm-instant. The handle() method could add database instrumentation to rewrite ALTER TABLE queries to insert ALGORITHM=INSTANT, similar to the technique in this post.

Overriding migrate is a little tricky, as it means that other apps can't also override it without inheriting from Django-MySQL's version. I am not sure many other packages override migrate but it's worth bearing in mind.

(Whitenoise jumps through some hoops to inherit from the correct runserver command in its override.)

Basically, I'd be happy to review an initial version but I'm on the fence about adding it to the package. With this kind of feature it's best to have another voice saying it's desirable before I commit to maintaining it.