在PostgreSQL中，分区还是多个数据库更有效？

Question

在PostgreSQL中，分区还是多个数据库更有效？

postgresqlpartitioning

7

我有一个应用程序，其中许多公司发布信息。每个公司的数据是自包含的-没有数据重叠。

从性能上来说，哪种方式更好：

在每个表的每一行上保留公司ID，并让每个索引使用它？
根据公司ID对每个表进行分区
分区并创建用户以访问每个公司以确保安全
为每个公司创建多个数据库

基于web的应用程序具有持久连接。

我的想法：

新的pg连接很昂贵，因此单个数据库会创建较少的新连接
只有一个字典副本似乎比200个左右要更有效率
多个数据库肯定比程序员错误更安全
如果应用程序规范发生变化以便公司分享，将难以实现多个数据库

- cc young

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Craig Ringer · Accepted Answer

我建议在PostgreSQL邮件列表中搜索关于多租户设计的信息。那里有很多讨论，答案归结为“这取决于情况”。在保证隔离、性能和可维护性之间存在权衡。一种常见的方法是使用单个数据库，但每个客户都有一个模式（命名空间），每个模式中具有相同的表结构，以及用于跨所有客户相同数据的共享或公共模式。PostgreSQL模式类似于MySQL中的“数据库”，您可以跨不同的模式查询，但默认情况下它们是隔离的。将客户数据放在单独的模式中，您可以使用search_path设置，通常通过ALTER USER customername SET search_path = 'customerschema, sharedschema'来确保每个客户只看到自己的数据。

为了提供额外的保护，您应该REVOKEpublic的ALL FROM SCHEMA customerschema，然后GRANTthecustomer对customerschema的ALL权限，这样他们将是唯一有访问权限的人，对他们的每个表都执行相同的操作。然后，您的连接池可以使用一个固定的用户帐户登录，该帐户没有任何客户模式的GRANT访问权限，但具有成为任何客户的SET ROLE的权利（通过将其设置为每个客户角色的成员，并设置NOINHERIT，以便通过SET ROLE显式声明权限）。连接应立即将SET ROLE设置为当前正在运行的客户端。这将允许您避免为每个客户创建新连接的开销，同时保持强大的保护措施，防止程序员错误地访问错误的客户数据。只要连接池在向下一个客户端分配连接之前执行DISCARD ALL和/或RESET ROLE，那么这将为您提供非常强大的隔离，而无需为每个用户创建单独的连接。

如果你的web应用环境没有像样的连接池（比如，你在使用带持久连接的PHP），那么你真的需要在Pg和web服务器之间放置一个好的连接池，因为太多的连接到后端会影响性能。PgBouncer和PgPool-II是最佳选择，并且方便地可以在连接移交期间为你处理DISCARD ALL和RESET ROLE。

这种方法的主要缺点是维护这么多表的开销，因为每个客户的基本非共享表集都会被克隆。随着客户数量的增加，这将累积到一个点，在自动整理运行期间检查的表的纯数量开始变得昂贵，任何基于数据库中总表数的操作都会减慢速度。如果你打算在同一个数据库中拥有成千上万的客户，则这更成为一个问题，但我强烈建议在承诺之前使用虚拟数据对此设计进行一些扩展测试。

理想的方法可能是单个表，自动行级安全控制元组可见性，但不幸的是，这是PostgreSQL尚未具备的功能。看起来由于SEPostgreSQL工作添加了合适的基础设施和API，它正在路上，但它不在9.1中。