Opened 3 years ago

Last modified 10 months ago

#24363 assigned New feature

Combine ALTER TABLE .. MODIFY statements for multiple columns into one statement.

Reported by: slachinger Owned by: ambivalentno
Component: Migrations Version: 1.7
Severity: Normal Keywords: Modify Field Column
Cc: simon.lachinger@…, emorley@… Triage Stage: Someday/Maybe
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

If a migration modifies multiple columns of a table for each column/field an ALTER TABLE statement is executed. However most DBMS (all but sqlite?) support modification of several columns at once.

For example, the consider this migration:

class Migration(migrations.Migration):

    dependencies = [
        ('core', '0001_initial'),
    ]

    operations = [
        migrations.AlterField(
            model_name='employee',
            name='salary',
            field=models.BigIntegerField(),
            preserve_default=True,
        ),
        migrations.AlterField(
            model_name='employee',
            name='academic_degrees',
            field=models.CharField(max_length=200),
            preserve_default=True,
        ),
    ]

This currently results in two ALTER TABLE statments being executed:

ALTER TABLE employee MODIFY COLUMN salary bigint;
ALTER TABLE employee MODIFY COLUMN academic_degrees varchar(200);

But this takes twice as long as using only one ALTER TABLE statement since the whole table is essentially recreated twice (at least in MySQL but IIRC it is the same in PG):

ALTER TABLE employee MODIFY COLUMN salary bigint, MODIFY COLUMN academic_degrees varchar(200);

This is particulary annoying because it essentially multiplies the time the migration runs by the number of modified fields and thus causes much longer downtimes of the service.

NOTE: this is most likely related to #24203 which refers to adding columns instead of modifying.

Change History (3)

comment:1 Changed 3 years ago by Tim Graham

Triage Stage: UnreviewedSomeday/Maybe
Type: UncategorizedNew feature

As I said in #24203, some performance numbers would be useful to assess the benefits of this versus the cost of additional code complexity.

comment:2 Changed 2 years ago by ambivalentno

Owner: changed from nobody to ambivalentno
Status: newassigned

comment:3 Changed 10 months ago by Ed Morley

Cc: emorley@… added
Note: See TracTickets for help on using tickets.
Back to Top