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.

Community
  • 1
  • 1
Jesse
  • 113
  • 1
  • 4

3 Answers3

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"

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • This is the easiest solution since I've now switched to MyBatis and have full control over SQL statements. – Jesse Apr 07 '12 at 15:07
1

While sharding by schema is common, see this post from the Apartment gem authors covering some drawbacks.

At Citus, we shard via option #3 listed above, and you can read more in our use-case guide in the Documentation.

Sumedh
  • 131
  • 4
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...

therealmitchconnors
  • 2,732
  • 1
  • 18
  • 36
  • 2
    As of Hibernate 4.0, multi-tenancy is NOT natively supported at the discriminator level. This is expected in 5.0. – Thinkisto Nov 25 '12 at 07:02
  • 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](http://docs.jboss.org/hibernate/orm/4.1/devguide/en-US/html/ch16.html#d5e4623). – therealmitchconnors Dec 05 '12 at 18:42
  • 1
    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." – Thinkisto Dec 06 '12 at 14:24
  • @AgileYogi has work on 5.0 started? I only see 4.3 in github. – Blankman May 30 '13 at 02:16
  • Event in 5.0 Multi-tenancy on Discriminator level is not done yet. – leokom Sep 11 '15 at 06:37