Hibernate and Multi-Tenant Database using Schemas in PostgreSQL
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"
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...
While sharding by schema is common, see this post from the Apartment gem authors covering some drawbacks.
At Citus, we shard via option #3 listed above, and you can read more in our use-case guide in the Documentation.