Multi-Tenant Postgres: Choosing a Pattern You Can Live With

Multi-tenancy is one of those decisions that is cheap at the start and expensive to revisit. Three approaches dominate, and the right one depends less on scale than on your isolation requirements.

The three patterns

Shared tables with a tenant column

Every row carries a tenant_id. One schema, one connection pool, simple migrations.

This is the right default for most products. It scales well, it is operationally simple, and the main risk — a query that forgets the tenant filter — is manageable with discipline and row-level security.

Schema per tenant

Each tenant gets its own Postgres schema with identical tables. Stronger isolation, easy per-tenant backup and export.

The cost arrives at migration time: a change must be applied to every schema, and with thousands of tenants that becomes a long, failure-prone operation. Connection pooling also gets more complicated.

Reasonable at tens or low hundreds of tenants. Painful beyond that.

Database per tenant

Maximum isolation, straightforward compliance story, trivial to delete a tenant completely. Also maximum operational overhead.

Appropriate for enterprise products with few, large, contractually demanding customers. Wrong for anything self-serve.

Row-level security is worth the setup

If you take the shared-table approach, Postgres row-level security turns "remember to filter by tenant" from a convention into an enforced constraint. Set the tenant in a session variable, define policies on each table, and a forgotten WHERE clause returns nothing rather than returning everyone's data.

This is the difference between a bug and a breach. The setup cost is a few hours.

Test it the obvious way: authenticate as one tenant and attempt to read another's record by its primary key. That test belongs in your suite permanently.

Indexing for tenancy

Composite indexes should lead with the tenant column. An index on (created_at) is close to useless in a multi-tenant table; (tenant_id, created_at) is what your queries actually use.

Watch for the large-tenant problem: if one customer holds most of the rows, the planner's statistics are skewed by them and query plans tuned on averages will be wrong for everyone else.

Plan the exits early

Two operations are always eventually requested, and both are miserable to retrofit:

Export a tenant's data. Increasingly a legal requirement, not a courtesy.

Delete a tenant completely. Including from backups, within a stated window.

If every tenant-owned table carries the tenant column and foreign keys cascade correctly, both are straightforward. If tenancy is implied through three levels of joins, neither is.

A reasonable default

Shared tables, tenant column on everything, row-level security enabled, tenant-leading composite indexes, and an export path written before the first customer asks. Move to stronger isolation when a specific customer's contract requires it — not in anticipation.