Django ForeignKey partial index

By default django creates a database index automatically when you use the models.ForeignKey field. However in some situations it makes more sense to use a partial index, for example when the vast majority of the foreign key values are null.

Let’s say we have this example model:

class RestaurantChain(models.Model):
    name = models.CharField(max_length=512)


class Restaurant(models.Model):
    name = models.CharField(max_length=512)
    restaurant_chain = models.ForeignKey(
        RestaurantChain, on_delete=models.CASCADE, null=True, blank=True
    )

To change the implicit (full) index into a partial index we need to disable the default index and then define the new partial index:

class RestaurantChain(models.Model):
    name = models.CharField(max_length=512)


class Restaurant(models.Model):
    name = models.CharField(max_length=512)
    restaurant_chain = models.ForeignKey(
        RestaurantChain, on_delete=models.CASCADE, null=True, blank=True, db_index=False
    )

    class Meta:
        indexes = [
            models.Index(
                fields=["restaurant_chain"],
                condition=~models.Q(restaurant_chain=None),
            ),
        ]

Note: this only works when you use PostgreSQL or SQLite. Oracle, MySQL and MariaDB do not support partial indexes.