CODEWITHSUNDEEP
pythondjangodjango-modelsdatabase-indexes
orokusaki
orokusaki

Reputation: 57198

Django: Does unique_together imply db_index=True in the same way that ForeignKey does?

A field on a model, foo = models.ForeignKey(Foo) will automatically add a database index for the column, in order to make look-ups faster. That's good and well, but Django's docs don't state whether the fields in a model-meta's unique_together receive the same treatment. I happen to have a model in which one char field which is listed in unique_together requires an index for quick lookups. I know that it won't hurt anything to add a duplicate db_index=True in the field definition, but I'm curious.

Upvotes: 35

Views: 12508

Answers (5)

auvipy
auvipy

Reputation: 1208

unique_together does not automatically add indexes for each field included in the list.

The new versions of Django suggest using Index & constraint meta options instead:

https://docs.djangoproject.com/en/3.2/ref/models/options/#unique-together

https://docs.djangoproject.com/en/3.2/ref/models/options/#index-together

https://docs.djangoproject.com/en/dev/ref/models/indexes/

And an example model from an open source project:

class GroupResult(models.Model): """Task Group result/status."""

group_id = models.CharField(
    max_length=getattr(
        settings,
        "DJANGO_CELERY_RESULTS_TASK_ID_MAX_LENGTH",
        255
    ),
    unique=True,
    verbose_name=_("Group ID"),
    help_text=_("Celery ID for the Group that was run"),
)
date_created = models.DateTimeField(
    auto_now_add=True,
    verbose_name=_("Created DateTime"),
    help_text=_("Datetime field when the group result was created in UTC"),
)
date_done = models.DateTimeField(
    auto_now=True,
    verbose_name=_("Completed DateTime"),
    help_text=_("Datetime field when the group was completed in UTC"),
)
content_type = models.CharField(
    max_length=128,
    verbose_name=_("Result Content Type"),
    help_text=_("Content type of the result data"),
)
content_encoding = models.CharField(
    max_length=64,
    verbose_name=_("Result Encoding"),
    help_text=_("The encoding used to save the task result data"),
)
result = models.TextField(
    null=True, default=None, editable=False,
    verbose_name=_('Result Data'),
    help_text=_('The data returned by the task.  '
                'Use content_encoding and content_type fields to read.'))

def as_dict(self):
    return {
        'group_id': self.group_id,
        'result': self.result,
        'date_done': self.date_done,
    }

def __str__(self):
    return f'<Group: {self.group_id}>'

objects = managers.GroupResultManager()

class Meta:
    """Table information."""

    ordering = ['-date_done']

    verbose_name = _('group result')
    verbose_name_plural = _('group results')

    indexes = [
        models.Index(fields=['date_created']),
        models.Index(fields=['date_done']),
    ]

Upvotes: 8

Tom
Tom

Reputation: 22841

For anyone coming here wondering if they need an index_together in addition to unique_together to get the index's performance benefit, the answer for Postgres is no, they are functionally the same.

Upvotes: 42

Joe Tricarico
Joe Tricarico

Reputation: 373

In Django 1.5 and higher, you can use the {Model}.Meta.index_together class attribute. If you had two fields named foo and bar, you would add:

class Meta(object):
    index_together = unique_together = [
        ['foo', 'bar']
    ]

If you have only one set of unique fields, you can use a one-dimensional iterable for unique_together. However, the documentation does not indicate that the same applies to index_together.

This would also be okay:

class Meta(object):
    unique_together = 'foo', 'bar'
    index_together = [
        ['foo', 'bar']
    ]

This, however, is NOT supported by the documentation:

class Meta(object):
    unique_together = 'foo', 'bar'
    index_together = 'foo', 'bar'

Upvotes: 7

Alasdair
Alasdair

Reputation: 309109

If unique_together does add an index, it will be a multiple column index.

If you want one of the columns to be indexed individually, I believe you need to specify db_index=True in the field definition.

Upvotes: 14

Torsten Engelbrecht
Torsten Engelbrecht

Reputation: 13496

According to the docs, it will only enforce uniqueness on database level. I think generally making a field unique does not imply it has an index. Though you could also simply check on db level if the index exists. Everything indicates though it does not.

Upvotes: 1

Related Questions