Database indexes in Django 1.11: difference between db_true, indexes and index_together

Question

Django 1.11 offers new ways to create database indexes. So far we had db_index=True in each field:

# example 1

class Person(models.Model):
    name = models.CharField(db_index=True)
    age = models.IntegerField(db_index=True)

Now we have models.Index and the possibility of declaring indexes within the class Meta block — or even index_together.

That said I have two doubts:

1. Is the code from example 1 doing the same thing as example 2 below?

# example 2

class Person(models.Model):
    name = models.CharField()
    age = models.IntegerField()

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

2. What about index with multiple fields and index_together: are examples 3 and 4 below doing exactly the same thing?

# example 3

class Person(models.Model):
    name = models.CharField()
    age = models.IntegerField()

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

# example 4

class Person(models.Model):
    name = models.CharField()
    age = models.IntegerField()

    class Meta:
        index_together = [['name', 'age']]

What are the diferences between 1 and 2, and differences between 3 and 4? What am I missing? Many thanks.

Razor

Answer 1

According to the docs, you can give indexes a name and you can set an order for each field in the index (but not all DBs are equally supported). But in reality the whole point of this is to bring extra flexibility to define other types of indexes where supported.

Currently as of Django 1.11 and only when using Postgres you can define GIN and BRIN indexes if that is something that would fit your needs. (see django.contrib.postgres.indexes at https://docs.djangoproject.com/en/1.11/ref/contrib/postgres/indexes/)

Being able to define indexes as you described is just the most general case and unless you want to use a feature named above (naming indexes or ordering fields) you can probably use indexes as you've been doing until now (db_index and index_together).

Submit

Just an extra question: if I change from example 1 to example 2 and run makemigrations Django dumps all my previous indexes and recreate them – is this expected? Why? – Razor

I'm a bit unsure about that, but I guess Django just doesn't know how those features are related even though they achieve the same thing. When migrations are being computed I assume that Django notices that you dropped the db_index=True or index_together, which translates in dropping the index. At the same time Django notices that you added idexes and thus it creates them. It just seems to be unable to relate one to another. This should not be a problem unless you have really massive indexes that would get recreated by doing this. You'd need to evaluate your particular case. – Basilbasilar

It sort of surprises me as the new indexes feature is just generalising db_index and index_together. They could've translated the classic way of handling them to the new one and add some checks that tell Django they are equivalent. I guess there are some extra technical reasons I don't know why they took the decision to not handle them this way. Maybe someone else could shed some light here. – Basilbasilar

Found this blog - markusholtermann.eu/2017/04/to-index-or-not-is-not-the-question. Must read. – Vardhamana

Answer 2

In Django, the db_index=True option is used in model field and indexes used in meta. Their concept is same. They creates an index (B-Tree) in the database.. You may either use db_index_True or meta indexes

Yes, The code

• example-1 and example-2 doing the same thing

# example-1
class Person(models.Model):
    name = models.CharField(db_index=True)
    age = models.IntegerField(db_index=True)

# example-2
class Person(models.Model):
    name = models.CharField()
    age = models.IntegerField()

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

• example-3 and example-4 are same as well

# example 3
class Person(models.Model):
    name = models.CharField()
    age = models.IntegerField()

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

# example 4

class Person(models.Model):
    name = models.CharField()
    age = models.IntegerField()

    class Meta:
        index_together = [['name', 'age']]

Facility