Single mapping thousand schemas at Hibernate: Suitable and how to?

[Hugo Alberto Bedolla]

Hi everybody.

I'm doing prospection about what's the better aproach for my case. Basically what I need to know is a) how to switch between schemas using Hibernate depending upon the user and b) if this is a suitable approach in order to achieve performance and scalability. When I say user I say a) one that is logged into the application and b) one which has its own DB schema. All the schemas will share all of their entity-relation structure, but of course, different data. My prospected DB (Postgresql) is seem to be enabled to support that thousand schemas transparently.

Looking around, I found info about how to do this by defining multiple SessionFactory, but I think this approach don't work well for me as I need schema availability as soon as the related schema/DB structure is created from scratch (through a DB script), nor compile-time SessionFactory definitions inside an XML file, nor JNDI static schema binding.

I looked around internet, and several people suggest, in cases like this, not to do so; otherwise, they suggest to maintain all the info into the same schema. I think this possibly will not scale well for me, because a) it will lead to put too much relations between the entity that represents my user/business unit and all the other tables which may have a relation to the user, and it will complex my queries, b) the information from user1 isn't necessarily related to the info of user2, c) I expect a very fast growing (possibly exponentially) of the information and the users (possibly hundreds to thousands users) with thousands of rows of information there, as my solution is basically a cloud-based inventory management system; so a single schema will rapidly become overwhelmed and surpassed, and it will lead me to a table partition/clustering management nightmare. I see a divide-and-conquer node/schema approach may scale better (I could be wrong, anyway).

I think possibly this propossed approach may not be suitable considering there are the cache features and the info may eventually collide when another schema is loaded; any caveat/suggestion on this?

Hope you may help me to clarify my requirements in this case, and of course, how to switch between schemas by using the same application entities.

Thanks in advance.

[Roel De Nijs]

My first question with such a scenario is a very easy one: why? Why would your application need a schema for every user? Why can't they share the same schema and tables?