Multi-tenant rails application: what are the pros and cons of different techniques?

Question

I originally wrote my Ruby on Rails application for one client. Now, I am changing it so that it can be used for different clients. My end-goal is that some user (not me) can click a button and create a new project. Then all the necessary changes (new schema, new tables, handling of code) are generated without anyone needing me to edit a database.yml file or add new schema definitions. I am currently using the SCOPED access. So I have a project model and other associated models have a project_id column.

I have looked at other posts regarding multi-tenant applications in Rails. A lot of people seem to suggest creating a different schema for each new client in Postgres. For me, however, it is not much useful for a new client to have a different schema in terms of data model. Each client will have the same tables, rows, columns, etc.

My vision for each client is that my production database first has a table of different projects/clients. And each one of those tables links to a set of tables that are pretty much the same with different data. In other terms a table of tables. Or in other terms, the first table will map to a different set of data for each client that has the same structure.

Is the way I explained my vision at all similar to the way that Postgres implements different "schemas"? Does it look like nested tables? Or does Postgres have to query all the information in the database anyway? I do not currently use Postgres, but I would be willing to learn if it fits the design. If you know of database software that works with Rails that fits my needs, please do let me know.

Right now, I am using scopes to accomplish multi-tenant applications, but it does not feel scalable or clean. It does however make it very easy for a non-technical user to create a new project provided I give them fillable information. Do you know if it is possible with the multi-schema Postgres defintion to have it work automatically after a user clicks a button? And I would prefer that this be handled by Rails and not by an external script if possible? (please do advise either way)

Most importantly, do you recommend any plugins or that I should adopt a different framework for this task? I have found Rails to be limited in some cases of abstraction as above and this is the first time I have ran into a Rails-scaling issue.

Any advice related to multi-tenant applications or my situation is welcome. Any questions for clarification or additional advice are welcome as well.

Thanks, --Dave

Answer 1

MSDN has a good introduction to multi-tenant data architecture.

At one end of the spectrum, you have one database per tenant ("shared nothing"). "Shared nothing" makes disaster recovery pretty simple, and has the highest degree of isolation between tenants. But it also has the highest average cost per tenant, and it supports the fewest tenants per server.

At the other end of the spectrum, you store a tenant id number in every row of every shared table ("shared everything"). "Shared everything" makes disaster recovery hard--for a single tenant, you'd have to restore just some rows in every shared table--and it has the lowest degree of isolation. (Badly formed queries can expose private data.) But it has the lowest cost per tenant, and it supports the highest number of tenants per server.

My vision for each client is that my production database first has a table of different projects/clients. And each one of those tables links to a set of tables that are pretty much the same with different data. In other terms a table of tables. Or in other terms, the first table will map to a different set of data for each client that has the same structure.

This sounds like you're talking about one schema per tenant. Pay close attention to permissions (SQL GRANT and REVOKE statements. And ALTER DEFAULT PRIVILEGES.)