java - 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"