Multi-Tenancy for SaaS: Database-per-Tenant, Schema, and Row-Level Security Compared

The multi-tenancy decision is usually made early and changed late. Teams pick an isolation model when they have five customers, then discover three years later that it’s the wrong one for where the product ended up. The migration is painful enough that some companies rebuild from scratch rather than attempt it.

There are three dominant approaches: database-per-tenant, schema-per-tenant, and shared database with row-level security. Each makes different tradeoffs between isolation, cost, and operational complexity. The right choice depends on what your product needs today and what it’s likely to need in two years.

What Multi-Tenancy Actually Means

A SaaS application is multi-tenant when multiple customers (tenants) share the same application infrastructure. The users of Customer A should never see data from Customer B, even though both are running queries against the same system.

That isolation requirement is where the three models diverge.

Model 1: Database per Tenant

Each tenant gets their own database instance. Customer A’s data lives in db_customer_a, Customer B’s in db_customer_b, and so on.

Your application maintains a routing layer that maps an incoming request to the right database connection:

def get_db_connection(tenant_id: str) -> Connection:
    config = tenant_registry.get(tenant_id)
    return create_connection(
        host=config.host,
        database=config.database,
        user=config.user,
        password=config.password,
    )

What it’s good for:

• Regulatory requirements. HIPAA, SOC 2, and GDPR sometimes require physical data isolation. “Each customer’s data is in a separate database” is a clean story for auditors.
• High-value enterprise contracts. Customers willing to pay $50K+/year often expect (and will pay for) dedicated infrastructure.
• Variable workloads. One customer running heavy analytics doesn’t affect others.
• Simple backup and restore. Restoring one tenant’s data doesn’t touch others.

What it costs:

• Connection overhead. Even a modest SaaS with 500 tenants now needs connection pooling across 500 databases. PgBouncer helps, but this adds infrastructure.
• Schema migrations run per-database. A simple ALTER TABLE now needs to run across every tenant database, usually via a migration orchestrator. If one fails mid-run, you have inconsistent schemas across tenants.
• Cost scales with tenant count. Each database needs minimum resources. At low tenant counts (under 50), this is fine. At hundreds or thousands, it gets expensive.
• Operational complexity. Monitoring, backups, and alerting multiply by tenant count.

This model works best for products with a small number of high-value customers where data isolation is a selling point, not just a nice-to-have.

Model 2: Schema per Tenant (PostgreSQL)

Tenants share a database server but each gets their own schema. In PostgreSQL, schemas are namespaces within a database: customer_a.orders and customer_b.orders are separate tables.

-- Create a schema for each new tenant
CREATE SCHEMA customer_a;

-- Tables are created per-schema during provisioning
CREATE TABLE customer_a.orders (
    id BIGSERIAL PRIMARY KEY,
    amount DECIMAL(10, 2),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

Your application sets the search path on each connection:

def get_tenant_connection(tenant_id: str) -> Connection:
    conn = pool.getconn()
    conn.execute(f"SET search_path TO {tenant_id}, public")
    return conn

What it’s good for:

• Softer isolation than database-per-tenant, but stronger than row-level security. A query that forgets to filter by tenant can’t cross schema boundaries.
• One database to manage. Backups, monitoring, and upgrades are simpler than the per-database model.
• Schema migrations are per-schema but can run in parallel within the same database instance.

What it costs:

• PostgreSQL has practical limits on schema count. Hundreds of schemas in one database work fine. Thousands start showing catalog bloat (slow pg_tables queries, long autovacuum cycles, planning overhead). The practical ceiling is usually a few thousand schemas before you need to shard across database instances.
• Schema creation on tenant signup adds latency and complexity. You need to run migration tooling at signup, not just insert a row.
• ORM support varies. Most ORMs handle multi-schema setups with configuration, but dynamic schema switching at the connection level requires care.

This model fits mid-market SaaS: more than a handful of customers, not yet at a scale where per-schema costs become a problem, and where one level of database isolation matters.

Model 3: Shared Database with Row-Level Security

All tenants share the same tables. A tenant_id column on every table identifies which tenant owns each row. Row-level security (RLS) in PostgreSQL enforces that queries only return rows belonging to the current tenant.

-- Enable RLS on the table
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Policy: only see rows where tenant_id matches the session variable
CREATE POLICY tenant_isolation ON orders
    USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- Application sets this before queries
SET app.tenant_id = 'f47ac10b-58cc-4372-a567-0e02b2c3d479';

In practice, the application sets the tenant ID as a session variable at connection setup:

@contextmanager
def tenant_context(conn, tenant_id: str):
    conn.execute(
        "SELECT set_config('app.tenant_id', %s, true)",
        [str(tenant_id)]
    )
    try:
        yield conn
    finally:
        conn.execute("SELECT set_config('app.tenant_id', '', true)")

The true parameter in set_config makes it transaction-scoped rather than session-scoped, which is safer with connection pools.

What it’s good for:

• Cost efficiency. One set of tables, one connection pool, shared infrastructure. Works at any tenant count from 10 to 10 million.
• Simple schema migrations. One ALTER TABLE, done.
• Query efficiency for cross-tenant analytics. Reporting across all tenants is a single query, not a union across databases.
• Fast tenant provisioning. Adding a tenant is an INSERT into a tenants table, not a schema migration.

What it costs:

• Tenant data mixes in the same physical table. A query bug that doesn’t set app.tenant_id (or sets it incorrectly) can expose one tenant’s data to another. RLS is a strong guard but requires discipline.
• A misconfigured RLS policy is a data breach. Unlike the database-per-tenant model where forgetting to set a connection parameter causes a connection error, RLS failures can be silent.
• Performance isolation is absent. One tenant running a heavy query affects table lock contention and cache pressure for others.
• Enterprise customers with compliance requirements often won’t accept this model.

This model is the right default for most early-stage SaaS products. The operational simplicity and cost efficiency are significant, and the RLS implementation in PostgreSQL is mature enough to trust in production.

Comparison at a Glance

Criteria	DB per Tenant	Schema per Tenant	Shared + RLS
Isolation strength	Strongest	Strong	Relies on policy
Cost per tenant	Highest	Medium	Lowest
Migration complexity	High	Medium	Low
Tenant provisioning	Slow	Medium	Fast
Scale limit	~hundreds	~thousands	Millions
Enterprise/compliance	Best	Good	Harder to sell
Cross-tenant queries	Complex	Complex	Simple

Migrating Between Models

Teams most often need to migrate from shared RLS to schema-per-tenant or database-per-tenant as the business grows and enterprise contracts arrive.

The pattern is always the same:

1. Build the new model in parallel (schemas or databases for new tenants)
2. Write a migration script that exports tenant data from the shared table and imports it into the new structure
3. Migrate tenants one at a time, validating data integrity before cutover
4. Route the tenant to the new isolation model
5. Repeat until all tenants are migrated, then remove the old code path

The expand-contract approach from zero-downtime migrations applies here too: run both models simultaneously during the transition, never flip all tenants at once.

Hybrid Approaches

Nothing forces you to pick one model globally. Some teams run shared RLS for most tenants and spin up dedicated schemas or databases for enterprise customers who require it. The routing layer just returns different connection configs based on tenant type:

def get_tenant_context(tenant_id: str):
    tenant = tenants.get(tenant_id)
    if tenant.isolation_tier == "dedicated":
        return DedicatedDBConnection(tenant.db_config)
    elif tenant.isolation_tier == "schema":
        return SchemaConnection(pool, tenant.schema_name)
    else:
        return RLSConnection(pool, tenant_id)

This lets you sell an isolation upgrade as a paid tier without rebuilding the product.

The Decision

If you’re starting out and have fewer than 50 customers, shared database with RLS is almost always the right call. The migration path to stronger isolation exists and is understood; the operational simplicity of a single database while you’re still finding product-market fit is worth more than early isolation.

If compliance requirements or enterprise contracts are part of the plan from day one, design for schema-per-tenant. It’s a reasonable middle ground that satisfies most audit requirements without the operational overhead of per-database management.

Reach for database-per-tenant only when you have specific customers requiring it and the revenue to justify the operational cost. The isolation is real, but so is the complexity.

Whatever you pick, put tenant_id on every table from day one, even in the shared model. Retrofitting it later, after rows have no ownership metadata, is the migration nobody wants to do.