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
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.
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.
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
Something to keep in mind, as the complexity of your DB schema grows, that’s a multiplying factor of how much memory ends up being used per connection on the database, for connections that end up visiting a majority of tenant schemas. Things like table statistics get cached in the connections worker process. I’ve seen this cache approach 1GB per connection with a complicated schema and less than 100 tenants.
Have a simpler schema - every table, column and index will have statistics that contributes to the amount allocated per connection in the postgresql worker process. This means that partitioning is a multiplier. Have fewer schemas. Reduce the number of schemas that a connection visits - either by biasing a connections in your connection pool towards schemas it has already visited, or by being disconnected and a new connection replacing it in your application connection pool.
I think it’s a good idea to retain the tenant ID in your tables anyway, so that you can choose whether N tenants will share a schema, or if a tenant will have a schema to itself.