Create composite index from a Django model

Question

I have the following model:

from django.db import models

class PopulationData(models.Model):
    slot = models.IntegerField(db_index=True)
    sample = models.IntegerField()
    value = models.FloatField()

    class Meta:
        unique_together = (('slot', 'sample'),)

And I would like to create also a compound index on the column pair that have the UNIQUE constraint, like so:

CREATE INDEX my_compound_index ON myapp_populationdata (slot, sample);

Right now I have a separate code connected to the post_syncdb signal that issues the previous SQL statement. Is there any way to indicate it from the model specification? (Note: I'm using the 1.3 branch).

Eugene Prikazchikov · Accepted Answer · 2023-06-02T08:06:18.457

Starting from django-1.5 you can make compound index using index_together meta option: https://docs.djangoproject.com/en/dev/ref/models/options/#index-together

Note (2023/01/30) index_together is deprecated since django-4.2. Instead, use much more powerful indexes meta option. With it you can create not only compound indexes, but also other types of indexes, e.g. function-based indexes, partial (conditional) indexes, covering indexes: https://docs.djangoproject.com/en/dev/ref/models/options/#django.db.models.Options.indexes

So, in your example index declaration would like this

from django.db import models

class PopulationData(models.Model):
    slot = models.IntegerField()
    sample = models.IntegerField()
    value = models.FloatField()

    class Meta:
        indexes = [
            models.Index("slot", "sample", name="population_slot_sample_idx")
        ]

new link https://docs.djangoproject.com/en/1.9/ref/models/options/#index-together — LennyLip, Apr 06 '16 at 08:39
latest link: https://docs.djangoproject.com/en/2.0/ref/models/options/#index-together — alphazwest, Dec 22 '17 at 18:05

score 8 · Answer 2 · answered Aug 21 '17 at 14:59

Starting from Django-1.11 use Meta.indexes option https://docs.djangoproject.com/en/1.11/ref/models/indexes/:

from django.db import models

class PopulationData(models.Model):
    slot = models.IntegerField(db_index=True)
    sample = models.IntegerField()
    value = models.FloatField()

    class Meta:
        unique_together = (('slot', 'sample'),)
        indexes = [
            models.Index(fields=['slot', 'sample']),
        ]

score 4 · Answer 3 · answered Nov 17 '20 at 02:41

Since a unique constraint also creates an index, it would be counterproductive to create both.

for example, from the postgres docs:

There's no need to manually create indexes on unique columns; doing so would just duplicate the automatically-created index.

Credit to Mark Byers for the doc link

If for some reason you still want to create a multi-column index, you can do so via index_together:

class PopulationData(models.Model):
    ...

    class Meta:
        index_together = [['slot', 'sample']]

It can't be the same index when one is compound though. They have completely different purposes. I don't see how it could result in creating the same index — toraman, Aug 23 '22 at 06:53

score 2 · Answer 4 · answered Apr 07 '13 at 18:44

I think that's not currently implemented in the django ORM.

If you use a migration tool (like south) that might be a good place to add that sql statement or if you preffer to avoid raw sql you could use sqlalchemy (core) but this case sounds simple enough to just go with sql.

score 2 · Answer 5 · answered Feb 21 '22 at 13:57

Note: index_together may be deprecated in the future: https://docs.djangoproject.com/en/dev/ref/models/options/#index-together

Use indexes in the Meta class instead:

from django.db import models

class Customer(models.Model):
    first_name = models.CharField(max_length=100)
    last_name = models.CharField(max_length=100)

    class Meta:
        indexes = [
            models.Index(fields=['last_name', 'first_name']),
            models.Index(fields=['first_name'], name='first_name_idx'),
        ]

Create composite index from a Django model

5 Answers5