02. Multi-Tenant Design

Chapter 2 of 24 · 15 min

KEY INSIGHT

Multi-tenant data models trade off between isolation, performance, and operational complexity—choose row-level isolation for flexibility, schema separation for compliance-heavy use cases. Multi-tenancy means multiple customers share infrastructure while believing they have dedicated resources. The degree of sharing determines the complexity. Three common patterns exist: shared database with tenant discriminator column, separate schemas per tenant, and completely separate databases. Row-level tenancy uses a `tenant_id` column on every table. This approach maximizes resource utilization—one database instance serves thousands of customers. Query performance depends on proper indexing. Without composite indexes on `(tenant_id, target_column)`, queries become sequential scans. ```python from sqlalchemy import Column, String, Integer, ForeignKey, Index from sqlalchemy.orm import declarative_base Base = declarative_base() class UsageLog(Base): __tablename__ = "usage_logs" id = Column(Integer, primary_key=True) tenant_id = Column(String(36), nullable=False, index=True) model_name = Column(String(100), nullable=False) tokens_used = Column(Integer, nullable=False) cost_kobo = Column(Integer, nullable=False) # Kobo for Naira precision created_at = Column(DateTime, default=datetime.utcnow) __table_args__ = ( Index("ix_usage_tenant_created", "tenant_id", "created_at"), ) ``` Schema-per-tenant provides stronger isolation. Each tenant gets their own schema (PostgreSQL) or database (MySQL). The application connects to the correct tenant schema at runtime. This pattern simplifies compliance with data residency requirements and makes tenant deletion straightforward—drop the schema. However, schema migrations become complex: each upgrade must run across all tenant schemas. ```python # Schema-per-tenant connection from sqlalchemy import create_engine, text TENANT_SCHEMA_PREFIX = "tenant_" def get_tenant_engine(tenant_id: str) -> Engine: # In production, connection pooling per tenant becomes expensive connection_string = f"{BASE_URL}/{TENANT_SCHEMA_PREFIX}{tenant_id}" return create_engine(connection_string) # Migrations across all tenant schemas def migrate_all_tenants(engine: Engine): with engine.connect() as conn: result = conn.execute(text("SELECT tenant_id FROM tenants")) tenant_ids = [row[0] for row in result] for tenant_id in tenant_ids: tenant_engine = get_tenant_engine(tenant_id) run_migrations(tenant_engine) ``` The hybrid approach works for many AI SaaS products: row-level isolation for development and small customers, schema separation for enterprise tiers. This adds routing complexity but handles the full customer spectrum.

EXERCISE

Calculate the resource implications of supporting 1,000 tenants with schema-per-tenant isolation. How many database connections would you need at peak? What connection pooling strategies address this?