multi-tenancy Postgres schemas and Django

agenda

  • what's tenant?

  • what's schema?

  • multi-tenancy vs. single-tenancy

  • solutions for multi-tenancy

  • use cases

  • django-tenants setup

  • django-tenants utils

  • real world problems

  • pros and cons

what is tenant?

schemas

  • namespaces
  • separate tables, functions, objects
  • user's access restricted by privileges
CREATE SCHEMA myschema;
CREATE TABLE myschema.mytable (
 ...
);
SELECT * FROM myschema.mytable;
SHOW search_path;

search_path
------------
public
SET search_path TO myschema, public;

basic commands

which database engines support schemas?

multi-tenancy

architecture in which single instance of application serves multiple customers called tenants.

tenants can have some configuration capabilities such as influencing business logic, introducing some small interface changes as colours, images etc.

single-tenancy

architecture in which single instance of application serves only one customer.

the customer has therefore wide range of customisation options including possible access to source code.

solutions for multi-tenancy

isolated approach

separate databases

shared approach

shared database

shared schema

semi isolated approach

shared database

separate schemas

why semi isolated approach?

simplicity

  • managing one database

  • barely any code base change

  • avoiding hassle of filtering database objects 

performance

  • make use of shared connections, buffers, memory 

use cases

django-tenants

  • based on django-schemata (not maintained) and django-tenant-schemas (still supported, recommended for earlier django versions)
  • dynamically developed
  • support for tenant aware caching, logging, static and media files, PostGIS
  • support for schemas aware celery tasks with tenant-schemas-celery

settings

DATABASES = {
    'default': {
        'ENGINE': 'django_tenants.postgresql_backend',
        # ..
    }
}
DATABASE_ROUTERS = (
    'django_tenants.routers.TenantSyncRouter',
)
MIDDLEWARE = (
    'django_tenants.middleware.main.TenantMainMiddleware',
    #...
)
TEMPLATES = [
    {
        #...
        'OPTIONS': {
            'context_processors': [
                'django.template.context_processors.request',
                #...
            ],
        },
    },
]

tenant model

from django.db import models
from django_tenants.models import TenantMixin, DomainMixin

class Store(TenantMixin):
    name = models.CharField(max_length=100)
    street = models.CharField(max_length=100)
    city = models.CharField(max_length=100)
    created = models.DateTimeField(auto_now_add=True)

    # schema will be automatically created and synced when it is saved
    auto_create_schema = True

class Domain(DomainMixin):
    pass

apps

SHARED_APPS = (
    'django_tenants',
    'stores',

    'django.contrib.contenttypes',

    'django.contrib.auth',
    'django.contrib.sessions',
    'django.contrib.sites',
    'django.contrib.messages',
    'django.contrib.admin',
)

TENANT_APPS = (
    'django.contrib.contenttypes',
    'django.contrib.auth',

    'invoices',
    'employees',
)

INSTALLED_APPS = list(SHARED_APPS) + [
    app for app in TENANT_APPS if app not in SHARED_APPS
]

management commands

python manage.py makemigrations
python manage.py migrate_schemas
python manage.py syncdb
python manage.py migrate

broken migrations?

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;

utils

how to retrieve current tenant model?

>>> from django.db import connection
>>> connection.tenant

<Store: Supermarket Python>

>>> connection.tenant.name
'Supermarket Python'

>>> connection.tenant.domains.all()

<QuerySet [<Domain: http://python.supermarket.com>]>
def has_permission(self, request, view):
    ...
    tenant = request.tenant
    ...

how to set tenant?

>>> from django.db import connection
>>> from stores.models import Store
>>> from employees.models import Employee
>>> python_store = Store.objects.filter(short_name='python').first()

<Store: Supermarket Python>

>> ruby_store = Store.objects.filter(short_name='ruby').first()

<Store: Supermarket Ruby>

>>> connection.set_tenant(python_store)
>>> Employee.objects.all()

<Queryset [<Employee: Cobra>, <Employee: Viper>]

>>> connection.set_tenant(ruby_store)
>>> Employee.objects.all()

<Queryset [<Employee: Diamond>, <Employee: Emerald>]

any other utils?

from django_tenants.utils import schema_context

with schema_context(schema_name):
    ...
from django_tenants.utils import tenant_context

with tenant_context(tenant):
    ...
>>> from django_tenants.utils import get_tenant_model
>>> get_tenant_model().objects.all()

<Queryset: [<Store: Supermarket Python>, <Store: Supermarket Ruby>]
>>> from django_tenants.utils import (
        get_public_schema_name,
        get_tenant_model,
    )
>>> get_tenant_model().objects.filter(
        schema_name=get_public_schema_name(),
    ).first()
    
<Store: Supermarket Public>

real world solutions

trigger action for all employees

...

@app.task
def trigger_actions():
    for tenant in get_tenant_model().objects.exclude(
        schema_name=get_public_schema_name(),
    ):
        with tenant_context(tenant):
            for employee in Employee.objects.all():
                trigger_action(employee)

single backend domain

from django_tenants.middleware import TenantMainMiddleware


class TenantDomainHeaderMiddleware(TenantMainMiddleware):
    @staticmethod
    def hostname_from_request(request):
        return request.META.get(
            'TENANT_DOMAIN_HEADER',
            settings.DEFAULT_TENANT_DOMAIN,
        )

MIDDLEWARE_CLASSES = (
    'stores_project.middlewares.TenantDomainHeaderMiddleware',
    ...
)

pros

  • easy setup

  • minimal code base change

  • spare hassle of filtering by related model instance acting as tenant model

cons

  • no possibility of setting connection to multiple tenants

  • easy to break database structure with 'migrate'

summary