SaaS multi-tenant Postgres: 3 architectures compared (2026)

When your B2B SaaS goes from 5 to 50 to 500 clients, multi-tenant architecture determines 80% of operational cost and security. 3 patterns dominate in 2026: schema-per-tenant, row-level (shared schema), database-per-tenant. Each has its sweet spot.

TL;DR

- Row-level: 1 DB, 1 schema, tenant_id everywhere. Simplest, scales to 10K tenants.

- Schema-per-tenant: 1 DB, N schemas. Strong isolation, complex migrations.

- DB-per-tenant: N DBs. Max isolation, high operational cost.

3-pattern comparison

Pattern 1 — Row-level (recommended 90% cases)

`prisma

model Organization {

id String @id @default(cuid())

name String

plan Plan

users User[]

projects Project[]

}

model Project {

id String @id @default(cuid())

organizationId String

organization Organization @relation(fields: [organizationId], references: [id])

name String

@@index([organizationId])

}

model User {

id String @id @default(cuid())

organizationId String

email String

@@unique([organizationId, email])

}

`

Critical security: every query MUST filter organizationId. Implement via Prisma middleware:

`ts

const SCOPED_MODELS = ['Project', 'User', 'Document', 'Comment'];

export function tenantScopedPrisma(organizationId: string) {

return new PrismaClient().$extends({

query: {

$allModels: {

async $allOperations({ args, model, operation, query }) {

if (!SCOPED_MODELS.includes(model ?? '')) return query(args);

if (operation.startsWith('find') || operation === 'count' || operation === 'aggregate') {

args.where = { ...args.where, organizationId };

}

if (operation === 'updateMany' || operation === 'deleteMany') {

args.where = { ...args.where, organizationId };

}

if (operation === 'create') {

args.data = { ...args.data, organizationId };

}

if (operation === 'createMany') {

args.data = (args.data as any[]).map(d => ({ ...d, organizationId }));

}

return query(args);

},

},

},

});

}

`

Mandatory cross-tenant tests:

`ts

test('User from org A cannot access org B data', async () => {

const orgA = await createOrg();

const orgB = await createOrg();

const projectB = await createProject(orgB.id);

const prismaA = tenantScopedPrisma(orgA.id);

const found = await prismaA.project.findMany({ where: { id: projectB.id } });

expect(found).toEqual([]);

});

`

Pattern 2 — Schema-per-tenant

Each tenant has its dedicated Postgres schema:

`sql

CREATE SCHEMA tenant_acme;

CREATE SCHEMA tenant_globex;

CREATE TABLE tenant_acme.projects (...);

CREATE TABLE tenant_globex.projects (...);

`

`ts

const pool = new Pool({

connectionString: process.env.DATABASE_URL,

searchPath: tenant_${tenantId},

});

const prisma = new PrismaClient({

datasources: { db: { url: ${process.env.DATABASE_URL}?schema=tenant_${tenantId} } },

});

`

For:

• Strong isolation
• Easy individual backup (pg_dump --schema=tenant_acme)
• GDPR: account deletion = DROP SCHEMA

Against:

• Migrations: must run on N schemas
• Limit ~1000-2000 schemas per Postgres DB
• Scaling beyond = multi-DB

Pattern 3 — Database-per-tenant

Each tenant has its dedicated Postgres DB:

`

[Tenant 1] → Postgres DB tenant_1

[Tenant 2] → Postgres DB tenant_2

[Tenant N] → Postgres DB tenant_N

`

For:

• Maximum isolation
• Dedicated performance per tenant
• Compliance (HIPAA, PCI Level 1, government)

Against:

• High operational cost: N backups, N monitoring
• Complex migrations (orchestration needed)
• Not suitable <100 enterprise tenants

When to migrate pattern 1 → 2 or 3?

Recommended hybrid strategy

`

[Shared pool: Standard tenants (row-level)] → main Postgres cluster

[Premium pool: Enterprise tenants (schema-per-tenant)] → dedicated Postgres cluster

[Gov pool: ultra-sensitive tenants (DB-per-tenant)] → isolated Postgres

`

Prisma + connection pooling lets you serve 3 patterns from the same app.

Performance considerations

Tenant-first composite indexes

`prisma

@@index([organizationId, createdAt])

@@index([organizationId, status])

@@index([organizationId, slug])

`

organizationId must always be first in composite indexes.

Connection pooling

For 1000+ tenants: PgBouncer or Neon serverless. Without pooling = connection explosion.

Redis caching

Per-tenant cache: cache:org_${organizationId}:projects:list. Short 5-15 min TTL.

Real case — Dakar SaaS (SME CRM)

Row-level → schema-per-tenant migration at ~1500 tenants. Doable but plan 4-8 weeks.

Common pitfalls

• Forgetting tenant_id filter on 1 query = GDPR + commercial catastrophe.
• No CI cross-tenant tests = guaranteed future bug.
• Migrating too early to schema-per-tenant = premature complexity.
• No per-tenant backup = unrecoverable loss.
• No "noisy neighbor" isolation — 1 spamming tenant slows all others.

FAQ

Q: Mongo or Postgres?

A: Postgres for 95% B2B SaaS. Mongo if truly dynamic schema per tenant.

Q: Multi-region needed?

A: For Africa-EU SaaS: Hetzner Frankfurt enough. US: Neon multi-region available.

Q: Soft-delete vs hard-delete?

A: Soft-delete (deletedAt) for audit + GDPR (easy rectification). Hard-delete on GDPR request.

Conclusion

Multi-tenant Postgres in 2026 = row-level for 95% of B2B SaaS, schema-per-tenant for regulated cases, DB-per-tenant for gov/banks. 2-4 weeks initial investment for clean isolation. ROI: security and scaling to 10K+ tenants.