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.