# How to Design a Multi-Tenant Postgres Schema That Scales

> Learn the three main multi-tenant Postgres schema patterns — shared tables, schemas, and databases — and how to choose the right one for your product.

- **Published:** 2025-12-01
- **Author:** Clixo
- **Reading time:** 6 min read
- **Tags:** postgres, schema-design, multi-tenancy, database
- **Canonical URL:** https://clixo.sh/blog/how-to-design-multi-tenant-postgres-schema

Most SaaS products reach a point where the data model either holds the product together or quietly becomes a liability. Multi-tenancy is the design decision that either earns you flexibility later or forces a painful migration. Getting the Postgres schema pattern right before you have thousands of tenants is far easier than refactoring under load.

This guide covers the three main multi-tenant Postgres schema patterns, what each one costs you in complexity and isolation, and a practical framework for picking 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. This is the most common starting point.

**How it works:**

```sql
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);
```

Every query must include `tenant_id` in its `WHERE` clause. Forget it once and you leak data across tenants — which is the central risk of this approach.

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

```sql
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.tenant_id')::BIGINT);
```

Setting `app.tenant_id` at the start of each connection or transaction means the database enforces the boundary even if application code has a bug. Row-level security is not a silver bullet — you still need to test it — but it removes the single point of failure from the application layer.

**When to use shared tables:** Early-stage products, teams with fewer than a few hundred tenants, situations where operational simplicity matters more than strict data isolation.

### 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.

```sql
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:** Complete data isolation. Tenant-specific backups, restores, and point-in-time recovery. Per-tenant resource limits are straightforward.

**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:

```sql
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

Every index on a shared table should lead with `tenant_id`. A B-tree index on `(tenant_id, created_at DESC)` allows Postgres to satisfy most per-tenant range queries with an index scan rather than a sequential scan.

Do not create indexes on high-cardinality columns alone when the query pattern always filters by tenant first. The planner will usually prefer the composite index anyway, but having a solo index wastes write overhead.

### Audit Trails and Soft Deletes

Multi-tenant products almost always need audit logs. Put audit tables in the shared schema, always include `tenant_id`, and never delete rows — use a `deleted_at` column:

```sql
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 separation is namespace separation, not security isolation. A single compromised connection string still has access to all schemas.

## Choosing a Pattern

```mermaid
flowchart TD
  A["New SaaS product"] --> B{"Compliance or contractual isolation required?"}
  B -- No --> C["Shared tables with tenant_id + RLS"]
  B -- Yes --> D{"Separate databases practical?"}
  D -- No --> E["Schema-per-tenant"]
  D -- Yes --> F["Database-per-tenant"]
  C --> G["Composite indexes on tenant_id"]
  E --> G
  F --> H["Separate connection pools per tenant"]
```

Start with shared tables unless you have a concrete compliance requirement that demands otherwise. Shared tables are simpler to operate, easier to migrate data in, and straightforward to reason about as long as RLS is enabled.

Move to schema-per-tenant when you need to run tenant-specific migrations on different schedules. Move to database-per-tenant only when you have a signed contract requiring it or a regulatory mandate.

The most expensive mistake is over-engineering the isolation model before you have paying customers. The second most expensive is under-engineering it and needing to migrate a running production system.

---

If you are designing a SaaS data model and want to get the schema right before you scale, the Clixo team has done this for multiple products. [Start a build](https://clixo.sh/#contact) and we can walk through the tradeoffs for your specific product.

---

Clixo · 1141 W Bryn Mawr Ave, Itasca, IL 60143, US · [hello@clixo.sh](mailto:hello@clixo.sh)
[Start a build](https://clixo.sh/#contact) · [All services](https://clixo.sh/services) · [Agent guide (llms.txt)](https://clixo.sh/llms.txt)