PostgreSQL的多租户应用模式 [英] PostgreSQL's schemas for multi-tenant applications

问题描述

我正在学习多租户应用程序，以及如何将PostgreSQL的架构用于此.

I'm learning about multi-tenant applications and how PostgreSQL's schemas can be used for this.

经过研究，我最终找到了文章，其中作者描述了在多租户应用程序中使用PostgreSQL架构时的糟糕经验.主要问题是迁移性能差和数据库资源使用率高.

Researching the subject, I ended up finding an article in which the author describes a poor experience when using PostgreSQL's schemas in multi-tenant applications. The main problems would be having bad performance for migrations and high usage of database resources.

与为每个租户使用一个单独的模式相比，似乎只有一个模式(在租户之间共享表)会带来更好的性能.但这对我来说很奇怪.我会相反，因为较小表上的索引往往比较大表上的索引更轻.

It seems like having only one schema (sharing the tables among the tenants) would lead to better performance than having one separated schema for each tenant. But it feels strange to me. I would think the opposite, since indexes on smaller tables tend to be lighter than indexes on larger tables.

为什么将数据分成多个小表(在多个模式中)而不是将数据分成几个大表(在一个模式中)会导致性能变差?

Why would the performance be worse when having data separated in a lot of small tables (in multiple schemas), than having data separated in a few huge tables (in a single schema)?

推荐答案

性能不一定会变差.正如文章所解释的，根据应用程序设计和工作负载，有一些特定条件会使架构方法变得更好或更糟.让我解释一下租户模式"与共享表"方法之间的权衡:

Performance isn't worse, necessarily. As the article explains, there are specific conditions which make the schema approach better or worse depending on your application design and workload. Let me explain the tradeoffs of the "tenant-schema" vs. "shared-table" approaches:

tenant-schema 是最好的选择，因为您的租户数量相对较少.一个示例是只有付费订阅用户的会计应用程序.使它成为您更好性能的选择的原因包括:

tenant-schema is best when you have a relatively small number of fairly large tenants. An example of this would be an accounting application, with only paid subscription users. Things which make it the better performing option for you include:

• 少量租户，每个租户都有大量数据
• 相对简单的架构，每个租户没有很多表
• 需要自定义一些租户的架构
• 每个租户利用数据库角色的能力
• 将租户数据从一台服务器迁移到另一台服务器的要求
• 为每个租户在云中启动专用应用服务器的能力

使其表现不佳的因素包括:

Things which make it a poor-performing option include:

• 很多数据很少的租户
• 无状态连接方法，其中每个请求可以是任何租户
• 为所有表(如ActiveRecord)缓存元数据的客户端库或orm
• 高效，高性能的连接池和/或缓存的要求
• VACUUM和其他PostgreSQL管理操作存在的问题，这些问题在1000个表中的伸缩性很差.

租户模式是否不利于迁移/模式更改，实际上取决于您的操作方式.对于快速推出通用架构更改不利，但对于在租户中逐步部署架构更改则有利.

Whether tenant-schema is bad for migrations/schema changes really depends on how you're doing them. It's bad for rolling out a universal schema change quickly, but good for deploying schema changes as a gradual rollout across tenants.

共享表在有很多租户且很多租户数据很少的情况下效果更好.一个示例是社交媒体移动应用程序，该应用程序允许免费帐户，因此有成千上万个被遗弃的帐户.使共享表模型受益的其他因素包括:

shared-table works better for situations when you have a lot of tenants, and a lot of your tenants have very little data. An example of this would be a social medial mobile application which permits free accounts and thus has thousands of abandoned accounts. Other things which make the shared table model beneficial are:

• 更好的连接池，因为所有连接都可以使用相同的池
• 更好的PostgreSQL管理，因为表总数较少
• 更好的迁移和架构更改，因为只有一组表"

共享表的主要缺点是需要将租户过滤条件附加到应用程序层中的每个查询上.这也是有问题的，因为:

The main drawback of shared-table is the need to append the tenant filter condition onto every single query in the application layer. It's also problematic because:

• 连接许多表的查询的性能可能会很差，因为租户过滤器会打​​乱查询计划
• 增长到一亿行的表可能会导致特定的性能和维护问题
• 无法进行针对租户的应用程序更改或架构升级
• 在服务器之间迁移租户的费用更高

那么哪种模型表现更好"实际上取决于哪种折衷会给您带来最坏的影响.

So which model "performs better" really depends on which tradeoffs hurt you the worst.

还有一个混合模型"tenant-view"，其中实际数据存储在共享表中，但是每个应用程序连接使用

There's also a hybrid model, "tenant-view", where the actual data is stored in shared tables, but each application connection uses security barrier views to view the data. This has some of the tradeoffs of each model. Primarily, it has the security benefits of the tenant-schema model with some of the performance drawbacks of both models.

这篇关于PostgreSQL的多租户应用模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！