I'm designing a B2B SaaS application and debating between a "Database-per-tenant" vs. a "Shared Schema" approach. What are the trade-offs regarding row-level security, scaling to thousands of tenants, and the complexity of running cross-tenant analytics for our internal business intelligence needs?
3 answers
For most startups, a shared schema with a discriminator column (Tenant_ID) is the way to go. It keeps infrastructure costs low and deployment pipelines much simpler.
The "Shared Schema" approach is generally more cost-effective and easier to maintain at scale, provided you have a rock-solid Row-Level Security (RLS) implementation. Databases like PostgreSQL have excellent built-in RLS features. However, if you have enterprise clients with strict regulatory requirements, they might demand a "Database-per-tenant" model for total isolation. For internal analytics, a shared schema is a dream because you don't have to aggregate data from 500 different databases. Use a robust tenant ID indexing strategy to ensure that query performance doesn't degrade as your tables grow into the billions of rows.
Have you considered the "Noisy Neighbor" effect? If one tenant runs a massive report in a shared schema, how do you prevent it from slowing down the app for everyone else?
Richard, we solve that by using resource tagging and read-replicas. By directing heavy analytical queries to a replica, the primary transactional database remains responsive for all other active tenants.
I agree. Managing migrations across hundreds of separate databases is a DevOps nightmare that most small teams aren't prepared to handle effectively early on.