CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecordrails-activerecordunicorn
Jirico
Jirico

Reputation: 1262

Multi-tenant Rails app with Postgresql and Unicorn

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.

Upvotes: 2

Views: 886

Answers (4)

penguincoder
penguincoder

Reputation: 587

I made a gem called acts_as_restricted_subdomain that implements the single-schema strategy outlined above. We have been using it successfully in production with Unicorn and Resque for about 4 years now to separate all of our clients' data from each other with no spillover.

Upvotes: 1

Andrew Hacking
Andrew Hacking

Reputation: 6366

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.

Upvotes: 3

Habax
Habax

Reputation: 1332

I can't directly answer to your question, but you might give a look to the acts_as_tenant gem.

Here is an example of usage: https://github.com/Bahanix/RubyBB/blob/master/app/controllers/application_controller.rb#L15-L25

Upvotes: 1

phoet
phoet

Reputation: 18835

i think this is not a feasible solution. this is what the docs say:

schema_search_path=(schema_csv) public Sets the schema search path to a string of comma-separated schema names. Names beginning with $ have to be quoted (e.g. $user => ‘$user’). See: http://www.postgresql.org/docs/current/static/ddl-schemas.html

This should be not be called manually but set in database.yml.

and this is the implementation

    def schema_search_path=(schema_csv)
      if schema_csv
        execute("SET search_path TO #{schema_csv}", 'SCHEMA')
        @schema_search_path = schema_csv
      end
    end

looks like it's too global for your usecase.

Upvotes: 2

Related Questions