Django migrate with AddIndex command to add index does not work with Postgres RDS

1. Ensure that the Migration Was Created Correctly

When using Django to define custom indexes in the Meta class of a model, the migration file should include an AddIndex operation. Ensure that the migration file generated by Django actually contains the AddIndex operations for the indices you expect.

For example, the migration file should contain entries like this:

# Generated migration file
from django.db import migrations, models

class Migration(migrations.Migration):

    dependencies = [
        ('yourapp', 'previous_migration'),
    ]

    operations = [
        migrations.AddIndex(
            model_name='yourmodel',
            index=models.Index(fields=['your_field'], name='your_index_name')
        ),
    ]

Make sure that AddIndex is present in the migration.

2. Check if Indexes Are Being Created Manually

Sometimes, the RDS PostgreSQL instance may have certain restrictions or configurations that prevent automatic index creation during migrations. You can check the indexes after running the migration by connecting to your RDS instance directly:

SELECT indexname FROM pg_indexes WHERE tablename = 'your_table';

This will list the indexes on a table. If the index is missing, it confirms that the migration did not execute the indexing operation.

3. Verify Permissions

Ensure that the database user running the migration has the correct permissions to create indexes. The user needs the CREATE privilege for the database or the specific schema where the indexes are being created.

You can check the privileges of the user with the following SQL query:

SELECT * FROM information_schema.role_table_grants WHERE grantee = 'your_db_user';

If the user does not have the necessary permissions, you can grant them like so:

GRANT CREATE ON DATABASE your_db_name TO your_db_user;

4. Elastic Beanstalk Container Commands Issue

Since you are running the migrations through Elastic Beanstalk's .ebextensions using container_commands, make sure that the environment is properly configured to run the migration commands.

Check your .ebextensions configuration to ensure the migration is being executed correctly. Here's an example .ebextensions configuration that runs Django migrations:

container_commands:
  01_migrate:
    command: "python manage.py migrate"
    leader_only: true

Make sure the migration command is executed as part of the deployment process. If you're using --noinput with the migration command (for automatic deployment), make sure no other issues are suppressing the output, which could cause silent failures.

5. Manual Index Creation in RDS

If all else fails, you can manually create the indexes directly in RDS PostgreSQL using SQL commands. You can execute the SQL statements using Django's dbshell or directly in a PostgreSQL client. For example:

CREATE INDEX your_index_name ON your_table (your_field);

You can also add these as raw SQL migrations in Django using the RunSQL operation:

from django.db import migrations

class Migration(migrations.Migration):

    dependencies = [
        ('yourapp', 'previous_migration'),
    ]

    operations = [
        migrations.RunSQL("CREATE INDEX your_index_name ON your_table (your_field);"),
    ]

This will create the index manually during the migration.