I am building an app where every company has it own private schema(Postgresql).
For every requisition, I set the Postgres search path in a before_action like this:
ActiveRecord::Base.connection.schema_search_path = 'company_id, public'
My doubt is, if I have multiple Unicorn workers, and one worker 'A' set the path, while another work 'B' set the path before worker A has finish, I think it will generate some conflict and 'A' worker could accidently save/read models from the wrong schema, right?
Is there another solution that could work better with Unicorn design?
Edit, schema details:
Each company has many users. Both users and companies tables live in the public schema, the rest( products, clients...) live in private schemas
Edit, more research:
After some research, I found that each database client connection has it own search path. Hence, if I change the search path using one connection, the others won't be affected, so this could work with Unicorn because each request has it own connection, but it will not work with multi-threaded servers like Puma.
But, there are still some problems cited in the answers, like ActiveRecord reloading the schema for each request. I would like to hear the experience of someone who is using this approach in production.
I don't think multiple schemas is a great idea because your ORM will need to re-load its schema on each request unless you plan on running a server instance for each tenant...
Multiple schemas are not scalable in postres anyhow from what I have read. If you have tens of thousands of tenants you will start to get performance issues.
The approach I have used is to have a tenant_id in each table and just use a scope on your models and some validation checks to ensure that related models are within the required tenant or user scope. Its really very simple and works well.
I use request_store to set both User.current and Tenant.current
from a base controller so that I have the needed context in my models to restrict and enforce tenant or user scope where required. I posted an example of this to another stack overflow question here.
I found that in my multi-tenant app not everything was isolated to a tenant, and I needed some tables to be shared so I quickly discounted the multiple schema solution, that, plus the per request schema reload issue and being able to easily create new tenants as normal model saves made multiple schemas a non starter.
Assuming you manage to avoid schema inconsistency problems within AR you also need to consider that live streaming, SSE or websockets are ruled out or become incredibly difficult with your approach as you cannot have threads operating in different tenants and Unicorn also doesn't support long running requests.
You may wish to instead consider using the EventMachine based Thin server and possibly rack-fiber-pool so that you can support currency, slow clients and long running requests for live streaming or SSE and not have thread related issues yet have excellent scalability. With the fiber approach you would need to work out how to switch/restore the schema context when the fiber is resumed but in principle it is doable.