How to Design Multi-Tenant PostgreSQL Schemas

Choose isolation from restore, residency, and fleet requirements

Shared tables are usually the practical default for a conventional SaaS: one migration history, ordinary joins and transactions, efficient pooled connections, and straightforward product-wide operations. They also put every customer's rows behind the same application and database roles, so the schema and authorization path must make accidental cross-tenant access difficult.

A schema is a namespace, not a miniature database. PostgreSQL's schema documentation warns that adding a schema to search_patheffectively trusts users who can create objects there. Schema-per-tenant designs therefore require controlled privileges and qualified names, while each migration must run across a growing fleet of objects. They fit a bounded tenant count or meaningful per-tenant customization better than an assumption of effortless isolation.

Database-per-tenant can support strict residency, independent encryption and maintenance, or direct tenant restore. It also multiplies connection pools, backups, monitoring, extensions, schema versions, and incident surfaces. A hybrid is often more durable: keep most tenants in shared tables and route exceptional tenants to dedicated databases or deployment stamps. Preserve one stable tenant ID so physical placement can change.

Carry tenant ownership through every relationship

In shared tables, put tenant_id on every tenant-owned row and make it non-null. Include it in primary or unique keys that other tenant-owned tables reference. A foreign key on project_idproves that a project exists; a foreign key on (tenant_id, project_id) also proves that the task and project share an owner.

CREATE TABLE tenants (
  id uuid PRIMARY KEY,
  name text NOT NULL
);

CREATE TABLE projects (
  tenant_id uuid NOT NULL REFERENCES tenants (id),
  id uuid NOT NULL,
  slug text NOT NULL,
  name text NOT NULL,
  PRIMARY KEY (tenant_id, id),
  UNIQUE (tenant_id, slug)
);

CREATE TABLE tasks (
  tenant_id uuid NOT NULL,
  id uuid NOT NULL,
  project_id uuid NOT NULL,
  title text NOT NULL,
  PRIMARY KEY (tenant_id, id),
  FOREIGN KEY (tenant_id, project_id)
    REFERENCES projects (tenant_id, id)
);

PostgreSQL supports multicolumn primary, unique, and foreign-key constraints directly. Scope human identifiers—project slugs, invoice numbers, external references—to the tenant unless the product truly needs a global namespace. Do not put tenant_id on genuinely global reference tables such as currencies merely for symmetry. Membership rows, however, belong to a tenant because they connect a global identity to a tenant-specific role.

Choose delete behavior from the product lifecycle. Cascading from a tenant into active projects may be correct; cascading into invoices or audit evidence may violate retention requirements. PostgreSQL does not automatically index referencing foreign-key columns, so add indexes for actual joins and delete checks where the query plan shows a need.

Use RLS as defense in depth, with a non-bypass application role

Application queries should still specify tenant scope. PostgreSQL row security policies add a database check so a forgotten predicate does not turn into an unrestricted read or write. When RLS is enabled and no applicable policy exists, PostgreSQL uses default deny. USING controls which existing rows are visible;WITH CHECK controls rows created or changed.

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects FORCE ROW LEVEL SECURITY;

CREATE POLICY projects_tenant_policy ON projects
  TO app_user
  USING (
    tenant_id = current_setting('app.tenant_id', true)::uuid
  )
  WITH CHECK (
    tenant_id = current_setting('app.tenant_id', true)::uuid
  );

Role design is part of the policy. PostgreSQL superusers and roles withBYPASSRLS always bypass row security, and table owners normally do too unless the table uses FORCE ROW LEVEL SECURITY. Run web requests and workers as a least-privilege role that neither owns tables nor bypasses RLS. Reserve migration and break-glass roles for isolated paths, log their use, and never expose them to user-driven queries.

Test reads, inserts, updates that attempt to change tenant ownership, deletes, joins, views, functions, bulk jobs, and administrative tools. Remember that TRUNCATE and referential-integrity checks are not governed like ordinary row access. RLS narrows a class of mistakes; it does not replace privileges, safe functions, application authorization, or cross-tenant security tests.

Bind tenant context to the pooled transaction

Resolve the tenant from authenticated membership on the server. Never trust a raw request header as authority. With a connection pool, set the tenant context transaction-locally on the same connection that executes the queries. PostgreSQL's set_config function accepts a local flag; a local value lasts only for the current transaction, reducing the chance that one request leaves state on a connection reused by another.

BEGIN;

SELECT set_config('app.tenant_id', :trusted_tenant_id, true);

SELECT id, slug, name
FROM projects
WHERE tenant_id = :trusted_tenant_id
ORDER BY name;

COMMIT;

Keep the explicit predicate even with RLS. It makes intent visible, helps indexes and routing, and avoids treating a security policy as a query API. Your data layer should require tenant context for tenant-owned operations and fail closed when it is missing. Background jobs need the same setup; “internal” is not a tenant.

Verify how the chosen driver implements transactions and pooling. A session-level SET, a transaction wrapper that silently uses another connection, or an ORM path that runs before context is set can invalidate the design. Integration tests should run through the production pool and application role, not only an owner account in a SQL console.

Design indexes from tenant-scoped query shapes

A typical index starts with tenant equality and then follows the filter and ordering used inside that tenant. For a task list queried by project and sorted newest first, (tenant_id, project_id, created_at DESC)is a plausible candidate. PostgreSQL notes that multicolumn B-tree indexes are most efficient when predicates constrain the leading columns, but it also recommends using multicolumn indexes sparingly.

CREATE INDEX tasks_by_project_created
  ON tasks (tenant_id, project_id, created_at DESC);

SELECT id, title, created_at
FROM tasks
WHERE tenant_id = $1
  AND project_id = $2
ORDER BY created_at DESC
LIMIT 50;

Do not prefix every index mechanically with tenant_id. A low-cardinality leading column can make an index unhelpful for product-wide administrative queries, and redundant indexes increase storage and write cost. Use production-like data with EXPLAIN (ANALYZE, BUFFERS), observe slow statements, and keep separate indexes for genuinely different global and tenant-scoped access patterns.

Table partitioning is not an automatic tenant-isolation feature. Use it when pruning, lifecycle management, or maintenance of very large tables provides a measured benefit. Thousands of tiny tenant partitions can make planning and migrations worse. Distributed PostgreSQL systems often use the tenant ID as a shard key so one tenant's related rows colocate, but that is a later scaling decision with product-wide query tradeoffs.

Plan migrations, tenant moves, and deletion before they are urgent

Add tenant ownership to an existing large table in stages: add a nullable column, backfill in bounded batches, write both old and new paths if needed, validate constraints, update keys and policies, then make the column non-null. Review lock behavior and use compatibility periods so old and new application versions can overlap. Test the migration with realistic row counts and skew; one oversized tenant can dominate an otherwise small database.

Keep an explicit tenant-placement record if a hybrid model is possible. Moving a tenant needs a consistent snapshot or change-capture plan, verification counts, cutover control, rollback, and cache/job routing updates. Global analytics should use a defined export or warehouse path rather than privileged ad hoc queries that bypass normal isolation.

Define deletion and export semantics per data class. A tenant deletion may need to cancel jobs, revoke files, clear caches, delete derived embeddings, and retain legally required financial records. Verify backup retention and the practical meaning of deletion there. For restoration, know whether the requirement is full-cluster recovery or one tenant; shared tables make the latter an application-level recovery workflow.

Review the schema with failure-focused tests

The goal is not to make every query look alike. It is to make tenant ownership hard to lose as the system changes. See the broader SaaS architecture checklist, the engineering services, and relevant project case studies for the application and delivery context around the database.