How to Design a Multi-Tenant Postgres Schema

A multi-tenant Postgres application can share tables, give each tenant a schema, or use separate databases. Compare the isolation requirements, migration process, and operational cost of each pattern before choosing one.

The Three Multi-Tenant Postgres Schema Patterns

1. Shared Tables with a tenant_id Column

Every tenant's data lives in the same tables. Each row carries a tenant_id foreign key that ties it to a specific tenant.

How it works:

CREATE TABLE orders (
  id          BIGSERIAL PRIMARY KEY,
  tenant_id   BIGINT NOT NULL REFERENCES tenants(id),
  customer_id BIGINT NOT NULL,
  status      TEXT NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
 
CREATE INDEX ON orders (tenant_id, created_at DESC);

Tenant-scoped queries need an enforced tenant boundary. Missing an application filter can expose other tenants’ rows if no database policy prevents access.

Enforce row-level security at the database layer, not just in application code:

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.tenant_id')::BIGINT);

Set tenant context from authenticated server-side state and test policies using the actual application role. Superusers and BYPASSRLS roles bypass RLS; table owners normally do too unless FORCE ROW LEVEL SECURITY applies. The example alone does not establish isolation. PostgreSQL row security.

When to use shared tables: Products whose isolation requirements can be met with shared infrastructure, tested access controls, and a manageable query workload.

2. Schema-Per-Tenant

Each tenant gets its own Postgres schema (a namespace inside the same database). Tables are identical across schemas; the connection switches the search_path to route queries to the correct tenant.

CREATE SCHEMA tenant_42;
SET search_path TO tenant_42, public;

Advantages: Natural isolation for schema migrations — you can migrate one tenant at a time. No tenant_id filter required. Easier to dump or restore a single tenant's data.

Disadvantages: Postgres has real limits on the number of schemas in a single database before performance degrades. Cross-tenant queries become difficult. Schema migration tooling often does not handle multi-schema setups cleanly, so you end up writing migration runners yourself.

When to use schema-per-tenant: Products where regulatory compliance or contractual SLAs require logical isolation, but where running separate databases is not practical.

3. Database-Per-Tenant

Each tenant gets a completely separate Postgres database, sometimes on a separate instance. This is the strictest isolation model.

Advantages: Separate database connections and access privileges per tenant. Evaluate backup, restore, and resource-isolation requirements separately; databases on one instance still share infrastructure.

Disadvantages: Operational cost scales linearly with tenant count. Cross-tenant analytics require a separate pipeline. Connection pooling becomes more complex because connection pools are per-database.

When to use database-per-tenant: Enterprise SaaS with strict compliance requirements, high-value customers who own their data contractually, or products where tenants have wildly different scaling needs.

Designing a Multi-Tenant Postgres Schema: Key Decisions

Primary Keys and Foreign Keys

In shared-table multi-tenancy, composite unique constraints that include tenant_id are often necessary:

ALTER TABLE projects
  ADD CONSTRAINT projects_tenant_slug_unique UNIQUE (tenant_id, slug);

Without tenant_id in the constraint, two tenants could collide on a slug that is unique only within their own workspace.

Indexing for Tenant Isolation

Choose indexes from the query predicates and measured plans. A B-tree index beginning with tenant_id can suit queries that filter by tenant, but it is not a requirement for every index. PostgreSQL multicolumn indexes.

Audit Trails and Soft Deletes

Include tenant identity in audit records and define their retention policy. Soft deletion of business records is a separate choice; the following example indexes active rows:

ALTER TABLE projects ADD COLUMN deleted_at TIMESTAMPTZ;
CREATE INDEX ON projects (tenant_id) WHERE deleted_at IS NULL;

Partial indexes like this keep active-record queries fast without scanning soft-deleted rows.

Common Multi-Tenant Schema Mistakes

• Missing tenant_id on join tables. If project_members joins users and projects, it still needs its own tenant_id column and RLS policy.
• Using application-level filtering as the only isolation mechanism. A single missed WHERE tenant_id = ? exposes all tenant data. Use RLS as a backstop.
• Ignoring connection pool configuration. In shared-table mode, the same pool serves all tenants. Make sure app.tenant_id is reset correctly between requests, especially with poolers like PgBouncer in transaction mode.
• Treating schema-per-tenant as free isolation. Schema names alone do not enforce isolation. Access depends on schema and object privileges; a shared role with access to every schema can cross tenant boundaries. PostgreSQL schemas and privileges.

Choosing a Pattern

Compare isolation, query workload, backup/restore needs, migration scheduling, and operating cost. Tenant-specific migrations may favor separate schemas; stricter resource or recovery requirements may justify separate databases or instances. Validate the chosen controls rather than treating a pattern name as an isolation guarantee.

Discuss tenant isolation and schema design with LinkIT.