Multi Tenancy Data Models Compared

1. What multi tenancy means and which questions the data model must answer

Multi tenancy describes a software architecture in which a single application instance manages data for several mutually independent customers, so called tenants, at the same time. Every tenant expects its data to remain fully isolated from every other tenant's data, even though they may technically share the same database, the same tables, or even the same rows.

The data model behind multi tenancy must answer three central questions: how is it prevented that a tenant accidentally or deliberately sees another tenant's data? How are schema changes and migrations rolled out when hundreds or thousands of tenants are affected at once? And how is it prevented that a single tenant with unusually high load degrades performance for everyone else? The answer to these three questions differs fundamentally depending on the chosen model.

In practice there are three established basic models for multi tenancy: shared database with shared schema, where all tenants use the same tables and a column marks the tenant; schema-per-tenant, where every tenant gets its own database schema within the same database instance; and database-per-tenant, where every tenant gets a fully separate database. The following sections walk through each model in detail.

2. Model 1: shared database, shared schema with tenant_id

In the most common model for multi tenancy, all tenants share the same tables in the same database. Every tenant specific table gets an additional tenant_id column that uniquely assigns each row to a tenant. Every query must account for this column in its WHERE clause, either explicitly in application code or via an automatic scope in the ORM.

-- Shared schema: tenant_id as a mandatory column in every tenant specific table
CREATE TABLE invoices (
  invoice_id   SERIAL PRIMARY KEY,
  tenant_id    INTEGER NOT NULL REFERENCES tenants(tenant_id),
  customer_id  INTEGER NOT NULL,
  amount       NUMERIC(10,2) NOT NULL,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Composite index: tenant_id leading, so every query can be
-- efficiently narrowed down to its own tenant
CREATE INDEX idx_invoices_tenant_customer
  ON invoices (tenant_id, customer_id);

-- Every query MUST filter by tenant_id
SELECT * FROM invoices
WHERE tenant_id = 42 AND customer_id = 1001;

The advantage of this model lies in its operational and migration simplicity: a single schema, a single migration that applies to all tenants at once. Resources such as connection pools and backups are managed only once, which keeps infrastructure costs low with many small tenants. The big downside is the risk of human error: a single forgotten tenant_id condition in a new query can expose one tenant's data to another, a mistake that carries particular weight under multi tenancy.

3. Row level security as a safeguard for shared schema

To mitigate the risk of a forgotten tenant_id condition, PostgreSQL and some other databases offer row level security (RLS): a database level policy automatically filters every query by the currently active tenant, regardless of whether the application code itself remembered to do so. Isolation is thereby no longer left solely to application code, but additionally enforced by the database.

-- Enable row level security and define a policy
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.current_tenant')::int);

-- Application sets the active tenant at the start of every session/transaction
SET app.current_tenant = '42';

-- This query automatically sees ONLY rows with tenant_id = 42,
-- even though the WHERE clause itself contains no tenant_id condition
SELECT * FROM invoices WHERE customer_id = 1001;

-- Even a developer mistake without an explicit tenant_id condition
-- can no longer return another tenant's data

Row level security thus acts as a second line of defense that holds even when application code is faulty. The downside is added operational complexity: every database connection must reliably set the correct tenant context before executing queries, and connection pooling solutions must ensure that a reused connection does not accidentally retain an earlier session's tenant context.

4. Model 2: shared database, schema-per-tenant

The second model still keeps all tenants in the same physical database instance, but gives every tenant its own database schema with an identical table structure. The application switches the search_path, or the active schema, when establishing a connection, so identical SQL statements without any tenant_id filter automatically hit only the respective tenant's tables.

-- One schema per tenant, identical table structure in each
CREATE SCHEMA tenant_42;
CREATE SCHEMA tenant_43;

CREATE TABLE tenant_42.invoices (
  invoice_id   SERIAL PRIMARY KEY,
  customer_id  INTEGER NOT NULL,
  amount       NUMERIC(10,2) NOT NULL
);

-- Application switches the search_path when establishing the connection
SET search_path TO tenant_42;

-- Same query as with shared schema, but WITHOUT a tenant_id column,
-- because separation happens through the schema itself
SELECT * FROM invoices WHERE customer_id = 1001;

The advantage of this model is structural isolation at the database level, without running a separate database instance per tenant. A tenant technically cannot accidentally see another tenant's data, unless the search_path is explicitly set incorrectly. The downside appears at very high tenant counts: database systems have practical upper limits on the number of parallel schemas, and tools for migrations, backups, and monitoring must be able to handle hundreds or thousands of schemas, something many standard tools cannot do without adaptation.

5. Model 3: database-per-tenant

The third model goes one step further and gives every tenant a fully separate database, often even on its own database instance or its own server. The application keeps a routing table that stores connection information for the correct database per tenant, and routes every incoming request to the matching connection based on the tenant.

-- Central routing table in a separate "control plane" database
CREATE TABLE tenant_routing (
  tenant_id     INTEGER PRIMARY KEY,
  db_host       VARCHAR(255) NOT NULL,
  db_name       VARCHAR(100) NOT NULL,
  db_port       INTEGER NOT NULL DEFAULT 5432
);

-- Pseudocode: application picks a connection based on the tenant
-- SELECT db_host, db_name FROM tenant_routing WHERE tenant_id = 42;
-- connection = connectionPool.get(routingResult.db_host, routingResult.db_name);
-- connection.query("SELECT * FROM invoices WHERE customer_id = 1001");
-- No tenant_id column needed, physical separation handles isolation

-- Migration must run against each tenant database individually
-- for host, db in list_all_tenant_databases():
--     run_migration(host, db, "V42__add_discount_column.sql")

The advantage is maximum isolation: a data leak between tenants is practically ruled out at the database level, and individual tenants can even get different database versions, their own backup windows, or even geographically separate locations for regulatory requirements. The price is considerable operational effort: migrations must be rolled out across potentially thousands of individual databases, monitoring and capacity planning scale linearly with the tenant count, and infrastructure costs rise significantly more than in the other two models.

6. Migrations and schema changes across thousands of tenants

An often underestimated aspect of multi tenancy is the operational effort for schema migrations. With shared schema, a migration is trivial: a single ALTER TABLE statement automatically affects all tenants at once. With schema-per-tenant, and even more so with database-per-tenant, the same migration must run repeatedly, once per schema or database, which with thousands of tenants leads to migration runs that can take hours and leave an inconsistent intermediate state if a failure occurs mid rollout.

Practical solutions to this problem are migration orchestration tools that batch migrations in parallel, persist progress per tenant, and on failure retry only the affected tenants instead of restarting the entire run. A canary approach, where a migration first runs and is monitored against a small subset of tenants before the full rollout starts, significantly reduces the risk of a widespread failure.

7. Noisy neighbor problems and resource isolation

The so called noisy neighbor effect describes a situation where a single tenant, through unusually high load such as an inefficient batch job or an unexpected traffic spike, degrades performance for all other tenants on the same shared infrastructure. With shared schema and schema-per-tenant, all tenants share the same database resources such as CPU, memory, and I/O, which makes this effect structurally possible.

Countermeasures range from per tenant resource limits at the application level, such as rate limiting and query timeouts, through per tenant connection pool quotas, to deliberate segmentation: large or particularly resource hungry tenants move into a database-per-tenant model, while smaller tenants remain in the cheaper shared schema model. This hybrid strategy combines the cost efficiency of shared schema with the isolation of database-per-tenant exactly where it is truly needed.

8. Decision criteria: which model when

Choosing the right multi tenancy model depends on three factors: the expected number of tenants, isolation requirements from regulation or customer demand, and the available operational budget for infrastructure and migration tooling. A startup with thousands of small tenants and a small operations team is usually better off with shared schema plus row level security. A B2B provider with few but very large enterprise customers who explicitly demand dedicated infrastructure often benefits from database-per-tenant despite higher costs.

Schema-per-tenant often sits in between and suits medium tenant counts with moderate isolation requirements, for example when regulatory rules demand physical separation of tables without justifying a fully separate database per tenant. It is important not to treat this decision as a one time, unchangeable choice: many SaaS providers start with shared schema and later migrate individual, particularly demanding tenants into a more isolated model.

9. The three models compared

The following table compares the three models for multi tenancy by degree of isolation, migration effort, and infrastructure cost.

10. Summary

Multi tenancy is not a single technique but a range of models with different trade-offs between isolation, cost, and operational effort. Shared schema with tenant_id is the cheapest and easiest to migrate, but demands disciplined filtering in every query, which can additionally be secured with row level security. Schema-per-tenant offers structural isolation without the full cost burden of database-per-tenant, but runs into practical limits at very high tenant counts.

Database-per-tenant delivers maximum isolation and flexibility for individual large customers, but costs significantly more infrastructure and migration tooling. The right choice depends on tenant count, isolation requirements, and operational budget, and many successful SaaS architectures combine several models depending on tenant size in a hybrid strategy.

11. FAQ: Multi Tenancy Data Models Compared