Hibernate and Multi-Tenant Database using Schemas in PostgreSQL

Question

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.

Answer 1

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"

Answer 2

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...