Multi-Tenant Architecture in SaaS: Isolation, Scale and Real Trade-offs

TL;DR

• There are 3 main patterns: database per tenant, schema per tenant and partitioned tables. None is "correct"; the choice depends on the number of tenants, data volume and engineering resources.
• Isolation at schema level (tenant_id in the key) is more common in small to medium B2B SaaS because it allows initial scalability with less operational complexity than database-per-tenant.
• Table partitioning by tenant in PostgreSQL 16+ offers logical isolation with less operational overhead than multiple databases, but requires expertise in Row Level Security or rigorous application-level policies.
• The biggest pitfall is NOT_FOUND queries that return data from multiple tenants due to missing mandatory tenant_id filter in the application. This breaks isolation completely.
• Total cost of ownership varies: database-per-tenant (€500-2000/month per managed DB) vs shared schema (€50-200/month base + indices) depends on the platform.

Why Data Organisation Matters in Multi-Tenant

When you build a SaaS, the first temptation is to throw everything into a database and let application logic manage isolation with WHERE tenant_id = ? filters. It works. Until it doesn't.

Many SaaS platforms face serious production issues because data architecture wasn't thought through from the start. Slow queries under multiple tenant load, complex data migrations, inability to do selective backups, fragile compliance. Data organisation isn't just a technical detail; it's an architectural decision that affects cost, security and development velocity for years to come.

Let me be direct: there's no "perfect solution". Each pattern has real trade-offs. The goal here is to show you the options with concrete numbers and criteria for choosing.

Pattern 1: Partitioned Tables with Tenant_ID (Shared Schema)

This is the most common pattern in small to medium B2B SaaS. A single database, one schema, all tables with a tenant_id column. Isolation is the responsibility of the application.

Practical example in PostgreSQL 16:

CREATE TABLE organizations (
    id UUID PRIMARY KEY,
    tenant_id UUID NOT NULL,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    UNIQUE(tenant_id, id)
);

CREATE TABLE workspace_members (
    id UUID PRIMARY KEY,
    tenant_id UUID NOT NULL,
    organization_id UUID NOT NULL,
    user_id UUID NOT NULL,
    role VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    CONSTRAINT fk_org FOREIGN KEY (tenant_id, organization_id) 
        REFERENCES organizations(tenant_id, id),
    INDEX idx_tenant_org (tenant_id, organization_id),
    INDEX idx_tenant_user (tenant_id, user_id)
);

-- Partition by tenant for faster queries on large volumes
CREATE TABLE workspace_events (
    id UUID NOT NULL,
    tenant_id UUID NOT NULL,
    workspace_id UUID NOT NULL,
    event_type VARCHAR(100),
    payload JSONB,
    created_at TIMESTAMP DEFAULT NOW()
) PARTITION BY HASH (tenant_id) PARTITIONS 16;

-- RLS policies for automatic isolation (PostgreSQL 12+)
ALTER TABLE organizations ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_organizations ON organizations
    USING (tenant_id = CURRENT_SETTING('app.current_tenant_id')::uuid);

CREATE POLICY tenant_isolation_members ON workspace_members
    USING (tenant_id = CURRENT_SETTING('app.current_tenant_id')::uuid);

Pros:

• Operationally simple. One database, one backup, one set of indices.
• Multi-tenant queries are trivial (SELECT * WHERE tenant_id = ?).
• Low initial cost. On Render, Heroku or Supabase, you can have 100+ tenants in a single instance for €50-150/month.
• Simple migrations; you change one table, it affects all tenants uniformly.

Cons:

• Complexity grows with volume. With 10,000 active tenants, even with partitioning, contention on shared indices starts to hurt. P99 latency rises.
• Backups and restores affect all tenants. An error in one customer's data might require a full restore.
• Vertical scaling has limits. The database grows to a single server (unless you use read replication).
• Compliance is complex. GDPR delete-right for one tenant affects referential integrity of others.
• Row Level Security isn't "magic". It requires rigorous discipline: every query MUST pass app.current_tenant_id. A query without tenant_id filtering returns everything from everyone.

When to choose: SaaS with < 1000 active tenants, data volume < 100GB per tenant, team with solid SQL and DevOps knowledge.

Pattern 2: Database-Per-Tenant

Each tenant has their own PostgreSQL database (or MongoDB, Mongo, etc.). Isolation is guaranteed at infrastructure level.

Example setup in TypeScript with Prisma:

// db.ts
import { PrismaClient } from '@prisma/client';

const tenantConnections: Record<string, PrismaClient> = {};

export async function getPrismaForTenant(tenantId: string): Promise<PrismaClient> {
  if (tenantConnections[tenantId]) {
    return tenantConnections[tenantId];
  }

  // Dynamic connection string per tenant
  const databaseUrl = `postgresql://user:pass@postgres-${tenantId}.prod.rds.amazonaws.com:5432/db_${tenantId}`;
  
  const prisma = new PrismaClient({
    datasources: {
      db: {
        url: databaseUrl,
      },
    },
  });

  tenantConnections[tenantId] = prisma;
  return prisma;
}

// api/workspaces/[tenantId].ts
import { getPrismaForTenant } from '@/db';

export async function getWorkspace(tenantId: string, workspaceId: string) {
  const prisma = await getPrismaForTenant(tenantId);
  
  // No tenant_id filter needed; it's implicit in the DB
  return prisma.workspace.findUnique({
    where: { id: workspaceId },
  });
}

Pros:

• Isolation guaranteed. A query that fails in one tenant doesn't affect others.
• Selective backups and fast restore. Each tenant is independent.
• Automatic compliance. GDPR delete is trivial (drop database).
• Infinite horizontal scale. Each tenant can have replicas, failover, dedicated resources.
• Predictable performance. No contention with other tenants.

Cons:

• High operational cost. AWS RDS, Heroku, or Neon charge per database. Minimum €15-50/month per DB. With 500 tenants, you're at €7500-25000/month.
• Deployment complexity. Each new tenant requires automatic DB provisioning, migrations, dedicated backups.
• Shared queries are complex. "Show me aggregated data from all my tenants" requires federated queries or data copied to a data warehouse.
• Connection pooling is critical. 500 tenants * 10 connections = 5000 open connections. PgBouncer or similar is mandatory.
• DevOps tools need multi-database awareness. Monitoring, alerts, logs must scale.

When to choose: SaaS with > 1000 high-value tenants, strict compliance requirements, or when most tenants have massive data volumes (> 500GB).

Pattern 3: Schema-Per-Tenant (Namespace Isolation)

Each tenant has their own PostgreSQL schema within the same instance. Isolation at schema level, not database level.

-- Setup
CREATE SCHEMA tenant_acme_001 AUTHORIZATION postgres;
CREATE SCHEMA tenant_xyz_002 AUTHORIZATION postgres;

-- Table definitions are the same, but repeated per schema
CREATE TABLE tenant_acme_001.organizations (
    id UUID PRIMARY KEY,
    name VARCHAR(255),
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE tenant_xyz_002.organizations (
    id UUID PRIMARY KEY,
    name VARCHAR(255),
    created_at TIMESTAMP DEFAULT NOW()
);

-- Search_path selects the active tenant's schema
SET search_path TO tenant_acme_001, public;
SELECT * FROM organizations; -- Accesses only tenant_acme_001.organizations

Middleware in Node.js/Express for automatic switching:

// Middleware to select schema
export function schemaSwitcher(req: Request, res: Response, next: NextFunction) {
  const tenantId = req.headers['x-tenant-id'] as string || req.user?.tenantId;
  
  if (!tenantId) {
    return res.status(401).json({ error: 'Missing tenant ID' });
  }

  // Execute SET search_path for this request
  const schemaName = `tenant_${tenantId.replace(/-/g, '_')}`;
  
  // Pass to context (Express, Fastify, etc.)
  req.locals = { schemaName, tenantId };
  
  next();
}

// Apply to queries
app.get('/api/organizations', schemaSwitcher, async (req, res) => {
  const { schemaName } = req.locals;
  
  const query = `
    SET search_path TO ${schemaName}, public;
    SELECT * FROM organizations LIMIT 10;
  `;
  
  const result = await db.query(query);
  res.json(result.rows);
});

Pros:

• Stronger isolation than partitioned tables, simpler than database-per-tenant.
• Selective backups per schema. pg_dump -n tenant_acme_001.
• Scales better than shared schema on large volumes because indices are separate per schema.
• Moderate operational cost. One DB for N tenants, but with less overhead than shared schema on large volumes.

Cons:

• Still operationally complex. Each new tenant requires CREATE SCHEMA + permission setup.
• Aggregated queries remain problematic. Crossing data from multiple schemas is slow.
• Error in search_path and you have data leakage. A query written wrong without SET search_path can return the wrong data.
• ORM tools (Prisma, SQLAlchemy) don't have native support for dynamic schema-switching. You need raw queries.

When to choose: SaaS with 100-1000 medium-value tenants, 50-500GB data volume per tenant, when database-per-tenant is too expensive but shared schema is risky.

Common Production Pitfalls

1. Queries Without Tenant_ID Filter

The most serious. A developer writes a query without WHERE tenant_id = ?. If the application uses shared schema, this returns data from ALL tenants.

// WRONG
async function getUserProjects(userId: string) {
  return prisma.project.findMany({
    where: { userId },
  });
}

// If two tenants have users with repeated IDs (e.g., auto-increment),
// this userId might exist in both. The query returns both.

// CORRECT
async function getUserProjects(userId: string, tenantId: string) {
  return prisma.project.findMany({
    where: { 
      userId,
      tenantId, // Always mandatory
    },
  });
}

2. Migrations That Break Isolation

A migration that adds a column with a unique default breaks things. Example:

-- WRONG in shared schema
ALTER TABLE api_keys ADD COLUMN secret_key VARCHAR(255) UNIQUE;

-- Two inserts with DEFAULT will create a UNIQUE conflict between tenants
INSERT INTO api_keys (tenant_id, name) VALUES ('tenant-1', 'key1');
INSERT INTO api_keys (tenant_id, name) VALUES ('tenant-2', 'key1');
-- Error: duplicate key

3. N+1 Queries Multiplied by Tenants

With database-per-tenant, each query opens a connection. N+1 in shared schema is slow. N+1 in database-per-tenant is catastrophic (hundreds of connections).

// WRONG in database-per-tenant
const workspaces = await getWorkspaces(tenantId); // 1 connection
for (const ws of workspaces) {
  const members = await getMembers(tenantId, ws.id); // +1 connection per workspace
}
// With 100 workspaces: 101 connections open

// CORRECT: Batch queries
const workspacesWithMembers = await prisma.workspace.findMany({
  where: { tenantId },
  include: { members: true }, // 1 query with JOIN
});

4. Incomplete Backups

With database-per-tenant, forgetting to backup a schema or database leaves a tenant vulnerable. Without automation, it's a disaster.

Practical Recommendation: Start with Shared Schema, Prepare for Scale

Most SaaS should start with shared schema (pattern 1) because:

• Operationally simple. Focus on product, not DevOps.
• Low initial cost.
• Fast migrations and deployments.

But implement from the start:

• Column tenant_id in EVERY data table. No exceptions.
• Row Level Security (PostgreSQL) or mandatory filter in application layer.
• Indices always include tenant_id as first column: INDEX idx_user_tenant (tenant_id, user_id).
• Tests that verify isolation. Query factory that always passes tenantId.

When you have 500+ active tenants and P99 latency rises above 200ms, consider schema-per-tenant. When you have enterprise customers with strict isolation requirements or compliance, migrate to database-per-tenant.

Tools That Help

PostgreSQL 16+: HASH partitioning, RLS, parameterized queries. Everything supports multi-tenant natively.

Prisma: Middleware to automatically pass tenantId to all queries. const result = prisma.$queryRaw\SELECT * FROM users`is dangerous; useprisma.user.findMany({ where: { tenantId } })`.

PgBouncer: Connection pooling essential in database-per-tenant or schema-per-tenant with many tenants.

Neon or Vercel Postgres: Managed PostgreSQL with automatic scaling. Supports branch per tenant for testing.

Conclusion

Multi-tenant data organisation in SaaS is not a minor technical decision. It determines operational cost, security, compliance and scaling velocity for years. Start simple (shared schema with rigorous filters), invest in isolation tests from day 1, and scale to schema-per-tenant or database-per-tenant as the number of tenants and data volume demand.

If you're facing a similar problem, book a conversation at https://impact-origin.com/agendamento.