问题描述
我正在开发一个未来的多租户 Web 应用程序,该应用程序需要支持数千个用户.该应用程序建立在基于 Java 的 Play 之上!使用 JPA/Hibernate 和 postgreSQL 的 MVC 框架.
I am working on a future multi-tenant web application that will need to support thousands of users. The app is being built on top of the Java based Play! MVC Framework using JPA/Hibernate and postgreSQL.
我在 在 Rails 中编写多租户应用程序,其中他谈到了几种多租户的方法(数据隔离随着列表的下降而降低):
I watched Guy Naor's talk on Writing Multi-tenant Applications in Rails in which he talks about a few approaches to multi-tenancy (data isolation decreases as you go down the list):
- 每个客户都有一个单独的数据库
- 一个数据库,每个客户都有单独的架构和表(表命名空间).
- 一个数据库,其中包含一组带有客户 ID 列的表.
我选择了方法 #2,其中从请求中解析出某种用户 ID,然后用于访问该用户表空间.postgres SET search_path TO customer_schema,public
命令在进行任何查询之前给出,以确保客户的表是查询的目标.这可以通过Play! 中控制器方法中的@Before
控制器注释轻松完成(这是Guy 在他的rails 示例中使用的方法).postgres 中的 search_path 与操作系统中的 $PATH
完全一样;太棒了!
I settled on approach #2, where a user id of some sort is parsed out of a request and then used to access that users tablespace. A postgres SET search_path TO customer_schema,public
command is given before any query is made to make sure the customer's tables are the target of a query. This is easily done with @Before
controller annotations in controller methods in Play! (this is the approach Guy used in his rails example). The search_path in postgres acts exactly like the $PATH
does in an OS; awesome!
所有这些听起来都很棒,但我在 JDBC/Hibernate/JPA 堆栈之上实现它时立即遇到了困难,因为似乎没有一种方法可以在运行时动态切换模式.
All this sounded great, but I immediately ran into difficulties in implementing it on top of a JDBC/Hibernate/JPA stack because there doesn't seem to be a way to dynamically switch schemas at runtime.
似乎数据库连接是由连接工厂静态配置的(请参阅:如何使用 hibernate 在一个数据库上管理多个模式).我发现类似的问题和每个用户使用多个 SessionFactorys 的答案相似,但由于我知道 SessionFactorys 是重量级的对象,所以你不可能支持数百个用户,更不用说数千个用户了,走这条路.
It seems database connections are statically configured by a connection factory (see: How to manage many schemas on one database using hibernate). I have found similar questions with similar answers of using multiple SessionFactorys per user, but since I understand SessionFactorys are heavy weight objects so it's implausible that you could support hundreds of users, let alone thousands of users, going this route.
我还没有完全承诺采用上面的方法 #2,但我也还没有完全放弃它用于方法 #3.
I haven't committed myself completely to approach #2 above, but I haven't quite abandoned it for approach #3 quite yet either.
推荐答案
可以执行命令
SET search_path TO customer_schema,public
在同一个连接/会话/事务中,根据需要经常使用.它只是另一个类似于 SELECT 1;
的命令.更多内容请参见手册.
as often as you need to, within the same connection / session / transaction. It is just another command like SELECT 1;
. More in the manual here.
当然,您也可以为每个用户预设search_path
.
Of course, you can also preset the search_path
per user.
ALTER ROLE foo SET search_path=foo, public;
如果每个用户或其中许多用户都有与其用户名匹配的架构,您可以简单地使用 postgresql.conf 中的默认设置:
If every user or many of them have a schema that matches their user name, you can simply go with the default setting in postgresql.conf:
search_path="$user",public;
更多设置search_path
在这里:
search_path 如何影响标识符分辨率和当前模式"
这篇关于在 PostgreSQL 中使用模式的 Hibernate 和多租户数据库的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!