Skip to main content

Multi-Tenant SaaS: Technical Q&A

Q1: Which architecture pattern should I start with?​

Answer: Start with Single Database, Shared Schema

Why?

  • Simplest to build and understand
  • Lowest cost to run
  • Easiest to scale at first
  • You can change later when needed

Implementation:

CREATE TABLE users (
id UUID PRIMARY KEY,
tenant_id UUID NOT NULL,
email VARCHAR(255)
);

-- Every query filters by tenant_id
SELECT * FROM users
WHERE tenant_id = 'abc-123' AND email = 'user@example.com';

When to change:

  • Enterprise customers demanding isolation
  • Strict compliance requirements (HIPAA, strict GDPR)
  • Large tenants (50GB+ data each)

The upgrade path:

Phase 1: Single DB + Shared Schema <- START HERE
↓
Phase 2: Single DB + Separate Schemas (if needed)
↓
Phase 3: Separate DB per Tenant (only for enterprise)

Q2: How do I implement row-level security in PostgreSQL?​

Answer: Two approaches - RLS or application-level filtering

Approach 1: PostgreSQL Row-Level Security (Built-in)​

-- Step 1: Enable RLS
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- Step 2: Create isolation policy
CREATE POLICY tenant_isolation ON projects
USING (tenant_id = current_setting('app.current_tenant', true));
// Set tenant context in middleware
app.use((req, res, next) => {
const tenantId = req.user.tenantId;
db.query(`SET LOCAL app.current_tenant = $1`, [tenantId]);
next();
});

Pros:

  • Database enforces it (cannot be bypassed)
  • Centralized security

Cons:

  • Slightly slower queries
  • More complex debugging

Approach 2: Application-Level Filtering​

// Create a helper function
function tenantQuery(table, conditions = {}) {
return db(table)
.where({ tenant_id: currentTenant.id })
.andWhere(conditions);
}

const projects = await tenantQuery('projects', { active: true });

My recommendation: Start with application-level, add RLS later.

Q3: What about multi-tenant authentication strategies?​

Pattern 1: Domain-Based (tenant1.yourapp.com)​

app.use((req, res, next) => {
const host = req.headers.host;
const subdomain = host.split('.')[0];
req.tenant = subdomain;
next();
});

app.post('/login', async (req, res) => {
const { email, password } = req.body;
const tenantId = req.tenant;

const user = await db('users')
.where({ email, tenant_id: tenantId })
.first();

if (!user || !verifyPassword(password, user.password)) {
return res.status(401).json({ error: 'Invalid' });
}

req.session.userId = user.id;
req.session.tenantId = tenantId;
res.json({ success: true });
});

Best for: Public SaaS applications

Pattern 2: JWT with Tenant ID​

app.post('/login', async (req, res) => {
const { email, password, tenantId } = req.body;

const user = await db('users')
.where({ email, tenant_id: tenantId })
.first();

if (!user || !verifyPassword(password, user.password)) {
return res.status(401).json({ error: 'Invalid' });
}

const token = jwt.sign(
{ userId: user.id, tenantId: user.tenant_id },
process.env.JWT_SECRET,
{ expiresIn: '24h' }
);

res.json({ token });
});

Best for: API-first applications, mobile apps

Q4: How do I handle tenant-specific customizations?​

Level 1: Simple Settings​

CREATE TABLE tenant_settings (
tenant_id UUID PRIMARY KEY,
theme VARCHAR(50) DEFAULT 'light',
logo_url VARCHAR(500),
features JSONB
);
const settings = await db('tenant_settings')
.where({ tenant_id: tenantId })
.first();

if (settings.features.chat) {
// Show chat feature
}

Level 2: Per-Tenant Data Structure​

CREATE TABLE tenant_custom_data (
id UUID PRIMARY KEY,
tenant_id UUID,
profile_data JSONB
);

Level 3: White-Label​

public/tenants/
├── acme/logo.png
├── acme/styles.css
└── widgetco/logo.png
app.use('/tenant-assets', (req, res, next) => {
req.url = `/${req.user.tenantId}${req.url}`;
next();
}, express.static('public/tenants'));

Q5: What are the best practices for backing up multi-tenant data?​

Shared Schema: Point-in-Time Recovery​

# postgresql.conf:
archive_mode = on
archive_command = 'cp %p /backup/wal/%f'

Per-Tenant Extraction​

pg_dump saas_db \
--data-only \
--where="tenant_id = 'abc-123'" \
> tenant_backup.sql

Separate Database: Simple​

pg_dump tenant_abc_db > backup.sql

Best Practices​

  1. Test restores regularly
  2. 3-2-1 rule: 3 copies, 2 media, 1 offsite
  3. Automate everything
  4. Encrypt sensitive backups
  5. Document recovery procedure

Q6: How do I migrate from single-tenant to multi-tenant?​

Phase 1: Database Prep​

ALTER TABLE users ADD COLUMN tenant_id UUID;
ALTER TABLE projects ADD COLUMN tenant_id UUID;

CREATE INDEX idx_users_tenant ON users(tenant_id);
CREATE INDEX idx_projects_tenant ON projects(tenant_id);

UPDATE users SET tenant_id = 'your-tenant-id';
UPDATE projects SET tenant_id = 'your-tenant-id';

ALTER TABLE users ALTER COLUMN tenant_id SET NOT NULL;

Phase 2: Application Changes​

app.use((req, res, next) => {
req.tenantId = extractTenantId(req);
next();
});

// BEFORE:
app.get('/projects', async (req, res) => {
const projects = await db('projects'); // NO FILTER!
res.json(projects);
});

// AFTER:
app.get('/projects', async (req, res) => {
const projects = await db('projects')
.where({ tenant_id: req.tenantId }); // FILTER!
res.json(projects);
});

Phase 3: Add Row-Level Security​

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON projects
USING (tenant_id = current_setting('app.current_tenant'));

Phase 4: Testing​

it('Prevents cross-tenant data access', async () => {
const projectB = await createProject('B', tenantB.id);

const response = await request(app)
.get(`/projects/${projectB.id}`)
.set('Authorization', `Bearer ${tenantAToken}`);

expect(response.status).toBe(403);
});

Q7: What about rate limiting and scaling?​

Rate Limiting (Per Tenant)​

const Redis = require('ioredis');
const redis = new Redis();

async function checkRateLimit(tenantId, maxRequests, windowMs) {
const key = `rate_limit:${tenantId}`;
const current = await redis.get(key);

if (current === null) {
await redis.setex(key, windowMs / 1000, 1);
return { allowed: true, remaining: maxRequests - 1 };
}

if (parseInt(current) >= maxRequests) {
return { allowed: false, remaining: 0 };
}

await redis.incr(key);
return { allowed: true, remaining: maxRequests - parseInt(current) - 1 };
}

app.use(async (req, res, next) => {
const { allowed, remaining } = await checkRateLimit(
req.user.tenantId,
1000, // 1000 requests
60000 // per minute
);

res.set('X-RateLimit-Remaining', remaining);

if (!allowed) {
return res.status(429).json({ error: 'Rate limit exceeded' });
}
next();
});

Horizontal Scaling​

Load Balancer
↓
[Server 1] [Server 2] [Server 3]
↓ ↓ ↓
[Redis Cache] (shared)
↓
[PostgreSQL]
// Stateless servers with Redis sessions
app.use(session({
store: new RedisStore({ client: redis }),
secret: process.env.SESSION_SECRET
}));

Database Read Replicas​

const primaryPool = new Pool({ connectionString: PRIMARY_DB_URL });
const replicaPool = new Pool({ connectionString: REPLICA_DB_URL });

// Writes to primary
await primaryPool.query('INSERT INTO projects...');

// Reads from replicas
const projects = await replicaPool.query(
'SELECT * FROM projects WHERE tenant_id = $1',
[tenantId]
);

Connection Pooling​

const pool = new Pool({
max: 20, // Max 20 connections
idleTimeoutMillis: 30000,
connectionTimeoutMillis: 2000
});

Caching Layer​

function cache(key, ttlSeconds, fetchFn) {
const cacheKey = `tenant:${tenantId}:${key}`;

const cached = await redis.get(cacheKey);
if (cached) return JSON.parse(cached);

const data = await fetchFn();
await redis.setex(cacheKey, ttlSeconds, JSON.stringify(data));
return data;
}

const projects = await cache('projects', 300, () =>
db('projects').where({ tenant_id: tenantId })
);

Queue Workers Example​

const Queue = require('bull');
const emailQueue = new Queue('email', { redis: { port: 6379 } });

await emailQueue.add('send-welcome', { userId, tenantId });

emailQueue.process(async (job) => {
const { userId, tenantId } = job.data;

const tenant = await db('tenants').where({ id: tenantId }).first();
if (!tenant) throw new Error('Invalid tenant');

await sendWelcomeEmail(userId);
});

Q8: How do you handle tenant onboarding and provisioning?​

Answer: A tenant lifecycle: signup → provision → activate → deactivate

Key steps:

  1. Create tenant record in tenants table
  2. Provision resources per isolation model (schema/DB per tenant)
  3. Create admin user + default roles
  4. Run tenant-specific migrations/settings
  5. Activate + notify

Implementation (queue-based provisioning):

// Signup endpoint enqueues provisioning
await provisioningQueue.add('provision', { tenantId, plan });

// Worker provisions asynchronously
provisioningQueue.process(async (job) => {
const { tenantId, plan } = job.data;
await createTenantSchema(tenantId); // or DB
await createAdminUser(tenantId);
await seedDefaultSettings(tenantId, plan);
await db('tenants').update({ status: 'active' }).where({ id: tenantId });
});

Why queue-based? Provisioning is slow (schema creation, seeding). Never block signup on it — return 202 Accepted, provision in background, email when ready.

Q9: What is the "noisy neighbor" problem and how do you fix it?​

Answer: One tenant consuming disproportionate CPU/IO/memory degrades other tenants on shared infrastructure.

Fixes:

  • Per-tenant rate limits (requests/sec, workers, concurrency)
  • Resource quotas (PostgreSQL: ALTER ROLE ... SET statement_timeout, max connections)
  • Read replicas / separate DBs for heavy tenants (isolation upgrade path)
  • Per-tenant queues (separate job queues per tenant or weighted priorities)
  • Monitoring: tenant-level CPU, query time, row counts — alert on outliers
-- Example: per-tenant query timeout
ALTER ROLE tenant_app SET statement_timeout = '5s';

The key: instrument everything by tenant_id so you can detect and quarantine noise early.

Q10: How do you test multi-tenancy isolation?​

Answer: Automated cross-tenant access tests + audit queries

Test levels:

  1. Unit: tenant filter helper applied everywhere (static analysis / test sweep)
  2. Integration: tenant A cannot read/write tenant B data (403/404, not data leak)
  3. E2E: two tenants in parallel flows, assert no bleed
  4. Adversarial: tamper with tenant_id in URL/body/token — must be ignored
  5. SQL audit: no query missing tenant_id filter (query interception / code review)
it('Blocks cross-tenant write', async () => {
const projectB = await createProject('secret', tenantB.id);
const res = await request(app)
.patch(`/projects/${projectB.id}`)
.set('Authorization', `Bearer ${tenantAToken}`)
.send({ name: 'hacked' });
expect(res.status).toBe(404); // 404, not 403: don't leak existence
});

Rule: return 404 for resources you don't own (403 reveals their existence).

Q11: How do you design a tenant-aware cache?​

Answer: Cache keys must include tenant_id; never share cache entries across tenants

function cacheKey(tenantId, key) {
return `t:${tenantId}:${key}`;
}

// Per-tenant TTL and quota
const entry = await redis.get(cacheKey(tenantId, 'settings'));
if (!entry) {
const data = await db('tenant_settings').where({ tenant_id: tenantId }).first();
await redis.setex(cacheKey(tenantId, 'settings'), 300, JSON.stringify(data));
}

Rules:

  • Tenant ID first segment of every key
  • TTL per tenant (paying tenants may get longer TTLs)
  • Invalidate per tenant on their data change (never global flush)
  • Sensitive tenants: no shared cache, or encryption at cache layer
  • PII must not cross tenant boundaries even in cache

Q12: How do you handle tenant data deletion (GDPR right-to-be-forgotten)?​

Answer: Soft delete + scheduled purge, with per-tenant scope

-- Soft delete: mark tenant
UPDATE tenants SET status = 'deleting', deleted_at = NOW() WHERE id = $1;

-- Scheduled job purges per-tenant rows (respecting FK order)
DELETE FROM audit_logs WHERE tenant_id = $1;
DELETE FROM projects WHERE tenant_id = $1;
DELETE FROM users WHERE tenant_id = $1;
DELETE FROM tenants WHERE id = $1;

Process:

  1. Disable login + revoke tokens
  2. Queue purge job (idempotent, resumable)
  3. Purge in dependency order; batch to avoid long locks
  4. Verify purge (row counts per table == 0)
  5. Remove backups containing tenant data per retention policy

Why queue? Deletion of large tenants can take minutes — never synchronously in a request.

Q13: How do you implement per-tenant encryption keys?​

Answer: Envelope encryption: data key per tenant, wrapped by master key

Master Key (KMS)
└── wraps ──> Tenant Data Key (per tenant, stored)
└── encrypts ──> tenant data
// On tenant creation
const masterKeyId = process.env.KMS_MASTER_KEY;
const { CiphertextBlob, Plaintext } = await kms.generateDataKey({
KeyId: masterKeyId, KeySpec: 'AES_256'
});
await db('tenants').update({
data_key_ciphertext: CiphertextBlob.toString('base64')
}).where({ id: tenantId });
// Plaintext only lives in memory during operation

// On data access: decrypt data key, encrypt/decrypt tenant rows
const dataKey = await kms.decrypt({ CiphertextBlob: Buffer.from(tenant.data_key_ciphertext, 'base64') });

When needed: regulated industries (healthcare, fintech), or customers requiring cryptographic tenant separation.

Q14: What is the difference between pooled vs isolated tenancy in SaaS?​

Answer:

Pooled (Shared)Isolated (Dedicated)
DataShared DB/schema with tenant_idSeparate DB/schema per tenant
CostLowest (economies of scale)Highest
IsolationLogical only (must enforce in code)Physical (hard boundary)
ComplianceHarder to proveEasier to certify
CustomizationLimited (shared schema)Full (per-tenant schema)
Upgrade riskShared migrations affect allPer-tenant migration control

Hybrid (most common in production): pool small/medium tenants, isolate enterprise/high-compliance tenants. Route by plan tier at provisioning time.

Q15: How do you measure and monitor multi-tenant health?​

Answer: Tenant-level metrics are mandatory — average hides outliers

Dashboards/alerts:

  • Per-tenant: request count, error rate, p95 latency, queue depth, DB connections, query time
  • Global: total throughput, error budget, cost per tenant
  • Alerts: per-tenant error spike, per-tenant latency breach, tenant hitting rate limit, signup funnel failures
// Tag all metrics with tenant_id
const { meter } = require('@opentelemetry/api');
const tenantRequests = meter.createCounter('tenant.requests', {
description: 'Requests per tenant'
});
tenantRequests.add(1, { tenant_id: tenantId, route: req.path });

Why: an outlier tenant degrading the platform is invisible in averages. Per-tenant dashboards let you detect noisy neighbors, abuse, and capacity planning per tier.