How to specify the name while concurrently removing indexes from database

I have some field in my table for which i need to remove indexing. In the django application, i saw that i could do this using the migrations.RemoveIndexConcurrently() method. However im having confusions on how to specify the name attribute with it. The previously said indexed fields were added during the time of creating the table and hence there is no separate AddIndex migration. Need to remove indexing for these fields in 2 different environments and when i looked up the names using

SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'my_db_table_name'

i saw indexnames like user_secondary_mail_779d505a_like, which could be different in the second environment. Is there any way i could specify the names of the fields so that i could run the migration in both environments. Any help would be greatly appreciated!

You want to remove an index in a Django migration using:

from django.contrib.postgres.operations import RemoveIndexConcurrently

The name argument is required, but:

You don’t have an explicit AddIndex migration, because the index was created automatically with the table.
PostgreSQL may generate different names in different environments (user_secondary_mail_779d505a_like, etc.).
You want a migration that works in both environments.

So you cannot hardcode the index name, because it varies.

Solution

You have a few options.

Option A: Let Django generate the index name dynamically

RemoveIndexConcurrently expects a models.Index instance rather than the raw name. If you define the index like Django would generate it, Django can figure out the correct name for you:

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

class Migration(migrations.Migration):
    dependencies = [
        ('myapp', '0001_initial'),
    ]

    operations = [
        RemoveIndexConcurrently(
            model_name='user',
            index=models.Index(fields=['secondary_mail'], name='dummy'),  # name won't be used
        ),
    ]

But here’s the tricky part: Django needs a matching Index object to generate the index name. For a LIKE operator (text search), the automatically created index might be a GinIndex or BTreeIndex depending on your field type and db_index.

So, if it was an automatic LIKE index for a CharField, you may need to recreate the index object exactly like Django would:

from django.db import models
from django.contrib.postgres.operations import RemoveIndexConcurrently

RemoveIndexConcurrently(
    model_name='user',
    index=models.Index(fields=['secondary_mail'], name='secondary_mail_idx'),
)

Then Django generates the proper name for that index for your database. You can check by running:

python manage.py sqlmigrate myapp 0002

Option B: Use `RunSQL` with `DROP INDEX CONCURRENTLY`

If the index name really differs per environment, the simplest reliable method is to dynamically detect it in SQL:

from django.db import migrations

class Migration(migrations.Migration):
    dependencies = [
        ('myapp', '0001_initial'),
    ]

    operations = [
        migrations.RunSQL(
            sql="""
            DO $$
            DECLARE idx_name text;
            BEGIN
                SELECT indexname INTO idx_name
                FROM pg_indexes
                WHERE tablename='my_db_table_name'
                AND indexdef LIKE '%secondary_mail%';
                
                IF idx_name IS NOT NULL THEN
                    EXECUTE format('DROP INDEX CONCURRENTLY %I', idx_name);
                END IF;
            END $$;
            """,
            reverse_sql="""
            -- optionally recreate the index here
            """,
        ),
    ]

This works regardless of the actual index name, so you don’t have to worry about differences between environments.

Recommendation

If the index was created automatically by Django, try Option A first and check sqlmigrate to ensure Django resolves the index name correctly.
If names vary too much, or you want a safe migration across environments, use Option B with RunSQL to dynamically drop the index.

Вернуться на верх