| We ran a multi-tenant SaaS product for years w/ a schema-per-tenant approach. For the most part it all worked pretty great. We ran into issues here and there but always found a way to work around them: * Incremental backups were a pain because of needing to lock so many objects (# of schemas X # of tables per schema). * The extra code to deal w/ migrations was kinda messy (as you mentioned). * Globally unique IDs become the combination of the row ID + the tenant ID, etc... For us though the real deal-breaker turned out to be that we wanted to have real foreign keys pointing to individual rows in tenant schemas from outside of the tenant schema and we couldn't. No way to fix that one since with multi-schema the "tenant" relies on DB metadata (the schema name). We ended up migrating the whole app to RLS (which itself was a pretty interesting journey). We were afraid of performance issues since the multi-schema approach kinda gives you partitioning for free, but with the index usage on the RLS constraints we've had great performance (at least for our use case!). After quite a bit of time working with both multi-schema & RLS I probably wouldn't go back to multi-schema unless I had a real compelling reason to do so due to the added complexity. I really liked the multi-schema approach, and I think most of the critiques of it I found were relatively easy to work around, but RLS has been a lot simpler for us. |