11

Background

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.

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):

  1. Each customer has a separate database
  2. One database with separate schemas and tables (table namespaces) for each customer.
  3. One database with 1 set of tables with customer id columns.

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!

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.

The Problem

How do I get either JDBC or Hibernate to support dynamically switching postgres schemas at runtime?

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.

I haven't committed myself completely to approach #2 above, but I haven't quite abandoned it for approach #3 quite yet either.

Improve this question

2 Answers 2

Reset to default
7

You can execute the command

SET search_path TO customer_schema,public

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.

Of course, you can also preset the search_path per user.

ALTER ROLE foo SET search_path=foo, public;

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;

More ways to set the search_path here:
How does the search_path influence identifier resolution and the "current schema"

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

This is the easiest solution since I've now switched to MyBatis and have full control over SQL statements.
1

As of Hibernate 4.0, multi-tenancy is natively supported at the discriminator (customerID), schema, and database level. See the source code here, and the unit test here.

The difficulty is that, while the unit test's file name is SchemaBasedMultitenancyTest, the actual MultitenancyStrategy used is Database. I can't find any examples on how to make it work based on schema, but maybe the unit test will be enough to go on...

Improve this answer

5 Comments

As of Hibernate 4.0, multi-tenancy is NOT natively supported at the discriminator level. This is expected in 5.0.
Kinara, can you provide documentation on this lack of support? The talk referenced in the OP seems to indicate that it is supported... as well as the jboss documentation.
Look at the last para of 16.3 in the same documentation, where it talks about the discriminator approach - "This strategy is not yet implemented in Hibernate as of 4.0 and 4.1. Its support is planned for 5.0."
@AgileYogi has work on 5.0 started? I only see 4.3 in github.
Event in 5.0 Multi-tenancy on Discriminator level is not done yet.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.