Comment by klysm
4 days ago
If you’re using Postgres, multi tenancy has been solved with row level security. It’s super easy to add a tenant id column to every table and a policy that only allows connections to see data from one tenant
4 days ago
If you’re using Postgres, multi tenancy has been solved with row level security. It’s super easy to add a tenant id column to every table and a policy that only allows connections to see data from one tenant
RLS is very useful and can solve multi tenancy and other problems, but it is complicated and can add a significant per row cost to queries if your policies get complicated.
The common path of comparing some constant like the role name to some column in the table is fine, and it's fast enough as the policy checker already has the row in hand when it does the check, but the natural tendency for people to want to abstract their policies into a function like has_permission() will blow up fast.
The best approach I've seen from pyramation's launchql [1] which precomputes policies into a bitstring and then masks that against a query constant bitstring of required permissions. Flexible policy definitions compiled into the row as bits so the check is as fast as possible.
[1] https://github.com/launchql/launchql
Sure if you start using it for more than just multitenancy you can get into performance trouble or other complexities. I haven’t felt tempted to put anything beyond the tenant level isolation though yet and it’s served us very well
Multi-tenancy causes performance issues that simply don't exist if each customer's data is in it's own database.
Yeah really depends on the volume of the data and how sensitive the workload is to a few milliseconds. For a lot of business use cases, it's totally worth it to maintain just one database.