How to generate indexes for related fields on Django models?

Say we’re building a Django-based site that clones Medium.com’s URL structure, where you have users and articles. We’d probably have this model:

class Article(models.Model):
    user = models.ForeignKey(User)
    slug = models.CharField()

We want to be able to build URLs that look like /<username>/<slug>/. Since we’re going to have billions of articles and zillions of pageviews, we want to put an index on that model:

class Meta:
    indexes = [
        models.Index(fields=['user__username', 'slug'])
    ]

But this causes the makemigrations command to fail with the following error:

django.core.exceptions.FieldDoesNotExist: Article has no field named 'user__username'. The app cache isn't ready yet, so if this is an auto-created related field, it won't be available yet.

So, plain vanilla models.Index doesn’t support relational lookups like a QuerySet does. How would I add an index like this? Let’s assume PostgreSQL, if that’s helpful.

Answer

It seems that you can’t make multi-table index according to this answer.

So if it’s not possible in the database, I don’t see how can Django offer this feature…

What you can do to make your queries more efficients is an index using user_id and slug.

Advertisement

Answer