# Approaches to tenancy in Postgres

Multi-tenancy is a term used across various kinds of technical infrastructure, including application hosting, compute, databases, and more.

For example, you may purchase cloud services from a provider, but your account is one of many that draws from a common pool of resources. Your account is one "tenant" in a multi-tenant infrastructure.

In this article, we're focusing on using a single Postgres database cluster to serve an application with many tenants—you are our customer, and your customers are tenants in that cluster.

Given the many approaches to multi-tenancy within a Postgres database, it is worth clarifying the recommended best practices and the data models you should avoid. These recommendations are informed by years of seeing multi-tenant applications, both good and bad, succeed and fail at scale.

## Definitions

The term "database" is overloaded and can refer to different things:

- A **Database Cluster** refers to the entire database server instance – the running Postgres process, its storage and any replicas.
- A **Logical Database** is an isolated namespace within a database cluster that contains its own schemas, tables, and data.

In short: one _database cluster_ can contain many _logical databases_.

When modeling data in a relational database:

- A **Tenant** refers to a single entity that accesses their own subset of data in your application.
- **Single-tenancy** refers to giving each tenant their own isolated schema, logical database, or database cluster.
- **Multi-tenancy** refers to using a consistent schema (set of tables and relationships) for all of the users of your application within a single database cluster.

## Three approaches to tenant isolation

There are three common approaches to separating tenant data within a single database cluster:

1. **Shared-schema** where each user/tenant uses a shared set of tables and is isolated by a column value such as `user_id`, `tenant_id`, etc.
2. **Schema-per-tenant** where each tenant has its own schema and tables
3. **Database-per-tenant** where each tenant has its own logical database, schema, and tables

Of the three approaches, **shared-schema** is the most common and is our recommended approach.

**Shared-schema** is the only true method of "multi-tenancy" in a relational database. Schema-per-tenant and database-per-tenant within the same database cluster do not share tables, but they do share resources.

### Good examples for multi-tenancy

Good examples of multi-tenancy include SaaS applications that need to isolate data for each customer but have so many customers that it would be impractical to assign each customer to an individual database cluster. Or multi-national applications that need to isolate data for each country, market, or region.

These are good use cases for multi-tenancy because only the **data** is different between tenants. The schema, tables, relationships, application code and access patterns are uniform across all tenants.

### Shared-schema

Recommended. This is the most common, general-purpose method for combining tenants in a single database.

- All data is stored in a single database cluster
- All tenants share the same schema and tables
- Each tenant's data is isolated with a column such as `tenant_id`

This is the simplest model conceptually and the most scalable approach to multi-tenancy.

#### Modeling tenants

In most multi-tenant applications, tenants have metadata beyond just an ID — a name, a region, etc. A dedicated `tenants` table gives you a place to store this and lets the `tenant_id` column across your schema remain a compact, performant `BIGINT` foreign key.

```
CREATE TABLE tenants (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    code VARCHAR(2) UNIQUE NOT NULL, -- 'uk', 'de'
    name TEXT NOT NULL               -- 'United Kingdom', 'Germany'
);
```

Using a `BIGINT` for `tenant_id` is preferred over text-based identifiers. A `BIGINT` is faster to compare than a string and is a stable identifier that won't need to change if a tenant rebrands or a region code is restructured.

### Enforcing tenant filtering

The inherent risk of shared-schema is that **every** query must include `WHERE tenant_id = ?`. Rather than relying on each query to add this manually, use ORM global scopes, middleware, or a shared data access layer to inject the tenant filter automatically.

Postgres also offers Row-Level Security (RLS) as an optional, additional layer of defense. RLS automatically appends a filter to every query on a table based on a session variable.

```
-- Create a non-superuser role for the application
CREATE ROLE app_user LOGIN PASSWORD 'secret';
GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO app_user;

-- Enable RLS and define the policy
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

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

### Partitioning

With all data stored in a single table, as your database scales and your tenant count grows, shared-schema can be further optimized by partitioning the table. The `tenant_id` column, which is used to partition the data, is an ideal partition key.

```
-- Create a partitioned table
CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY,
    tenant_id BIGINT NOT NULL,
    customer_name TEXT,
    total NUMERIC,
    PRIMARY KEY (tenant_id, id)
) PARTITION BY LIST (tenant_id);

-- Create a partition for each tenant
CREATE TABLE orders_tenant_1 PARTITION OF orders FOR VALUES IN (1);
CREATE TABLE orders_tenant_2 PARTITION OF orders FOR VALUES IN (2);
```

### Tenant data lifecycle

With partitioning, onboarding each new tenant requires creating a new partition. Removing tenants from a shared-schema configuration may require doing table-level delete operations, which can generate a significant number of dead tuples and increase vacuum pressure.

```
DELETE FROM orders WHERE tenant_id = 1;
```

## Schema-per-tenant

Generally not recommended. Schema-per-tenant has a few benefits but does not work well at scale.

- All data is stored in a single database cluster
- Each tenant has its own schema and tables
- Each tenant's schema and data are isolated by the schema name as a prefix to the table name

### Tenant data lifecycle

Onboarding new tenants requires creating a new schema for the tenant. Removing tenants from a schema-per-tenant configuration may be a simple operation.

```
DROP SCHEMA uk CASCADE;
```

## Database-per-tenant

Generally not recommended. Database-per-tenant has a few benefits but is at odds with the connection model of Postgres.

- All data is stored in a single database cluster
- Each tenant has its own logical database, schema, and tables
- Each tenant's data is isolated by the logical database name

### Security considerations

Of all the multi-tenancy approaches, the database-per-tenant approach is the most isolated from a security perspective. Each tenant has its own logical database, schema, and tables. Each tenant's data can be accessed only by a user with privileges to that database and schema.