How to Enable Distributed Multi‑Tenant PostgreSQL (Citus) in a Python/Django App

To scale a Django multi‑tenant app from a single database node to a distributed PostgreSQL (Citus) backend, several coordinated steps are required.

1. Introduce a tenant column to models

Add account_id to each model that belongs to an account, enabling fast queries for a specific tenant. For example, modify the Task model to include account = models.ForeignKey(Account, ...) and run python manage.py makemigrations.

For many‑to‑many relationships, add the tenant column to the through model and adjust queries to filter by account_id in the WHERE clause.

2. Include account_id in primary keys and unique constraints

Django creates a simple id primary key automatically; custom migrations are needed to replace it with a composite key (account_id, id). Example migration snippets use ALTER TABLE … DROP CONSTRAINT … and ADD CONSTRAINT … PRIMARY KEY (account_id, id). Unique constraints are similarly updated using unique=True or unique_together that reference account_id.

3. Update models to use TenantModelMixin and TenantForeignKey

Install django-multitenant (>=2.0.0, <3) and change the database engine in settings.py to 'django_multitenant.backends.postgresql'. Import the mixins and replace base classes with TenantModelMixin, defining tenant_id (e.g., 'id' for Account and 'account_id' for related models). Use TenantForeignKey for foreign keys between distributed tables.

Example model definitions:

from django.db import models
from django_multitenant.mixins import *
from django_multitenant.fields import TenantForeignKey

class Country(models.Model):
    name = models.CharField(max_length=255)

class Account(TenantModelMixin, models.Model):
    name = models.CharField(max_length=255)
    domain = models.CharField(max_length=255)
    subdomain = models.CharField(max_length=255)
    country = models.ForeignKey(Country, on_delete=models.SET_NULL)
    tenant_id = 'id'
    objects = TenantManager()

class Manager(TenantModelMixin, models.Model):
    name = models.CharField(max_length=255)
    account = models.ForeignKey(Account, related_name='managers', on_delete=models.CASCADE)
    tenant_id = 'account_id'
    objects = TenantManager()

class Project(TenantModelMixin, models.Model):
    account = models.ForeignKey(Account, related_name='projects', on_delete=models.CASCADE)
    managers = models.ManyToManyField(Manager, through='ProjectManager')
    tenant_id = 'account_id'
    objects = TenantManager()

class Task(TenantModelMixin, models.Model):
    name = models.CharField(max_length=255)
    project = TenantForeignKey(Project, on_delete=models.CASCADE, related_name='tasks')
    account = models.ForeignKey(Account, on_delete=models.CASCADE)
    tenant_id = 'account_id'
    objects = TenantManager()

class ProjectManager(TenantModelMixin, models.Model):
    project = TenantForeignKey(Project, on_delete=models.CASCADE)
    manager = TenantForeignKey(Manager, on_delete=models.CASCADE)
    account = models.ForeignKey(Account, on_delete=models.CASCADE)
    tenant_id = 'account_id'
    objects = TenantManager()

4. Distribute tables in Citus

Create an empty migration that calls tenant_migrations.Distribute for each table (e.g., Country, Account, Manager, Project, ProjectManager, Task) and apply it with python manage.py migrate. After this, the Django models work with the Citus backend.

5. Update the Django app to use range queries

The django-multitenant library automatically adds the appropriate WHERE account_id = … filter to all queries once set_current_tenant() is called. Example usage in a view:

# set the current tenant to the first account
s = Account.objects.first()
set_current_tenant(s)

# subsequent queries are scoped to that tenant
Project.objects.count()
Task.objects.filter(project__name='Very important project')

For convenience, a middleware can be added to set the tenant automatically from the logged‑in user:

# src/appname/middleware.py
from django_multitenant.utils import set_current_tenant

class MultitenantMiddleware:
    def __init__(self, get_response):
        self.get_response = get_response
    def __call__(self, request):
        if request.user and not request.user.is_anonymous:
            set_current_tenant(request.user.employee.company)
        response = self.get_response(request)
        return response

Register the middleware in settings/base.py by adding 'appname.middleware.MultitenantMiddleware' to the MIDDLEWARE list.

After completing these steps, the Django application is fully migrated to a distributed multi‑tenant PostgreSQL (Citus) setup, ready for data import and further view adjustments.