Multi-Tenant Database Architecture for SaaS: Row-Level Security vs Schema-per-Tenant

Every multi-tenant software system must answer a foundational engineering question: how do we guarantee that Tenant A can never view or overwrite the data belonging to Tenant B?

Early in a startup’s lifecycle, architectural mistakes here can cripple development velocity. Choosing an overly complex isolation model introduces migration nightmares and exhausts database connection pools. Conversely, choosing an informal model—relying solely on developer discipline to remember WHERE tenant_id = ? on every query—eventually results in catastrophic cross-tenant data leakage.

This guide benchmarks the three primary multi-tenant architectural patterns and details how to implement Postgres Row-Level Security (RLS) safely.

The Three Architecture Patterns Compared

1. Row-Level Security (RLS) in a Shared Schema

In this model, all tenants share identical database tables (e.g., projects, invoices, users). Every table includes a tenant_id foreign key. The database engine itself enforces access control policies at the storage layer.

• Infrastructure Cost: Minimal. A single $20/month managed Postgres instance (Neon, Supabase, Render, AWS RDS) can comfortably serve thousands of small business tenants.
• Migration Overhead: Zero complexity. Running an ALTER TABLE schema migration executes once across the entire cluster.
• Cross-Tenant Analytics: Simple. Running aggregate SQL queries for platform-wide metrics requires no union operations across schemas.

2. Schema-per-Tenant (Logical Separation)

Each tenant is allocated a dedicated Postgres schema (tenant_101.projects, tenant_102.projects) inside a single database instance.

• Isolation: Hard logical boundary. A bug in application code cannot accidentally read across schemas without explicit search path changes.
• The Failure Mode: Schema migration and connection exhaustion. If you have 2,000 tenants, adding a single column requires executing 2,000 sequential ALTER TABLE statements. Furthermore, Postgres caching structures and connection pools (like PgBouncer) struggle when thousands of dynamic schemas must be held in memory.

3. Database-per-Tenant (Physical Separation)

Each tenant receives an independent database server or cluster.

• Use Case: Reserved exclusively for strict compliance or high-paying enterprise contracts ($50,000+ ARR) that demand dedicated encryption keys, custom backup retention, and zero noisy-neighbor interference.
• Cost for Bootstrappers: Prohibitive. Minimum base costs scale linearly with customer count.

Implementing Postgres Row-Level Security (RLS)

Postgres has native support for Row-Level Security. When RLS is enabled on a table, all SQL queries (SELECT, INSERT, UPDATE, DELETE) pass through security barrier filters before returning results.

Step 1: Enable RLS and Force Policy Check

-- Create the multi-tenant table
CREATE TABLE projects (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Crucial: Enable RLS on the table
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- Recommended: Enforce RLS for table owners to avoid accidental superuser bypass
ALTER TABLE projects FORCE ROW LEVEL SECURITY;

Step 2: Define the Tenant Context Policy

Instead of hardcoding tenant IDs, read the current tenant from a session-level configuration variable (app.current_tenant_id):

-- Create security policy for multi-tenant isolation
CREATE POLICY tenant_isolation_policy ON projects
    FOR ALL
    USING (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::UUID)
    WITH CHECK (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::UUID);

Step 3: Application Middleware (Setting Session Context)

In your backend application (Node.js, Go, Python, or Ruby), execute a session variable set within each database connection before running business queries:

// Express / Fastify middleware: establish tenant context per request
async function withTenantContext(req, res, next) {
  const tenantId = req.user.tenantId; // Extracted from verified JWT
  
  // Obtain connection from pool
  const client = await pool.connect();
  try {
    // Set current session tenant ID (scoped to the active transaction)
    await client.query("SELECT set_config('app.current_tenant_id', $1, true)", [tenantId]);
    
    // Attach scoped client to request
    req.db = client;
    
    // Proceed to route handler
    next();
  } finally {
    // Release connection back to pool
    client.release();
  }
}

Now, even if a junior developer writes SELECT * FROM projects; without any WHERE clause, Postgres automatically filters and returns only the rows belonging to that authenticated tenant.

Performance Optimizations: Composite Indexes

When using RLS in a shared database, performance will degrade if your queries trigger sequential table scans across millions of rows.

Always create composite indexes starting with tenant_id:

-- Optimal composite indexing for multi-tenant tables
CREATE INDEX idx_projects_tenant_created 
ON projects (tenant_id, created_at DESC);

This guarantees that the Postgres query planner immediately narrows the index scan to the tenant’s partition before sorting or filtering on secondary fields.

For independent founders scaling from 0 to 5,000 customers, Postgres RLS in a single schema provides the ideal balance of ironclad security, low infrastructure cost, and friction-free database migrations.