Deploying a database per tenant is not that easy. You have a lot of new overhead, migrations become a pain in the ass (already are) and a lot of other little problems...
I would say a database per tenant is overcomplicating it.
A database per tenant makes the rest of the workflow significantly easier though. No need to add clauses to SQL WHERE statements for users/groups. Queries are faster (less data). And data can be moved much easier between servers.
Yes, it does add extra overhead at account creation, during DB migrations, and for backups.
But if you don’t need cross-account or public data access, it can make life much easier.
> No need to add clauses to SQL WHERE statements for users/groups.
This is basically what RLS does for you. You specify the access and you specify the current user(via a connection, SET ROLE, etc). Then it does all that complicated query filtering stuff for you, to ensure you don't screw it up.
> Queries are faster (less data). And data can be moved much easier between servers.
Not really, the overhead is just different(and likely more of it) in your solution. It's not wrong nor is using RLS right.
> No need to add clauses to SQL WHERE statements for users/groups.
ORMs have support to add in these conditions automatically, you only have to define the condition in 1 function. This gives you the best of both worlds. You can use your FK based separation and have application level guarantees that your tables with a tenant id are scoped to a tenant by default and you have the option to query multiple tenants to do super admin level reporting across tenants by using explicit function calls that basically say "yes I know I'm bypassing the auto-enforced tenancy check".
There's no performance issues since it's all 1 schema and there's no nightmare or overhead of having to run a DB migration for each database.
I'm not sure why the author didn't include this in their blog post because it's a popular option in Rails, Flask (SQLAlchemy), etc..
You end up building just a bit of automation around it and its fine. The migration isn't any harder, you just run an ansible job or something to roll it out to all databases.
Deploying multiple databases is typically costly in the infrastructure as a service space. Plus you have more operational overhead in ensuring backups work and keeping things secure. It's much easier to use Postgres' schemas to segment the data within one single database. Frankly schemas are much easier to reason about, maintain, scale, and keep compliant than row level security.
I worked for a SaaS that did that. We had 1000's of clients. Migrations would take all afternoon. We had custom connection pools, custom migration runners, and other weird stuff (this was about 10 years ago.) It was way too complicated, especially since most of the tenants had very little data.
Only really worth it if each tenant is creating enough value to justify this. Hard to see how a SaaS product with a cheap/free tier would pull this off.
I would say a database per tenant is overcomplicating it.