keepcalculator

06 Numbers and Powers

Multi-Tenant SaaS Architecture: Data Boundaries

Card No. 06·76179 · Updated · Standard formula, shown below

Multi-tenant SaaS architecture is about choosing your data boundaries, not just your deployment model. The core decision is how you isolate and store each customer’s data, which determines your scalability, cost, and security posture far more than whether you use a single shared database or separate containers.

What Are the Real Options?

Most discussions simplify multi-tenancy into three models, but the reality is a spectrum. The three canonical approaches are:

  • Single database, shared schema: All tenants share the same tables, with a tenant_id column on every row. This is the cheapest and simplest to operate, but it makes cross-tenant queries easy (a security risk) and requires careful indexing to avoid performance interference.
  • Schema-per-tenant: Each tenant gets its own schema (namespace) within the same database. This provides better logical isolation and makes it easier to restore or migrate a single tenant, but it increases the number of objects to manage and can strain connection limits.
  • Database-per-tenant: Each tenant gets a separate database, often on the same server or cluster. This offers the strongest isolation and allows per-tenant backup/restore, but it multiplies operational overhead and can be expensive at scale.

There are also hybrid approaches, such as pooling tenants into a few databases based on size or tier, or using a shared database with row-level security (RLS) to enforce isolation at the database engine level.

Why Data Boundaries Matter More Than Deployment

Deployment choices (e.g., containers, serverless, on-premises) affect how you run the application, but data boundaries affect how you scale, secure, and bill. For example, you could deploy a single codebase on Kubernetes, but if you put all tenants in one database, you still face the risk of one noisy tenant slowing down everyone. Conversely, you could run a monolithic app on a single server with database-per-tenant and still have excellent isolation. The data layer is where the long-term architectural commitments are made.

Data boundaries also drive compliance and data residency. If you have a customer in the EU, you may need to keep their data in an EU data center. Database-per-tenant makes it easier to place each tenant’s data in a specific region, whereas a shared database forces you to store everyone together, possibly violating residency requirements.

How to Choose the Right Model

There is no one-size-fits-all answer; the choice depends on your product, market, and resources. Consider these factors:

  • Tenant size and count: If you have thousands of small tenants (e.g., a task manager for individuals), a shared schema is usually best. If you have a few dozen large enterprises, database-per-tenant may be worth the cost.
  • Isolation requirements: Do your customers demand that their data be physically separated from others? Some regulated industries (healthcare, finance) may require this, pushing you toward database-per-tenant.
  • Operational capacity: A small team can manage a shared schema easily. Database-per-tenant requires automation for provisioning, backups, and monitoring, or it becomes a nightmare.
  • Cost per tenant: Shared schema has the lowest marginal cost per tenant. Database-per-tenant has a high fixed cost per tenant, so it only makes sense for high-revenue accounts.

A common pattern is to start with a shared schema to validate product-market fit, then migrate to schema-per-tenant or database-per-tenant as larger customers arrive. This migration is non-trivial but doable if you keep the tenant ID in all queries from day one.

Common Problems and How to Avoid Them

Several pitfalls appear across all models:

  • Leaky tenant isolation: Forgetting a WHERE tenant_id = ? clause in a query can leak data. Use a query linter, or better, use row-level security (RLS) in Postgres or similar features to enforce isolation at the database level.
  • Connection exhaustion: With schema-per-tenant or database-per-tenant, each tenant may require a connection. If you have 1,000 tenants, you’ll exhaust the default connection pool. Use a connection pooler like PgBouncer, or switch to a model with fewer connections.
  • Backup and restore complexity: With a shared schema, restoring one tenant requires restoring the whole database and then extracting that tenant’s data. With database-per-tenant, you can restore a single database, but you need a system to automate that.
  • Schema migrations: In a shared schema, a migration affects all tenants at once. In schema-per-tenant, you need to run the migration across hundreds of schemas, which can be slow and error-prone. Use migration tools that support multi-tenant environments.

Cost and Operational Trade-offs

The financial impact is significant. A shared schema uses the least storage (no per-tenant overhead) and the least compute (shared indexes, shared cache). A schema-per-tenant adds a small overhead per schema (e.g., metadata), but the big cost is in backup size and migration time. A database-per-tenant multiplies the number of database instances, which increases licensing costs (if any), backup storage, and the number of processes to monitor.

Operationally, a shared schema is the easiest to manage: one database to back up, one to tune. Database-per-tenant requires automation to create a database for each new customer, run migrations, and monitor health. Many teams use a combination: a shared schema for small/self-serve customers, and dedicated databases for enterprise customers.

Security and Compliance Considerations

Data boundaries are a security control. A shared schema with a single breached query can expose all tenants. Database-per-tenant limits the blast radius of a compromise to one tenant. However, even with separate databases, a flaw in the application layer (e.g., an API that doesn’t check tenant permissions) can still leak data. Always enforce tenant isolation at the application layer as well.

Compliance requirements (GDPR, HIPAA, SOC 2) often require that you can delete a tenant’s data completely. Database-per-tenant makes this easy: drop the database. With a shared schema, you must carefully delete all rows with that tenant’s ID, which is error-prone. Also, data residency can be handled more cleanly with per-tenant databases in specific regions.

Alternatives and Emerging Patterns

Beyond the three classic models, there are newer approaches:

  • Row-level security (RLS): The database engine filters rows based on a session variable (e.g., SET app.tenant_id = 123). This gives you shared-schema simplicity with enforced isolation. It’s available in Postgres, SQL Server, and some other databases.
  • Pooled databases with routing: Group tenants into pools based on size or activity, and route each tenant to the appropriate pool. This is a middle ground between shared and dedicated.
  • Microservices with per-service data: Each microservice may have its own database, and you can choose a different tenancy model per service. For example, a billing service might use a shared schema, while a document storage service uses database-per-tenant.

Frequently Asked Questions

Can I start with a shared schema and migrate to database-per-tenant later?

Yes, but it’s a significant effort. You’ll need to write a migration tool that extracts a tenant’s data from the shared tables and inserts it into a new database. The process is easier if you have a consistent tenant_id on every table and if you have a way to identify all related data (e.g., foreign keys). Plan for downtime or use a dual-write strategy during migration.

Is schema-per-tenant better than database-per-tenant for security?

Not inherently. Both provide logical isolation, but database-per-tenant gives physical separation, which can be a requirement for certain compliance frameworks. Schema-per-tenant still shares the same database instance, so a vulnerability in the database engine could affect all schemas. However, for most applications, schema-per-tenant is secure enough if you use strong access controls.

How does row-level security (RLS) compare to the traditional models?

RLS is a feature that lets you define security policies on tables, automatically filtering rows based on the current session’s tenant ID. It gives you the operational simplicity of a shared schema (one database, one set of indexes) while providing strong isolation at the database level. It’s a good choice for many SaaS products, but it requires careful configuration and may not be supported by all database engines.

What is the best model for a small SaaS with 10 customers?

For a small number of customers, database-per-tenant is often the easiest to manage and provides the best isolation. You can create a database for each customer manually, and the overhead is low. As you grow to hundreds or thousands of customers, you’ll likely want to move to a shared schema or schema-per-tenant to reduce costs.

Does the choice of database engine affect the tenancy model?

Yes. Some databases have features that make certain models easier. For example, Postgres has native RLS and supports many schemas per database. MySQL has no RLS, so you’d rely on application-level filtering. SQL Server has schemas and row-level security as well. Also, some managed database services have per-tenant pricing that might influence your choice.

How do I handle tenant-specific customizations in a shared schema?

You can use a JSON column for flexible attributes, or have a separate table for tenant settings. Avoid altering the schema per tenant, as that breaks the shared model. If a tenant needs a truly custom schema, consider moving them to a dedicated database.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.

More cards in this drawer

The whole Numbers and Powers drawer →