Rails + Postgres: Multi-tenancy done right?

Question

I am going to build an app using Rails. It uses multi-tenancy using the Apartment gem and PostgreSQL. The app will have users, each of which have an account. This implies that each user has it's own PostgresSQL schema; the users table is in the default schema.

Each user has his own list of customers in his own schema. A customer with the same email (essentially the same customer) can appear in multiple schemas. I want a customer to be able to log in and see all the users he's associated with. I can't put the customers table in the default/public schema because it's related to other tables that are not in the default schema.

What I thought I would do is create a link table between customers and users in the public schema. This table would contain the email of the customer and the id of the user. My issue with that is that I don't understand how well this would work with Rails. What I would like to achieve is something like customer.users.

So the question is: How should I approach this problem?

You seem to be saying that some users are tenants, and some users are customers. (Since customers can log in, they're users in some sense of that word.) And you want customers to be able to query across all tenants, because the mapping between users and customers is stored in tenant-specific schemas. But isn't that exactly the kind of thing a multi-tenant architecture is supposed to prevent? — Mike Sherrill 'Cat Recall'
@MikeSherrill'Catcall' Thanks for your answer. I took a couple days to think, and I realised that in my case, multi-tenancy has more disadvantages than advantages. I will build my Rails app without multi-tenancy. — Robert Audi

Beryllium Beryllium · Accepted Answer · 2013-05-22T10:40:34

I suggest to differ between your users (who log in, not part of a tenant), and the customers (which are kept separately, and located in each tenant). The users table (possibly accompanied by other tables) can hold the information for the assignment from user to schema/customer etc. I would not even use foreign keys to link the user table with the tables in the tenant, just to keep them really separate.

In short, the user table serves to authenticate and to authorize only.

Update: The question describes a multi-tenancy approach using separate database schemas for the individual tenants. In this setup up, I would not link the users with the customers by database foreign keys, and I would not query them together. Just authenticate against the users, and get the assigned tenant(s). After that switch to the tenant.

If you really want to query both items (users and customers) in one run, I would not use separate schemas: One schema, create a tenant table, and put a foreign key into all other tables (customers etc.). In this scenario you could even do without a separate user table, and you could query the (single) customer table.

Update 2: To answer your query question:

You can query for schemas in PostgreSql's meta data:

select schemaname from pg_tables where tablename = 'Customer'

Which gives you all schemas with a customer table. Using that information you can dynamically build a union select:

select name from schema1.customer
union all
select name from schema2.customer
union all

[...repeat for all schemas...]

to query all tables across schemas. You could use group by to eliminate duplicates.

Rails + Postgres: Multi-tenancy done right?

2 Answers