Multi Tenancy

[zimt28]

I want to convert an existing app to a multi tenancy architecture and will be using Postgres schemas for this. One account will be a tenant = one Postgres schema.

So far I’ve had a single accounts table which held all accounts and their information:

public (schema)
└── accounts
    ├── contacts
    ├── orders
    ├── settings
    └── users

Now the account information should be stored somewhere within the schema boundaries, but having just one account table to store one single entry feels weird. It would look like this:

tenant_1 (schema)
├── account
├── contacts
├── orders
├── settings
└── users

tenant_2 (schema)
...

I cannot think of a good alternative and couldn’t find any examples online, not even for Rails or Laravel. Is this the way to go or am I missing something?

[egze]

I’ve never built multi-tenant apps, but could an accounts table be located in the public schema? You need to know somehow which accounts exist, who is paid/free/blocked/etc. You could put there the schema name associated with the account. Just an idea

[bigbassroller]

They way I am doing it is have just have a users table on public (along with billing invoice tables) and have another users table in the subdomain schema for that tenants users.

[thiagomajesk]

I’m also working with schema-based multi-tenancy in a new project. I don’t know if it helps, but this is my checklist so far:

1- Triplex to do the “heavy lifting”
2- “Tenant specific” routes scoped with the tenant name (slug)
3 - All tenant-related data is inside the tenant’s schema; the rest is considered “global” scoped and goes inside the public schema (platform configuration, views, etc)
4- Users access only one tenant “area” (~/tenant-name-slug/resource) at a time
5- We use the :prefix option in our queries, to get tenant-related data

I don’t know exactly what you are building, but I guess you could still store tenant-specific information in the public schema - depending on what you consider part of your global scope. There’s certainly no right answers.

Also, take into consideration that if you need to implement something like search or report, there are pros and cons of both approaches. For instance: queries may be faster inside the tenant’s scope because the schema will have fewer rows in total to be queried against; but if you need to collect global information about all tenants it’ll be more expensive since you’ll have to query all schemas at once.

There are a lot of approaches around these scenarios and all depends on what you want to achieve. In the end it depends on how you are separating the scopes of your application.

[thojanssens1]

@egze

I’ve never built multi-tenant apps

Most likely you did, just table-based multi-tenancy (use of id in the table to distinguish user), not schema-based as mentioned here.

[egze]

True that’s exactly what I meant.

[yurko]

We have a multitenant app that uses schemas, here are few basic points.

Triplex is a low level library that abstracts away few things that you will need and gets out of the way otherwise. It will help with migrations, prefix validation etc.

Similar to what others mentioned we have global stuff in public schema and a schema per tenant, these tenants are managed on the global level - public schema has a table with tenants and their prefixes.

We use plugs in tenant space to load and validate current prefix (schema) as well as to ensure the user only uses their own schema.

Most of the tenant context functions get an extra parameter for prefix which goes to repo calls (Ecto basically supports it out of the box).

We also have global users in public schema that with enough permissions can access any tenant.

All of that seems to work nicely, one thing you might want to know - it’s good if you have few big tenants, but not too many. After about 100 of them (somewhere between 100 and 900) you might experience problems running migrations.

[onkara]

Using either Triplex or some other library is it possible to achieve MultiTenancy without using separate schema or separate database for each tenant? i.e. just one Customer table whose foreign_key is in every child table and all queries are scoped to that tenant only? This is something easily implemented in Ruby gem acts_as_tenant

[mathieuprog]

See Multi tenancy with foreign keys

[chasers]

@yurko

All of that seems to work nicely, one thing you might want to know - it’s good if you have few big tenants, but not too many. After about 100 of them (somewhere between 100 and 900) you might experience problems running migrations.

This is a very important caveat to this per table tenant design. I would probably stay away from this design unless there is a very compelling reason you need to put each tenant in their own table.