Multi-Tenant SaaS Architecture: Row-Level vs Schema-Per-Tenant

Choosing the right multi-tenancy model is a critical architectural decision when building a B2B SaaS. It dictates how securely tenant data is isolated, how easily you can scale, and how much your infrastructure will cost.

Here is a deep technical comparison of the two primary multi-tenancy models in relational databases.

1. Row-Level Multi-Tenancy (Shared Database, Shared Schema)

In this model, all tenants share the same database tables. Every record in a tenant-specific table contains a tenant_id column.

[Shared Database]
  -> Table: Users (id, name, email, tenant_id)
  -> Table: Invoices (id, total, date, tenant_id)

Securing Data with PostgreSQL Row-Level Security (RLS)

To prevent accidental data leakage, we enforce isolation at the database level using PostgreSQL Row-Level Security (RLS). This ensures that queries without a matching tenant context fail:

-- 1. Enable RLS on the target table
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

-- 2. Create policy to filter records by tenant context
CREATE POLICY tenant_invoice_isolation ON invoices
    FOR ALL
    USING (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid);

In your application server, set the database context before executing queries:

// src/lib/db.ts
import { Pool } from "pg";

const pool = new Pool();

export async function queryTenantInvoices(tenantId: string) {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    // Set tenant context for this connection session
    await client.query("SET LOCAL app.current_tenant_id = $1", [tenantId]);
    
    // RLS policy will automatically restrict results
    const result = await client.query("SELECT * FROM invoices");
    
    await client.query("COMMIT");
    return result.rows;
  } catch (error) {
    await client.query("ROLLBACK");
    throw error;
  } finally {
    client.release();
  }
}

---

2. Schema-Per-Tenant (Shared Database, Isolated Schemas)

In this model, each tenant has a distinct SQL schema (namespace) containing its own copy of the application's tables.

[Shared Database]
  -> Schema: tenant_abc (Users, Invoices, Settings)
  -> Schema: tenant_xyz (Users, Invoices, Settings)

• Migration Complexity: High. Schema migrations must run across all schemas, which requires complex deployment coordination.
• Customizability: High. You can add custom columns or tables for specific high-value enterprise accounts without affecting other tenants.

---

Comparison Summary

| Metric | Row-Level Security (RLS) | Schema-Per-Tenant | | :--- | :--- | :--- | | Data Isolation | Logical (database policies) | Structural (isolated namespaces) | | Resource Efficiency | Maximum (shared connections & memory) | Moderate (higher connection pool usage) | | Migration Overhead | Low (run schema script once) | High (loop and run schema migration per tenant) | | Backup Restoration | Hard (must filter records to restore) | Easy (restore individual schemas) |

For 95% of early to mid-stage SaaS applications, we recommend Row-Level Security in PostgreSQL. It keeps infrastructure costs low while providing reliable data isolation.