Perhaps start with a simple test project and see how it works in practice, and then you will have some data to base a decision off of.

With ecto and triplex, it’s an extra line per tenantized query and you can pick out the tenant id in a plug so that is done in exactly one place. Not a big deal, really.

Here’s a screencast you may be interested in:

I’ve done both approaches for multitenant applications, because the situation differed in subtle ways:

• How many tenants do you expect? If it’s only a couple of dozen, and each one pays you plenty of monies, then you should probably just do a manual onboarding process and have each tenant on their own separate instance of your app. It allows you to give each tenant their own update schedule, means you never need to worry about them stomping all over each other, and can just generally babysit each customer as much as they deserve. On the other hand, if you expect thousands of tennants that don’t pay you very much, then you should do as much colocating as possible.

• How much overlap do you expect them to have? If it makes business sense to give all your users a single account throughout your system, then you definitely don’t want to mess with SSO solutions right out the gate. That’ll cancel out the “simplicity” you would’ve otherwise had.

• Do you expect your users to do local installations of your app? Worse, do you expect them to locally install the app and then have the app turn around and phone the mothership? If so, you might as well have all the hosted tenants on separate infrastructure behind an API gateway, so that the “cloud service” and the “on-prem service” can be kept as similar as possible.

By the way, if you get the choice wrong, you can fix it later. Slack started with all the tenants on separate VMs, but merged them into a single database later. Aspects of their UX still kind of reflect the old way, but it works and they’re doing fine overall despite the pivot.

You might notice that most of these aren’t really technical considerations, they’re business decisions. You need to decide if you want lots of cheap users, a self-serve web app, and not much integration, or if you want to go the Enterprise-with-a-capital-E path where your tenants each work with a sales representative (if this is a new company, that “sales rep” will probably be the same person as the CEO) and might get custom modules because one tenant asked for it.

Spinning up multiple instances works alright if you have a limited number of tenants, but becomes increasingly wasteful as the number of tenants rises. Upgrades become increasingly more onerous as instead of upgrading the number of systems required to satisfy actual use and demand, you need to update one instance per tenant. It also means that every new tenant requires an update to the front-end proxies, and whenever a new tenant is added that either the sign-up/creation machinery needs to be able to influence the orchestration infrastructure (one more attack surface), or it requires manual intervention. (That last point is less important if tenants come on with a manual process on your part anyways.) Infrastructure management tools are pretty amazing these days, but there is no reason to be more wasteful or make life more complicated than necessary.

Another downside with multiple instances is that you now have 3 places to track your tenancy settings: the front-end proxies, the configuration on each deployment, and in your code. One may expect that putting the tenancy into config means removing all such considerations from you code … but should there be shared resources (e.g. common database tables / information) between tenants, then you end up having one path for tenant-specific resources and general resources. shrug

Multi-tenancy is not perfect, of course, but it is simpler to manage deployments for and allows easier sharing of common resources. It also does mean that per-tenant features can not be controlled by application-global configuration, but needs to be per-tenant … on the other hand that means all your tenant information is kept in one place (usually the database).

Tools like triplex makes tenancy easy, as well.

For what it’s worth, the approach of setting a tenant_id on each table keeps things simple and scales well. From there, if you actually grow the database farther than a single instance you will be ready for Citus.