Opened 10 years ago

Last modified 23 months ago

#24363 new New feature

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

Reported by: slachinger Owned by: nobody
Component: Migrations Version: 1.7
Severity: Normal Keywords: Modify Field Column
Cc: simon.lachinger@…, emorley@…, Phil Krylov, Adam Johnson, elonzh 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 (7)

comment:1 by Tim Graham, 10 years ago

Triage Stage: Unreviewed → Someday/Maybe
Type: Uncategorized → New 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:3 by Ed Morley, 8 years ago

Cc: emorley@… added

comment:4 by Phil Krylov, 5 years ago

Cc: Phil Krylov added

comment:5 by Adam Johnson, 5 years ago

Cc: Adam Johnson added

comment:6 by elonzh, 2 years ago

Cc: elonzh added

comment:7 by elonzh, 23 months ago

Cc: elonzh removed

comment:8 by elonzh, 23 months ago

Cc: elonzh added
Note: See TracTickets for help on using tickets.
Back to Top