django-postgres-denormalize

PostgreSQL cannot index, sort or constrain on a value that is stored in another table. A value that is several joins away, such as the tenant that a row belongs to through its parent, cannot be part of an index or a unique constraint on the row’s table, and every query that filters or orders by it pays for the joins.

This package lets a model store a copy of such a value in an ordinary column, and keeps the copy correct with PostgreSQL triggers. That works for every write, whether it comes from the ORM, a bulk update, raw SQL or the previous release that is still serving during a rolling deploy. The model declares the lookup path and nothing else; makemigrations writes the column, the triggers and their changes.

Full documentation is at https://django-postgres-denormalize.readthedocs.io/.

from django.db import models
from postgres_denormalize.fields import DenormalizedField, DenormalizedForeignKey


class Task(models.Model):
    project = models.ForeignKey('Project', on_delete=models.CASCADE)
    title = models.CharField(max_length=100)
    # A copy of project.tenant: a real foreign key, its target and on_delete rule inferred from the path.
    tenant = DenormalizedForeignKey('project__tenant')
    # A copy of project.name: a column of the same type as the source.
    project_name = DenormalizedField('project__name')

    class Meta:
        ordering = ['project_name', 'title']
        constraints = [models.UniqueConstraint(fields=['tenant', 'title'], name='unique_task_title_per_tenant')]
        indexes = [models.Index(fields=['tenant', 'project_name', 'title'], name='task_listing')]

From then on, saving a task computes its copies in the database and reads them back onto the instance. Moving a project to another tenant, or renaming it, rewrites the copies of its tasks in the same transaction, but only the copies that change.

Use it for values that an index or a unique constraint needs, and values that most queries sort or filter by across several joins. Do not use it to save a join for rendering, because select_related does that for free. Do not use it either for values whose source is often updated in bulk by code that you cannot wrap in one of its batch helpers. See when to use it, and when not to, and the alternatives page, which compares it with django-pgconstraints and django-computedfields.

pip install django-postgres-denormalize

INSTALLED_APPS = [
    # ...
    'pgtrigger',
    'postgres_denormalize',
]