---
id: db-multi-tenant-deep
title: Multi-Tenant DB — pool / schema / DB / row
category: Coding
status: draft
source_trust_level: B
verification_status: conceptual
created_at: 2026-05-09
updated_at: 2026-05-09
tags: [database, multi-tenant, vibe-coding]
tech_stack: { language: "SQL", applicable_to: ["Backend", "Database"] }
applied_in: []
aliases: [multi-tenant, tenant isolation, RLS, row-level security, schema per tenant, DB per tenant]
---

# Multi-Tenant DB

> 1 app + N customer (tenant). **Pool / schema / DB / row 4 가지 isolation level**. Row-level security (RLS) 가 modern Postgres.

## 📖 핵심 개념
- Pool: 1 schema, 1 DB. Tenant column.
- Schema: 매 tenant 의 schema.
- DB: 매 tenant 의 DB.
- Hybrid: 큰 tenant 가 own DB.

## 💻 코드 패턴

### Pool model (가장 common)
```sql
CREATE TABLE users (
    id UUID PRIMARY KEY,
    tenant_id UUID NOT NULL,
    email TEXT
);

CREATE INDEX ON users (tenant_id);

-- 매 query 가 tenant_id filter.
SELECT * FROM users WHERE tenant_id = $1;
```

→ Cheap. 매 query 가 tenant filter 잊으면 leak.

### Row-Level Security (Postgres)
```sql
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON users
USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- App 가 connection 별 set.
SET app.tenant_id = '...';

-- 자동 filter.
SELECT * FROM users;   -- 만 tenant 의 row.
```

→ DB-level enforce. App bug 가 leak X.

### RLS 의 setup (TS)
```ts
async function withTenant<T>(tenantId: string, fn: () => Promise<T>): Promise<T> {
  const client = await pool.connect();
  try {
    await client.query(`SET app.tenant_id = $1`, [tenantId]);
    return await fn();
  } finally {
    await client.query(`RESET app.tenant_id`);
    client.release();
  }
}
```

### Schema per tenant
```sql
CREATE SCHEMA tenant_alice;
CREATE TABLE tenant_alice.users (...);

CREATE SCHEMA tenant_bob;
CREATE TABLE tenant_bob.users (...);

-- App 가 schema 선택
SET search_path = tenant_alice;
SELECT * FROM users;
```

→ Strong isolation. 1000+ tenant 가 schema 폭발.

### DB per tenant
```ts
const tenantDBs = new Map<string, Pool>();

function getDB(tenantId: string) {
  if (!tenantDBs.has(tenantId)) {
    tenantDBs.set(tenantId, new Pool({ database: `tenant_${tenantId}` }));
  }
  return tenantDBs.get(tenantId)!;
}
```

→ Strongest. 매우 expensive (1000 DB = 1000 connection pool).

### Hybrid (cell-based)
```
Small tenant: pool.
Big tenant (>10% load): own schema.
Enterprise: own DB.
```

→ Cost vs isolation balance.

→ [[Arch_Cell_Based]].

### Migration 의 complexity
```
Pool: 1 migration = 모두.
Schema: 매 schema 가 migrate.
DB: 매 DB 가 migrate (slow).
```

→ 큰 schema/DB 가 N+1 migration cost.

### Connection pool
```
Pool model: 1 pool, 1000 tenant 공유.
Schema: 1 pool 가 SET search_path.
DB: 1000 pool (PgBouncer 가 도움).

→ DB per tenant 가 connection 폭발.
```

### Cost
```
Pool: 1 DB instance ($).
Schema: 1 DB ($) — 큰 tenant 만 영향.
DB: N DB ($$$).

→ Pool 가 가장 cheap.
DB 가 가장 expensive.
```

### Backup / restore
```
Pool: 모두 또는 일부 (matrix backup).
Schema: 매 schema 의 dump.
DB: 매 DB 의 dump.

→ Tenant 별 restore = schema/DB 가 simple.
Pool 가 어려움 (row 별 select + delete).
```

### Query performance
```
Pool:
- 매 query 가 (tenant_id, ...) index.
- 1 tenant 의 큰 query 가 다른 tenant 영향 (noisy neighbor).

Schema:
- 격리 강.
- Optimizer 가 schema 별 plan.

DB:
- 완전 격리.
- 큰 tenant 가 own resource.
```

### Data export (tenant 가 leave)
```
Pool: row 별 export. Slow.
Schema: pg_dump schema.
DB: pg_dump database.

→ Schema/DB 가 simple.
```

### Encryption (per-tenant key)
```sql
-- pgcrypto + RLS
INSERT INTO sensitive (tenant_id, encrypted)
VALUES ($1, pgp_sym_encrypt($2, $3));   -- $3 = tenant key

SELECT pgp_sym_decrypt(encrypted, $key) FROM sensitive WHERE tenant_id = $1;
```

→ Tenant 별 key 가 GDPR / HIPAA 친화.

### Custom domain
```
tenant1.app.com → tenant1.
tenant2.app.com → tenant2.

App 의 middleware 가 host → tenant.
```

```ts
app.use((req, res, next) => {
  const tenant = subdomain(req.host);
  req.tenant = tenant;
  next();
});
```

### B2B vs B2C
```
B2B (Slack, Notion):
- 큰 tenant.
- Schema / DB 가 흔한.
- Cell-based.

B2C (Spotify):
- 매 user 가 자체 = pool.
- Tenant ≈ user.
```

### Citus (Postgres extension)
```sql
SELECT create_distributed_table('users', 'tenant_id');
-- → tenant_id hash 가 shard.
```

→ Multi-tenant Postgres at scale.

### Auth + tenant
```
JWT 의 tenant_id claim.
- Token 가져옴 → tenant 식별.
- RLS 가 query 자동 filter.
```

### GDPR / data residency
```
EU tenant = EU DB.
US tenant = US DB.

→ DB per tenant + region 별.
```

### Migration from pool → schema
```
1. Backup pool DB.
2. 매 tenant 의 row → schema.
3. Update app code.
4. Cutover (1 시점).
```

→ 큰 cost. Plan 신중.

### Common 함정
```
- Pool + tenant_id forget: leak.
- RLS 없이 pool: 누설 가능.
- Schema 1000+: pg_class bloat.
- DB 1000+: connection 폭발.
- Migration 가 매 tenant: slow + drift.
- Backup 가 tenant 별 X: GDPR 위반.
```

### Monitoring
```
- Per-tenant query count / latency.
- Per-tenant storage.
- Per-tenant cost (chargeback).
- Noisy neighbor 검출.
```

### Noisy neighbor 방지
```
Pool model:
- Query timeout.
- Per-tenant rate limit.
- 큰 tenant 의 isolated workload (read replica).

Schema/DB:
- 자체 isolated.
```

### Real-world
- **Slack**: workspace = schema (cell-based).
- **Notion**: workspace = schema.
- **Salesforce**: 큰 multi-tenant pool.
- **Atlassian**: Jira = pool, Bitbucket = pool.
- **HubSpot**: pool + cell.

### Sharding (관련)
```
Sharding: 큰 tenant = 다른 shard.
Multi-tenant: 작은 tenant = 같은 schema.

→ Sharding + multi-tenant 결합 흔함.
```

→ [[DB_Sharding_Strategies]].

## 🤔 의사결정 기준
| 상황 | 추천 |
|---|---|
| B2C / 1M user | Pool + RLS |
| B2B / 1k tenant | Schema |
| Enterprise tenant | DB |
| Hybrid | Cell-based |
| GDPR / region | DB per region |
| 큰 schema variation | Schema |
| 작은 / simple | Pool |

## ❌ 안티패턴
- **Pool 가 RLS 없음**: leak 가능.
- **Schema 1000+**: pg_class bloat.
- **DB 1000+ + 1 pool 로 connect**: pool 폭발.
- **Migration 매 tenant manual**: drift.
- **Noisy neighbor 무시**: 1 tenant 가 다른 영향.
- **Backup 가 모두 1 file**: tenant 별 restore 어려움.
- **No per-tenant monitoring**: cost / SLA 측정 X.

## 🤖 LLM 활용 힌트
- Pool + RLS (Postgres) 가 가장 modern.
- Schema 가 sweet spot (B2B).
- DB per tenant 가 enterprise.
- Hybrid (cell-based) 가 큰 system.

## 🔗 관련 문서
- [[Backend_Multi_Tenant_Architecture]]
- [[Arch_Cell_Based]]
- [[DB_Sharding_Strategies]]
