[mschuster91]

alternatively, why not use database views? That way you can make sure that customers can only see their own data. Permissions can be handled with normal database users by only granting them SELECT rights on the views.

If you're afraid of users tanking performance, read replicas. As instantaneous as it gets, and no customer can tank others.

[crabmusket]

I think my gut would feel safer having a serialisation boundary between the customer's queries and the primary data store. But yes in principle I don't see why this approach wouldn't work just as well. Food for thought.

[mschuster91]

The boundary is the read-replica, it cannot write back to the primary DB cluster. But yeah if you require absolute isolation of tenant data, aka even a compromise / local privilege escalation on the read-replica node shouldn't allow an attacker to read other tenants' data, no way around.

Or... depending if your database layout allows, you might be able to achieve that with a per-tenant read replica server and MySQL replication filters [1] or Postgres row filters [2].

A sqlite db is effectively the safest option because there is no way to bypass an export step... but it might also end up seriously corrupting your data (e.g. columns with native timezones) or lack features like postgre's spatial stuff.

[1] https://dev.mysql.com/doc/refman/8.4/en/change-replication-f...

[2] https://www.postgresql.org/docs/current/logical-replication-...