Do django db_index migrations run concurrently?

Question

I'm looking to add a multi-column index to a postgres database. I have a non blocking SQL command to do this which looks like this:

CREATE INDEX CONCURRENTLY shop_product_fields_index ON shop_product (id, ...);

When I add db_index to my model and run the migration, will it also run concurrently or will it block writes? Is a concurrent migration possible in django?

score 14 · Accepted Answer · answered Nov 20 '19 at 14:29

There are AddIndexConcurrently and RemoveIndexConcurrently in Django 3.0:

https://docs.djangoproject.com/en/dev/ref/contrib/postgres/operations/#django.contrib.postgres.operations.AddIndexConcurrently

Create a migration and then change migrations.AddIndex to AddIndexConcurrently. Import it from django.contrib.postgres.operations.

score 13 · Answer 2 · edited Jan 23 '23 at 11:29

With Django 1.10 migrations you can create a concurrent index by using RunSQL and disabling the wrapping transaction by making the migration non-atomic by setting atomic = False as a data attribute on the migration:

class Migration(migrations.Migration):
    atomic = False # disable transaction

    dependencies = []

    operations = [
        migrations.RunSQL('CREATE INDEX CONCURRENTLY ...')
    ]

RunSQL: https://docs.djangoproject.com/en/stable/ref/migration-operations/#runsql
Non-atomic Migrations: https://docs.djangoproject.com/en/stable/howto/writing-migrations/#non-atomic-migrations

score 10 · Answer 3 · answered May 30 '19 at 15:09

You could use the SeparateDatabaseAndState migration operation to provide a custom SQL command for creating the index. The operation accepts two lists of operations:

state_operations are operations to apply on the Django model state. They do not affect the database.
database_operations are operations to apply to the database.

An example migration may look like this:

from django.db import migrations, models

class Migration(migrations.Migration):
    atomic = False

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

    operations = [    
        migrations.SeparateDatabaseAndState(    
            state_operations=[
                # operation generated by `makemigrations` to create an ordinary index
                migrations.AlterField(
                    # ...  
                ),
            ],

            database_operations=[
                # operation to run custom SQL command (check the output of `sqlmigrate`
                # to see the auto-generated SQL, edit as needed)
                migrations.RunSQL(sql='CREATE INDEX CONCURRENTLY ...',
                                  reverse_sql='DROP INDEX ...'),
            ],
        ),
    ]

score 1 · Answer 4 · answered Apr 03 '19 at 00:08

Do what tgroshon says for new django 1.10 +

for lesser versions of django i have had success with a more verbose subclassing method:

from django.db import migrations, models


class RunNonAtomicSQL(migrations.RunSQL):
    def _run_sql(self, schema_editor, sqls):
        if schema_editor.connection.in_atomic_block:
            schema_editor.atomic.__exit__(None, None, None)
        super(RunNonAtomicSQL, self)._run_sql(schema_editor, sqls)


class Migration(migrations.Migration):
    dependencies = [
    ]

    operations = [

        RunNonAtomicSQL(
            "CREATE INDEX CONCURRENTLY",
        )
    ]

score 0 · Answer 5 · edited Jan 23 '23 at 11:30

0

There is no support for PostgreSQL concurent index creation in django.

Here is the ticket requesting this feature - https://code.djangoproject.com/ticket/21039

But instead, you can manually specify any custom RunSQL operation in the migration - https://docs.djangoproject.com/en/stable/ref/migration-operations/#runsql

edited Jan 23 '23 at 11:30

Jieter

4,101
1
19
31

answered May 30 '15 at 01:03

kmmbvnr

5,863
4
35
44

6

You can't run `CREATE INDEX CONCURRENTLY` inside a transaction, and AFAIK Django always runs migrations inside a transaction (at least on Postgres). – Pankrat Jul 21 '15 at 06:50
3

@Pankrat 1.10 will support [non-transactional migrations](https://code.djangoproject.com/ticket/25833), so this should be possible now. – rcoup Jun 16 '16 at 07:27

score 0 · Answer 6 · answered Jul 29 '21 at 10:15

You can do something like


import django.contrib.postgres.indexes
from django.db import migrations, models
from django.contrib.postgres.operations import AddIndexConcurrently


class Migration(migrations.Migration):

    atomic = False

    dependencies = [
        ("app_name", "parent_migration"),
    ]

    operations = [
        AddIndexConcurrently(
            model_name="mymodel",
            index=django.contrib.postgres.indexes.GinIndex(
                fields=["field1"],
                name="field1_idx",
            ),
        ),
        AddIndexConcurrently(
            model_name="mymodel",
            index=models.Index(
                fields=["field2"], name="field2_idx"
            ),
        ),
    ]

Ref: https://docs.djangoproject.com/en/dev/ref/contrib/postgres/operations/#django.contrib.postgres.operations.AddIndexConcurrently

Do django db_index migrations run concurrently?

6 Answers6