We've updated our use of the term "row-level isolation" to "shared-schema" in this article to avoid confusion with Postgres RLS.We do not recommend relying on Postgres RLS.
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.
When you generate credentials to connect to a database, you're connecting to the database cluster. The queries you perform will target a single logical database within it. On PlanetScale Postgres, the default logical database name is postgres.
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:
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.
Schema-per-tenant where each tenant has its own schema and tables
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 also 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.
Finally, you may already be running a database using one schema-per-tenant. You may be able to migrate to a recommended approach to improve the performance of your application and workloads. See Migrating to schared-schema multi-tenancy.
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.
With any multi-tenancy approach, your goal should be for data belonging to each tenant to be consumed by the same applications, with care to ensure that one tenant cannot query another tenant's data nor that their behavior in your application could jeopardize the experience of another tenant.
These recommendations assume all tenants share the same schema. If tenants genuinely need different schema structures, schema-per-tenant or database-per-tenant is the better fit.
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
With shared-schema, each tenant shares the same schema and tables, but has its own data.
This is the simplest model conceptually and the most scalable approach to multi-tenancy.CREATE TABLE orders (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
tenant_id BIGINT NOT NULL,
customer_name TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
total NUMERIC
);
-- tenant_id should lead most indexes
CREATE INDEX idx_orders_tenant_created ON orders(tenant_id, created_at DESC);
-- Insert data for different tenants into the same table
INSERT INTO orders (tenant_id, customer_name, total) VALUES (1, 'Alice', 49.99);
INSERT INTO orders (tenant_id, customer_name, total) VALUES (2, 'Hans', 59.99);
-- Every query must filter by tenant
SELECT * FROM orders WHERE tenant_id = 1;
Depending on the size of your tables, shared-schema can easily scale to many thousands of tenants. Migrations and schema changes need only be applied to a single table to update all tenants. Querying across tenants is simple and efficient.
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.
The column name tenant_id is a common one, but not a required naming convention. For example, a social media application may use the column user_id for the same purpose.
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. In the example below, RLS ensures that queries are scoped to the current tenant without relying on the application to include the filter.-- 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);
-- At runtime, your app sets the tenant context per request
BEGIN;
SET LOCAL app.current_tenant = '1';
-- SET LOCAL ensures the setting is scoped to this transaction
-- which is important when using connection pooling.
-- Only returns orders for tenant_id = 1
SELECT * FROM orders;
COMMIT;
We generally don't recommend relying on RLS. It shifts security logic into the database, where policy misconfiguration, silent failures, and connection pooling interactions are difficult to debug. Keep tenant isolation enforced in your application code.
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.
Partitioning is a Postgres feature that splits a single logical table into multiple sub-tables based on a column value. Your application queries don't need to target a specific partition, as Postgres will automatically route the query to the correct one.
In practice, you would only partition tables that grow large enough to benefit from it. A messages table with billions of rows is a strong candidate for partitioning by tenant, but not a small reference table like office_locations with only thousands of rows.
Note that Postgres requires the partition key to be part of the primary key on partitioned tables.-- 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
-- All rows with tenant_id=1 (UK) go into 'orders_tenant_1'
CREATE TABLE orders_tenant_1 PARTITION OF orders FOR VALUES IN (1);
-- All rows with tenant_id=2 (DE) go into 'orders_tenant_2'
CREATE TABLE orders_tenant_2 PARTITION OF orders FOR VALUES IN (2);
-- Your application doesn't know or care about partitions
INSERT INTO orders (tenant_id, customer_name, total) VALUES (1, 'Alice', 49.99);
-- Postgres automatically routes this to orders_tenant_1
SELECT * FROM orders WHERE tenant_id = 1;
-- Postgres only scans orders_tenant_1 (partition pruning)
Partitioning can greatly improve performance and scalability by reducing the amount of data that needs to be scanned and the size of indexes. Internal processes such as vacuuming and index maintenance are also performed on a per-partition basis.
This adds to operational overhead, as you will need to create a new partition for each tenant.
Row-level isolation with partitioning offers some of the benefits of database-per-tenant multi-tenancy with lower operational overhead.
Tenant data lifecycle
With partitioning, onboarding each new tenant requires creating a new partition.
Partitioning simplifies offboarding tenants: you can drop the partition, and all data for that tenant is deleted.-- Wrap in a transaction in case the DROP fails
BEGIN;
ALTER TABLE orders DETACH PARTITION orders_tenant_1;
DROP TABLE orders_tenant_1;
COMMIT;
Without partitioning, a new tenant's data can be inserted into a table with no schema changes or migrations.
However, removing tenants requires 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
The appeal of this approach is greater isolation, since your queries do not need to filter on a specified tenant_id column. Instead, your application can reuse the same queries but with a different search_path to target the correct tenant's data.-- Create the schemas
CREATE SCHEMA uk;
CREATE SCHEMA de;
-- Each gets identical tables
CREATE TABLE uk.orders (id BIGINT PRIMARY KEY, customer_name TEXT, total NUMERIC);
CREATE TABLE de.orders (id BIGINT PRIMARY KEY, customer_name TEXT, total NUMERIC);
-- At runtime, your app sets the search path per request
BEGIN;
SET LOCAL search_path TO uk;
SELECT * FROM orders; -- returns uk.orders data
COMMIT;
BEGIN;
SET LOCAL search_path TO de;
SELECT * FROM orders; -- now returns de.orders data
COMMIT;
There are performance benefits to using a schema-per-tenant. With each table containing fewer rows, indexes are smaller and more likely to fit in the buffer cache. One tenant's update/delete churn will not increase another tenant's bloat or vacuum workload.
However, the operational overhead of maintaining a schema-per-tenant outweighs the performance benefits. It increases schema migration complexity because they need to be applied to each tenant's schema. Should you need to query across tenants, complex cross-schema joins will be required.
While this approach works, it likely won't scale beyond a few hundred tenants. Every table, index, constraint, and sequence across all schemas lives in shared system catalogs. With hundreds of schemas, each containing even a modest number of tables and their indexes, these catalogs grow into millions of rows. This slows the query planner as it consults the catalog on every query. Migrations slow down as the catalog size increases.
Safety concerns of SET search_path
There is no database-level enforcement of preventing access to the wrong schema. Schema-per-tenant feels like greater separation of data, but it does not meaningfully impact data isolation from a security perspective. You may also need to create a separate database user and set up precise schema-level permissions for better security.
Tenant data lifecycle
Onboarding new tenants requires creating a new schema for the tenant and performing a migration.
Removing tenants from a schema-per-tenant configuration may be one of the few operational advantages of this approach to multi-tenancy, as it is a single, 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
Within a PlanetScale Postgres database, you have the option to run CREATE DATABASE to create many logical databases within a single database cluster.
The appeal of using logical databases per tenant is increased isolation: you do not need to filter by a column or modify search_path; instead, you can modify the connection string to connect to the correct database. This makes working with the data and schema of an individual tenant much simpler.-- Create separate databases
CREATE DATABASE uk_store;
CREATE DATABASE de_store;
-- Connect to the UK database and create tables there
\c uk_store
CREATE TABLE orders (id BIGINT PRIMARY KEY, customer_name TEXT, total NUMERIC);
-- Connect to the German database and do the same
\c de_store
CREATE TABLE orders (id BIGINT PRIMARY KEY, customer_name TEXT, total NUMERIC);
There are notable performance benefits to using a database-per-tenant. With each table in each database containing fewer rows, indexes are smaller and more likely to fit in the buffer cache. One tenant's update/delete churn will not increase another tenant's bloat or vacuum workload.
Database-per-tenant is better for performance than a schema-per-tenant, as each database contains its own catalog of tables, indexes, constraints, and sequences.
However, these performance benefits are still outweighed by the drawbacks of increased operational complexity. Critically, connection pooling becomes a problem immediately, as PgBouncer pools are calculated per-database and will quickly exceed your max_connections limit. Connection limits are the primary issue with database-per-tenant multi-tenancy.
Additionally, each CREATE DATABASE copies Postgres's template database, consuming roughly 8 MB. Unlike schema-per-tenant, where all schemas share a single set of system catalogs, every logical database carries its own, multiplying storage and catalog maintenance overhead with each new tenant.
While all the isolation and performance benefits of a database-per-tenant are compelling, it conflicts with Postgres's connection model.
Additionally, if you need to query across tenants, there is no way to do so in Postgres. You would need to use an external data warehouse or a custom application layer to join the data together.
While this approach works, it likely won't scale beyond a few hundred tenants.
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.
Even so, the limitations on connectivity and the operational complexity of this model make it difficult to recommend.
Tenant data lifecycle
Every new tenant requires a new logical database to be created and a migration to set up its tables.
Removing tenants from a database-per-tenant configuration may be one of the few operational advantages of this approach to multi-tenancy, as it is a single, simple operation with no side effects.DROP DATABASE uk_store;
Protecting tenants from each other
In all three approaches to multi-tenancy, tenants must be protected from one another, both in terms of data access and resource contention.
Our recommended approach, shared-schema, is the most exposed because tables and indexes are shared. Care must be taken here to keep things safely isolated. Schema- and database-per-tenant approaches are more isolated at the relation level, but all three compete for CPU, memory, disk I/O, and connections.
One tenant running an expensive query degrades performance for all other tenants, commonly referred to as a "noisy neighbor" problem. Within your database, you can add some protection by setting statement_timeout and idle_in_transaction_session_timeout appropriately. Your application should also be aware of potential rate limits, which could allow one tenant to disrupt another tenant's experience.
PlanetScale Query Insights can help you identify and troubleshoot performance issues within your database, which you can debug manually or with an Agent using the PlanetScale MCP server.
Migrating to shared-schema multi-tenancy
Should your application already be configured for schema, database, or some other kind of multi-tenancy, you may be able to migrate to shared-schema multi-tenancy by adding a tenant_id column to your tables and updating your application to filter by this column.
If you are not yet on PlanetScale, we have successfully migrated large, multi-tenant workloads that were experiencing operational, performance, or scaling issues. We offer hands-on assistance on a case-by-case basis.
Reach out to discuss your current situation.
Other examples for multi-tenancy
For needs that are less than mission critical, such as internal applications and side projects, you may diverge from the recommendations in this post. For example, you might like to run distinct applications from a single database cluster, as it seems cheaper or operationally advantageous.
If your multiple "tenants" are actually different applications with unique data structures running from a single database, we simply ask you to exercise caution.
If you can't behave, be careful.
