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
- Test restores regularly
- 3-2-1 rule: 3 copies, 2 media, 1 offsite
- Automate everything
- Encrypt sensitive backups
- 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:
- Create tenant record in
tenantstable - Provision resources per isolation model (schema/DB per tenant)
- Create admin user + default roles
- Run tenant-specific migrations/settings
- 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:
- Unit: tenant filter helper applied everywhere (static analysis / test sweep)
- Integration: tenant A cannot read/write tenant B data (403/404, not data leak)
- E2E: two tenants in parallel flows, assert no bleed
- Adversarial: tamper with tenant_id in URL/body/token — must be ignored
- SQL audit: no query missing
tenant_idfilter (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:
- Disable login + revoke tokens
- Queue purge job (idempotent, resumable)
- Purge in dependency order; batch to avoid long locks
- Verify purge (row counts per table == 0)
- 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) | |
|---|---|---|
| Data | Shared DB/schema with tenant_id | Separate DB/schema per tenant |
| Cost | Lowest (economies of scale) | Highest |
| Isolation | Logical only (must enforce in code) | Physical (hard boundary) |
| Compliance | Harder to prove | Easier to certify |
| Customization | Limited (shared schema) | Full (per-tenant schema) |
| Upgrade risk | Shared migrations affect all | Per-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.