SaaS Database Architecture: Single-Tenant vs. Multi-Tenant Patterns

Choosing the database architecture for your B2B SaaS is one of the most critical decisions you will make. It directly impacts your infrastructure costs, development velocity, database migration complexity, and compliance with data security standards (such as GDPR, HIPAA, or SOC2).

A mistake here is incredibly expensive to rectify later. In this technical guide, we will analyze the three primary multi-tenant database patterns, map their trade-offs in detail, and write a complete, secure implementation using PostgreSQL Row-Level Security (RLS).

The Three Multi-Tenant Paradigms

+-----------------------+   +-----------------------+   +-----------------------+
|  Pattern A: Separate  |   |  Pattern B: Separate  |   |   Pattern C: Shared   |
|       Databases       |   |        Schemas        |   |    Database & Tables  |
+-----------------------+   +-----------------------+   +-----------------------+
|  [DB 1]      [DB 2]   |   |  [Database]           |   |  [Database]           |
|  Tenant A    Tenant B |   |   ├── Schema Tenant A |   |   └── [Table]         |
|                       |   |   └── Schema Tenant B |   |        ├── Row Tenant A|
| (Max Isolation/Cost)  |   | (Medium Isolation)    |   |        └── Row Tenant B|
+-----------------------+   +-----------------------+   +-----------------------+

1. Deep Dive Comparison

To make an informed decision, let's contrast the three patterns across operational metrics:

2. Implementing Row-Level Security (RLS) in PostgreSQL

If you choose Pattern C (Shared Database & Tables) due to its cost efficiency and ease of maintenance, you must guarantee that users cannot accidentally or maliciously access another customer's data.

PostgreSQL Row-Level Security (RLS) handles this by applying filter policies directly inside the SQL engine.

Step 1: Create Tables and Enable RLS

We will design a simple CRM table for storing leads. We explicitly enable RLS using the ALTER TABLE statement.

-- Create tenants table
CREATE TABLE tenants (
  id SERIAL PRIMARY KEY,
  company_name VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Create projects table with tenant mapping
CREATE TABLE projects (
  id SERIAL PRIMARY KEY,
  tenant_id INT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  name VARCHAR(255) NOT NULL,
  status VARCHAR(50) DEFAULT 'draft',
  created_at TIMESTAMP DEFAULT NOW()
);

-- Index the tenant_id column (Crucial for query performance across multiple tenants)
CREATE INDEX idx_projects_tenant ON projects(tenant_id);

-- Enable RLS for the projects table
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

Step 2: Define the Security Policy

We will configure PostgreSQL to restrict access based on a session configuration variable called app.current_tenant_id.

CREATE POLICY tenant_isolation_policy ON projects
  AS ASYMMETRIC
  USING (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::integer);

Step 3: Accessing Data from the Application Layer

When your backend (e.g., Node.js/Fastify or Nuxt server routes) connects to PostgreSQL, it runs queries inside a transaction, setting the session context variable first.

import { Client } from 'pg';

async function fetchTenantProjects(tenantId: number): Promise<any[]> {
  const client = new Client({ connectionString: process.env.DATABASE_URL });
  await client.connect();

  try {
    // Start transaction
    await client.query('BEGIN');

    // Set the tenant ID context variable for the duration of this transaction
    await client.query(`SET LOCAL app.current_tenant_id = $1`, [tenantId]);

    // Even if we query SELECT * without a WHERE clause, RLS filters the records
    const result = await client.query('SELECT * FROM projects');

    await client.query('COMMIT');
    return result.rows;
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    await client.end();
  }
}

3. Best Practices for Scaling Multi-Tenant Databases

1. Always Index tenant_id: If you don't index the tenant column, every query will perform a sequential table scan, destroying database performance as the table grows.
2. Verify policies in automated tests: Write integration tests that attempt to fetch Tenant B's data using Tenant A's connection context, verifying that the database throws access errors or returns empty results.
3. Prepare for tenant migration: Enterprise customers may eventually ask to move to a dedicated database (Pattern A) for regulatory reasons. Design your schemas so you can easily extract records matching a specific tenant_id and load them into a standalone instance.

For example, in the LingvoHabit language portal, we successfully implemented Row-Level Isolation (Pattern C) using indexed tenant schemas, keeping monthly database fees extremely low while serving thousands of registered learners.

Sources and documentation

• PostgreSQL: Row Security Policies — official RLS documentation
• PostgreSQL: Schemas — logical schemas for Pattern B
• GDPR.eu — isolation and data-portability requirements for personal data

Choosing the right multi-tenant paradigm involves balancing your startup's budget, resources, and regulatory requirements. RLS offers an elegant and secure way to build a cost-effective, scalable B2B SaaS without compromising data safety. To see how this database setup ties into a full development lifecycle, explore my guide on Building B2B SaaS MVPs in 30 Days.

If you are designing the data layer for a multi-tenant application and want an expert architecture review to ensure it handles growth safely, explore my SaaS Development Service or book a Technical Architecture Consultation to get a production plan.