Separate Schema

Introduction

Multi-Tenant Separate Schema gives each tenant (or tenant tier) its own namespace within a shared database instance — tenant_a.listings vs tenant_b.listings. Airbnb uses this for enterprise host partners who need isolation stronger than row filters but don't require separate database servers. Schema-per-tenant balances isolation, cost, and migration complexity.

Real production story

Airbnb's host platform ran most hosts on shared schema with host_id filtering. Enterprise hotel chains signing multi-year deals required contractual "logical separation" — shared schema failed legal review even with RLS. Separate database per chain was rejected: 200 enterprise partners × dedicated RDS = ops nightmare.

Staff architects chose separate schema per enterprise partner in shared PostgreSQL clusters: marriott.listings, hilton.reservations. Provisioning ran via Terraform on contract signature — schema + role + connection string in 4 minutes. Migrations ran per-schema with orchestration tool tracking lag per partner. When shared-schema migration broke a custom reporting view for one chain, only their schema was affected — blast radius contained.

Business problem

Business pressure: Airbnb enterprise deals require isolation guarantees and optional schema customization (custom indexes, extension tables) without full database-per-tenant cost.

• Revenue at risk: Enterprise host contracts ($10M+) include isolation SLAs — shared schema row filters fail legal review.
• Engineering velocity: Most hosts stay on shared schema; enterprise tier gets separate schema — two migration tracks to maintain.
• Compliance / trust: Partners audit schema access — separate role per schema simplifies "who can see our data" answers.

Architecture overview

Separate Schema in production: each tenant gets PostgreSQL schema (namespace) with identical or customized table set. Connection sets search_path or uses qualified names. Migrations apply per schema — can lag for one tenant without blocking others.

• Definition: Logical isolation via database namespace per tenant within shared database instance(s).
• When to adopt: Enterprise tier needing isolation + optional customization; hundreds not millions of isolated tenants.
• When to defer: Millions of uniform tenants (ops explosion) or true physical isolation required (use separate database).
• Operability: Schema provisioning automation; migration runner with per-schema version tracking.

Architecture motivation

Why architects care: Separate schema is the middle tier between shared schema (cheap, risky for enterprise) and separate database (isolated, expensive). Choose when contracts demand namespace isolation and limited customization.

• Force: Enterprise tenants reject shared schema; separate DB too costly at hundreds of tenants.
• Constraint: Platform team cannot run 200 independent database fleets.
• Outcome: Schema-per-tenant with automated provisioning, per-schema migration orchestration, shared connection pool routing.

Internal architecture

Airbnb enterprise schema topology:

PostgreSQL Cluster (shared RDS)
├── public schema          ← standard hosts (shared schema model)
├── marriott schema        ← enterprise partner
│   ├── listings
│   └── reservations
├── hilton schema
│   ├── listings
│   ├── reservations
│   └── custom_reports    ← partner-specific extension
└── migration_meta schema
    └── schema_versions(partner_id, version, applied_at)

Data flow

Connection routing: host authenticates → tenant tier resolved → connection pool sets search_path TO partner_schema, public → queries hit partner namespace only.

• Write path: enterprise API sets schema from JWT partner claim — pool hands out schema-scoped connection.
• Read path: standard hosts use public schema with host_id filter — separate code path.
• Async path: migration runner iterates schemas, applies pending version with timeout per schema.

class SchemaRouter {
  getPool(partnerId: string | null): Pool {
    if (!partnerId) return this.sharedPool; // public schema
    const schema = this.resolveSchema(partnerId); // e.g. "marriott"
    return this.pools.get(schema) ?? this.createPool(schema);
  }

  private createPool(schema: string): Pool {
    const pool = new Pool({
      ...baseConfig,
      options: `-c search_path=${schema},public`,
    });
    this.pools.set(schema, pool);
    return pool;
  }
}

// Migration orchestrator
for (const schema of await listEnterpriseSchemas()) {
  await migrateSchema(schema, targetVersion);
}

System design diagram

Two diagrams show the Separate Schema topology and the primary request/event path used in production at scale.

Production code example

Schema provisioning Terraform + migration lag monitor:

# terraform/modules/enterprise_schema/main.tf
resource "postgresql_schema" "partner" {
  name  = var.partner_slug
  owner = postgresql_role.partner_app.name
}

resource "postgresql_grant" "partner_usage" {
  database    = var.db_name
  role        = postgresql_role.partner_app.name
  schema      = postgresql_schema.partner.name
  privileges  = ["USAGE"]
}

# Lag alert: schema_versions.version < fleet_target - 1 for >24h → PagerDuty

Enterprise case study

Airbnb enterprise host tier: 180 separate schemas on 6 RDS shards; Terraform provisioning; Flyway-style per-schema migration with lag dashboard.

• Before: Enterprise legal blocked shared schema; separate DB quote rejected by finance.
• Decision: Separate schema tier with automated provisioning; standard hosts remain shared schema.
• After: $40M enterprise pipeline unblocked; one partner migration failure contained to single schema.

Trade-offs

• Isolation vs ops complexity: Better than shared schema for enterprise; worse than shared schema for migration fleet (N schemas × M migrations).
• Customization vs drift: Partner-specific tables create schema drift — governance required.
• Connection pools vs memory: Per-schema pools multiply connections — use PgBouncer with search_path routing.

Security considerations

Security is architectural: Each enterprise schema gets dedicated DB role with USAGE only on its schema — role cannot read other partner schemas.

• Identity: PostgreSQL role per partner; connection credentials scoped to schema.
• Data: GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA marriott TO marriott_app_role.
• Supply chain: Migration scripts tested against clone schema before fleet rollout.

Scalability analysis

Scale dimensions: Airbnb targets hundreds of enterprise schemas, not millions — schema count must stay bounded or ops costs explode.

• Horizontal scale: Shard enterprise schemas across multiple RDS instances by partner tier/size.
• Hot spots: Largest chain schema grows 10× median — move to dedicated RDS (promote to separate database tier).
• Cost: Schema metadata overhead minimal; connection pool multiplication is the hidden cost.

Failure scenarios

What breaks: Migration succeeds on 198/200 schemas; search_path misconfiguration leaks public schema data; schema provisioning fails mid-contract signing.

• Partial migration: migration_meta tracks per-schema version; alert on lag >1 version behind fleet.
• search_path leak: integration test asserts queries never return rows from wrong schema.
• Provisioning failure: idempotent Terraform apply with rollback; never half-provisioned schema in prod.

Staff engineer insights

• Cap the number of separate schemas — above ~500, migration orchestration cost rivals separate databases.
• Promotion path: shared schema → separate schema → separate database as tenant grows and pays.
• Never allow ad-hoc partner schema customization without architecture review — drift kills migration runner.

Interview questions

Architecture review questions

• Are quality attributes (latency, availability, consistency) explicit with SLOs for Multi-Tenant Separate Schema?
• Is the failure/degraded mode documented — including what happens when dependencies are down?
• Are boundaries and ownership clear on an architecture diagram a new engineer understands in 10 minutes?
• Is there an ADR capturing alternatives considered and why they were rejected?
• Can this design scale 10× on traffic and 3× on engineering headcount without a rewrite?
• Security: authn/authz, encryption, and blast radius reviewed at every external interface?

Summary

Multi-Tenant Separate Schema at Airbnb scale gives enterprise partners namespace isolation within shared database infrastructure — with automated provisioning, role-scoped access, and per-schema migration orchestration. Cap schema count and productize tier promotion.