在 确定分布策略 中,我们讨论了在多租户用例中使用 Citus 所需的与框架无关的数据库更改。在这里,我们专门研究如何借助 django-multitenant 库将多租户 Django
应 用程序迁移到 Citus 存储后端。
此过程将分为 5 个步骤:
TenantModelMixin
Django
应用程序更新为范围查询最初,您将从放置在单个数据库节点上的所有租户开始。为了能够扩展 django
,必须对模型进行一些简单的更改。
让我们考虑这个简化的模型:
from django.utils import timezone
from django.db import models
class Country(models.Model):
name = models.CharField(max_length=255)
class Account(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)
class Manager(models.Model):
name = models.CharField(max_length=255)
account = models.ForeignKey(Account, on_delete=models.CASCADE,
related_name='managers')
class Project(models.Model):
name = models.CharField(max_length=255)
account = models.ForeignKey(Account, related_name='projects',
on_delete=models.CASCADE)
managers = models.ManyToManyField(Manager)
class Task(models.Model):
name = models.CharField(max_length=255)
project = models.ForeignKey(Project, on_delete=models.CASCADE,
related_name='tasks')
这种模式的棘手之处在于,为了找到一个帐户的所有任务,您必须首先查询一个帐户的所有项目。一旦您开始分片数据,这就会成为一个问题,特别是当您对嵌套模型(如本例中的任务)运行 UPDATE
或 DELETE
查询时。
为了扩展多租户模型,查询必须快速定位属于一个帐户的所有记录。考虑一个 ORM
调用,例如:
Project.objects.filter(account_id=1).prefetch_related('tasks')
它生成这些底层 SQL
查询:
SELECT *
FROM myapp_project
WHERE account_id = 1;
SELECT *
FROM myapp_task
WHERE project_id IN (1, 2, 3);
但是,使用额外的过滤器,第二个查询会更快:
-- the AND clause identifies the tenant
SELECT *
FROM myapp_task
WHERE project_id IN (1, 2, 3)
AND account_id = 1;
这样您就可以轻松查询属于一个帐户的任务。实现这一点的最简单方法是在属于帐户的每个对象上简单地添加一个 account_id
列。
在我们的例子中:
class Task(models.Model):
name = models.CharField(max_length=255)
project = models.ForeignKey(Project, on_delete=models.CASCADE,
related_name='tasks')
account = models.ForeignKey(Account, related_name='tasks',
on_delete=models.CASCADE)
创建迁移以反映更改:python manage.py makemigrations
。
目标与之前相同。我们希望能够将 ORM
调用和查询路由到一个帐户。我们还希望能够在 account_id
上分发与帐户相关的多对多关系。
所以产生的调用:
Project.objects.filter(account_id=1).prefetch_related('managers')
可以在他们的 WHERE 子句中包含这样的 account_id:
SELECT *
FROM "myapp_project" WHERE "myapp_project"."account_id" = 1;
SELECT *
FROM myapp_manager manager
INNER JOIN myapp_projectmanager projectmanager
ON (manager.id = projectmanager.manager_id
AND projectmanager.account_id = manager.account_id)
WHERE projectmanager.project_id IN (1, 2, 3)
AND manager.account_id = 1;
为此,我们需要引入 through
模型。在我们的例子中:
class Project(models.Model):
name = models.CharField(max_length=255)
account = models.ForeignKey(Account, related_name='projects',
on_delete=models.CASCADE)
managers = models.ManyToManyField(Manager, through='ProjectManager')
class ProjectManager(models.Model):
project = models.ForeignKey(Project, on_delete=models.CASCADE)
manager = models.ForeignKey(Manager, on_delete=models.CASCADE)
account = models.ForeignKey(Account, on_delete=models.CASCADE)
创建迁移以反映更改:python manage.py makemigrations
。
Django
会自动在模型上创建一个简单的 “id”
主键,因此我们需要通过自己的自定义迁移来规避这种行为。运行 python manage.py makemigrations appname --empty --name remove_simple_pk
, 并将结果编辑为如下所示:
from django.db import migrations
class Migration(migrations.Migration):
dependencies = [
# leave this as it was generated
]
operations = [
# Django considers "id" the primary key of these tables, but
# we want the primary key to be (account_id, id)
migrations.RunSQL("""
ALTER TABLE myapp_manager
DROP CONSTRAINT myapp_manager_pkey CASCADE;
ALTER TABLE myapp_manager
ADD CONSTRAINT myapp_manager_pkey
PRIMARY KEY (account_id, id);
"""),
migrations.RunSQL("""
ALTER TABLE myapp_project
DROP CONSTRAINT myapp_project_pkey CASCADE;
ALTER TABLE myapp_project
ADD CONSTRAINT myapp_product_pkey
PRIMARY KEY (account_id, id);
"""),
migrations.RunSQL("""
ALTER TABLE myapp_task
DROP CONSTRAINT myapp_task_pkey CASCADE;
ALTER TABLE myapp_task
ADD CONSTRAINT myapp_task_pkey
PRIMARY KEY (account_id, id);
"""),
migrations.RunSQL("""
ALTER TABLE myapp_projectmanager
DROP CONSTRAINT myapp_projectmanager_pkey CASCADE;
ALTER TABLE myapp_projectmanager
ADD CONSTRAINT myapp_projectmanager_pkey PRIMARY KEY (account_id, id);
"""),
]
对 UNIQUE
约束也需要做同样的事情。您可以使用 unique=True
或 unique_together
在模型中设置显式约束,例如:
class Project(models.Model):
name = models.CharField(max_length=255, unique=True)
account = models.ForeignKey(Account, related_name='projects',
on_delete=models.CASCADE)
managers = models.ManyToManyField(Manager, through='ProjectManager')
class Task(models.Model):
name = models.CharField(max_length=255)
project = models.ForeignKey(Project, on_delete=models.CASCADE,
related_name='tasks')
account = models.ForeignKey(Account, related_name='tasks',
on_delete=models.CASCADE)
class Meta:
unique_together = [('name', 'project')]
对于这些约束,您可以简单地在模型中更改约束:
class Project(models.Model):
name = models.CharField(max_length=255)
account = models.ForeignKey(Account, related_name='projects',
on_delete=models.CASCADE)
managers = models.ManyToManyField(Manager, through='ProjectManager')
class Meta:
unique_together = [('account', 'name')]
class Task(models.Model):
name = models.CharField(max_length=255)
project = models.ForeignKey(Project, on_delete=models.CASCADE,
related_name='tasks')
account = models.ForeignKey(Account, related_name='tasks',
on_delete=models.CASCADE)
class Meta:
unique_together = [('account', 'name', 'project')]
然后使用以下命令生成迁移:
python manage.py makemigrations
一些 UNIQUE
约束是由 ORM
创建的,您需要显式删除它们。 OneToOneField
和 ManyToMany
字段就是这种情况。
对于这些情况,您需要:1. 找到约束 2. 进行迁移以删除它们 3. 重新创建约束,包括 account_id
字段
要查找约束,请使用 psql
连接到您的数据库并运行 \d+ myapp_projectmanager
你将看到 ManyToMany
(或 OneToOneField
)约束:
"myapp_projectmanager" UNIQUE CONSTRAINT myapp_projectman_project_id_manager_id_bc477b48_uniq,
btree (project_id, manager_id)
在迁移中删除此约束:
from django.db import migrations
class Migration(migrations.Migration):
dependencies = [
# leave this as it was generated
]
operations = [
migrations.RunSQL("""
DROP CONSTRAINT myapp_projectman_project_id_manager_id_bc477b48_uniq;
"""),
然后改变你的模型有一个 unique_together
包括 account_id
class ProjectManager(models.Model):
project = models.ForeignKey(Project, on_delete=models.CASCADE)
manager = models.ForeignKey(Manager, on_delete=models.CASCADE)
account = models.ForeignKey(Account, on_delete=models.CASCADE)
class Meta:
unique_together=(('account', 'project', 'manager'))
最后通过创建新迁移来应用更改以生成这些约束:
python manage.py makemigrations
接下来,我们将使用 django-multitenant
库将 account_id
添加到外键中,以便以后更轻松地查询应用程序。
在 Django
应用程序的 requirements.txt
中,添加
django_multitenant>=2.0.0, <3
运行 pip install -r requirements.txt
。
在 settings.py
中,将数据库引擎改为 django-multitenant
提供的自定义引擎:
'ENGINE': 'django_multitenant.backends.postgresql'
模型现在不仅继承自 models.Model
,还继承自 TenantModelMixin
。
要在你的 models.py
文件中做到这一点,你需要执行以下导入
from django_multitenant.mixins import *
以前我们的示例模型仅继承自 models.Model
,但现在我们需要将它们更改为也继承自 TenantModelMixin
。实际项目中的模型也可能继承自其他 mixin
,例如 django.contrib.gis.db
,这很好。
此时,您还将引入 tenant_id
来定义哪一列是分布列。
class TenantManager(TenantManagerMixin, models.Manager):
pass
class Account(TenantModelMixin, models.Model):
...
tenant_id = 'id'
objects = TenantManager()
class Manager(TenantModelMixin, models.Model):
...
tenant_id = 'account_id'
objects = TenantManager()
class Project(TenantModelMixin, models.Model):
...
tenant_id = 'account_id'
objects = TenantManager()
class Task(TenantModelMixin, models.Model):
...
tenant_id = 'account_id'
objects = TenantManager()
class ProjectManager(TenantModelMixin, models.Model):
...
tenant_id = 'account_id'
objects = TenantManager()
对于 ForeignKey
和 OneToOneField
约束,我们有几种不同的情况:
TenantForeignKey
(或 TenantOneToOneField
)。models.ForeignKey(MyModel, on_delete=models.CASCADE, db_constraint=False
) 来删除约束。最后你的模型应该是这样的:
from django.db import models
from django_multitenant.fields import TenantForeignKey
from django_multitenant.mixins import *
class Country(models.Model): # This table is a reference table
name = models.CharField(max_length=255)
class TenantManager(TenantManagerMixin, models.Manager):
pass
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) # No changes needed
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()
在本文的第二部分,我们介绍了在 citus
中, ManyToMany
关系需要一个带有租户列的 through
模型。这就是为什么我们有这个模型:
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()
安装库、更改引擎和更新模型后,运行 python manage.py makemigrations
。这将产生一个迁移,以便在必要时合成外键。
我们需要最后一次迁移来告诉 Citus
标记要分发的表。创建一个新的迁移 python manage.py makemigrations appname --empty --name Distribute_tables
。编辑结果如下所示:
from django.db import migrations
from django_multitenant.db import migrations as tenant_migrations
class Migration(migrations.Migration):
dependencies = [
# leave this as it was generated
]
operations = [
tenant_migrations.Distribute('Country', reference=True),
tenant_migrations.Distribute('Account'),
tenant_migrations.Distribute('Manager'),
tenant_migrations.Distribute('Project'),
tenant_migrations.Distribute('ProjectManager'),
tenant_migrations.Distribute('Task'),
]
从到目前为止的步骤中创建的所有迁移,使用 python manage.py migrate
将它们应用到数据库。
此时,Django
应用程序模型已准备好与 Citus
后端一起工作。您可以继续将数据导入新系统并根据需要修改视图以处理模型更改。
上一节讨论的 django-multitenant
库不仅对迁移有用,而且对简化应用程序查询也很有用。该库允许应用程序代码轻松地将查询范围限定为单个租户。它会自动将正确的 SQL
过滤器添加到所有语句中,包括通过关系获取对象。
例如,在一个视图中只需 set_current_tenant
,之后的所有查询或连接都将包含一个过滤器,以将结果范围限定为单个租户。
# set the current tenant to the first account
s = Account.objects.first()
set_current_tenant(s)
# now this count query applies only to Project for that account
Project.objects.count()
# Find tasks for very important projects in the current account
Task.objects.filter(project__name='Very important project')
在应用程序视图的上下文中,当前租户对象可以在用户登录时存储为 SESSION
变量, 并且视图操作可以 set_current_tenant
到该值。有关更多示例,请参阅 django-multitenant
中的 README
。
set_current_tenant
函数也可以接受一个对象数组,比如
set_current_tenant([s1, s2, s3])
它使用类似于 tenant_id IN (a,b,c)
的过滤器更新内部 SQL
查询。
而不是在每个视图中调用 set_current_tenant()
, 您可以在 Django
应用程序中创建并安装一个新的 middleware
类来自动完成。
# 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
通过更新 src/appname/settings/base.py
中的 MIDDLEWARE
数组来启用中间件:
MIDDLEWARE = [
# ...
# existing items
# ...
'appname.middleware.MultitenantMiddleware'
]