title: Hibernate and Multi-Tenant Database using Schemas in PostgreSQL
body: <h1>Background</h1>
  
  <p>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.</p>
  
  <p>I watched Guy Naor's talk on <a href="http://aac2009.confreaks.com/06-feb-2009-14-30-writing-multi-tenant-applications-in-rails-guy-naor.html" rel="nofollow noreferrer"> Writing Multi-tenant Applications in Rails</a> in which he talks about a few approaches to multi-tenancy (data isolation decreases as you go down the list):</p>
  
  <ol>
  <li>Each customer has a separate database</li>
  <li>One database with separate schemas and tables (table namespaces) for each customer.</li>
  <li>One database with 1 set of tables with customer id columns.</li>
  </ol>
  
  <p>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  <code>SET search_path TO customer_schema,public</code> 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 <code>@Before</code> controller annotations in controller methods in <em>Play!</em> (this is the approach Guy used in his rails example). The <strong>search_path</strong> in postgres acts exactly like the <code>$PATH</code> does in an OS; awesome!</p>
  
  <p>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.</p>
  
  <h1>The Problem</h1>
  
  <h3>How do I get either JDBC or Hibernate to support dynamically switching postgres schemas at runtime?</h3>
  
  <p>It seems database connections are statically configured by a connection factory (see: <a href="https://stackoverflow.com/questions/4158820/how-to-manage-many-schemas-on-one-database-using-hibernate">How to manage many schemas on one database using hibernate</a>). 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.</p>
  
  <p>I haven't committed myself completely to approach #2 above, but I haven't quite abandoned it for approach #3 quite yet either.</p>