中文
devexamples
← Recipes

阅读中文版本

Isolate Tenants with a Schema per Tenant

Give each tenant its own database schema, route connections by tenant, and run migrations across all schemas so isolation does not become a migration burden.

~50 minmulti-tenantdatabasesisolation

Task

Provision per-tenant schemas and automate migrations to keep tenant data isolated.

When to use this

All tenants share one schema, so a single missing WHERE clause can leak another tenant rows, and noisy neighbors affect everyone.

Prerequisites

  • A database that supports multiple schemas
  • A migration tool that can target each schema

Steps

1. Create a schema per tenant and store the mapping in a registry table.

Expected output: Each tenant has a dedicated namespace.

2. Set search_path on connection checkout based on the resolved tenant.

Expected output: Queries resolve to the right schema without tenant columns.

3. Run migrations once per schema in a controlled loop.

Expected output: All schemas stay on the same version.

4. Verify isolation with a test that queries as one tenant and asserts it cannot read another.

psql -c "SET search_path TO tenant_acme, public; SELECT count(*) FROM orders;"

Expected output: Cross-tenant leakage is caught before release.

Troubleshooting

Problem

Migration loop fails midway

Solution

Record per-schema version and resume idempotently; never assume all schemas migrated.

Problem

Connection pool reuse across tenants

Solution

Reset search_path on release, or use a pool per tenant, to prevent bleed between requests.

Limitations

  • Schema-per-tenant multiplies migration and connection overhead as tenant count grows.
  • Database-level limits on schema and connection counts cap how far this model scales.

Context

Provision per-tenant schemas and automate migrations to keep tenant data isolated. All tenants share one schema, so a single missing WHERE clause can leak another tenant rows, and noisy neighbors affect everyone.

Steps

  1. Create a schema per tenant and store the mapping in a registry table. Expected: Each tenant has a dedicated namespace.
  2. Set search_path on connection checkout based on the resolved tenant. Expected: Queries resolve to the right schema without tenant columns.
  3. Run migrations once per schema in a controlled loop. Expected: All schemas stay on the same version.
  4. Verify isolation with a test that queries as one tenant and asserts it cannot read another. Expected: Cross-tenant leakage is caught before release.
SELECT set_config('search_path', 'tenant_acme, public', false);
-- all unqualified queries now resolve to tenant_acme

Verify

  • Each tenant has a dedicated namespace.
  • Queries resolve to the right schema without tenant columns.
  • All schemas stay on the same version.
  • Cross-tenant leakage is caught before release.

Limits

  • Schema-per-tenant multiplies migration and connection overhead as tenant count grows.
  • Database-level limits on schema and connection counts cap how far this model scales.