noob question about building a multi-tenant SaaS running on k8s with a shared-DB-separate-schema/tables approach. Does anyone here have such a setup? If so, what are some of the engineering and operational challenges you have run into? e.g., upgrades -- do you upgrade the application first and be able to slowly ripple the DB schema updates without downtime?

@omi sols If you don't have a strict requirement for data isolation, I would not use separate schemas. The common approach is Shared Schema with Tenant Identifiers: All tenants share the same schema, meaning tables are shared across tenants, but each record is tagged with a tenant identifier. This approach is more resource-efficient as it avoids duplicating table structures and is typically easier to scale horizontally. If you go with separate schemas, your maintenance and changes will require more work. The worse part is - it will only get worse as your customer base grows, because you will have to replicate the same changes on all schema instances. Although you could automate this process, it will add more complexity and opportunity for error to your deployment process.

thanks much for your response @Lazar Gintchin during user-research, a few folks said that they care about data isolation. In addition, we might also have tenant specific data/schemas sometimes. So, to not paint ourselves into a corner, I'm thinking shared-DB-separate-schema/tables might help. What are some of the patterns where I can go with Shared Schema with Tenant Identifiers and get some semblance of data isolation and maintain some fluidity in the schema? any thoughts or insights there?

@omi sols you wrote "during user-research, a few folks said that they care about data isolation". I am not sure what your use case and industry is, and I was not part of these conversations. However: • Do you know what they really want and whether separate schemas is the only way to meet their need? • This does not look like a strong case to take on the extra complexity of separate schemas • Unless your product falls into one or more of the categories below, I would seriously reconsider: 1. Healthcare (HIPAA Compliance) 2. Financial Services (PCI DSS Compliance) 3. Government and Defense 4. Multi-Client Legal Platforms 5. SaaS Products Serving Large Enterprises (with Internal Data Policies) 6. Educational Platforms Handling Student Data (FERPA Compliance)

sorry, I should have give some more context. We would fall under (5) above. We are building a SaaS to help enterprises with their AI/ML data and model monitoring.. and some of the people we are talking to are from the large banks and financial institutions and such. We expect to have ~50 tables per tenant and there would also be some custom-fields and custom-tables per tenant as well.. given how the data will be maintained internally.

Do you plan to have separate users for each db schema?

Lazar, the users and the related metadata will be in a global table (partitioned by the tenant-id). In the present design, we are going to have separate storage per tenant as well.

Omi, I was not referring to the product users. I was referring to the non-human users, who will have access to the database.

aah! even those "system" users should be in the same tenant tables, right? Would that be problematic?

In order for the schema separation to make sense you need a separate system user for each schema.