Database per tenant - barring configuration errors to a connection string or something along those lines - means you won’t ever accidentally leak over other customers data
With Row level security, as good as it is, there is always a chance of leaking happening, due to bugs, user error or the like
Database per tenant also makes migrations safer, and allows easier versioning of an application, for example if you run a SaaS you may put your V2 application into permanent maintenance mode and allow existing customers to use it in perpetuity, while upgrading customers and new customers are put on the V3 platform. This is infinitely easier in practice when the database is per tenant
Many of our clients eventually want to host our app on-premises, so moving it to an on-premises environment is quite easy with the database-per-tenant approach. Just copy the database as is.
>> With Row level security, as good as it is, there is always a chance of leaking happening, due to bugs, user error or the like
That’s not correct. It’s hard isolation. In effect, RLS automatically applies a where clause preventing developers inadvertently accessing the wrong data.
If you don’t set the Postgres environment variable then no data is returned.
All the hard isolation without the schema sync pain.
> In effect, RLS automatically applies a where clause preventing developers inadvertently accessing the wrong data.
Right, but this assumes that you have a) remembered to enable RLS for every table where it matters and b) configured appropriate rules perfectly for every table. And there are tons of things you can mess up in the rules.
With per-tenant instances none of those things can happen.
The better isn’t clear here, why is it better?
Database per tenant - barring configuration errors to a connection string or something along those lines - means you won’t ever accidentally leak over other customers data
With Row level security, as good as it is, there is always a chance of leaking happening, due to bugs, user error or the like
Database per tenant also makes migrations safer, and allows easier versioning of an application, for example if you run a SaaS you may put your V2 application into permanent maintenance mode and allow existing customers to use it in perpetuity, while upgrading customers and new customers are put on the V3 platform. This is infinitely easier in practice when the database is per tenant