Multi-Tenant Monolith Database Design: Shared Tables vs Schema per Tenant

Three tenancy models and where each draws the isolation boundary

Every multi-tenant design answers one question: what stops tenant A from reading tenant B's rows? The three common models answer it at different layers.

Shared tables put all tenants in the same tables, and every row carries a tenant_id. The boundary is the query. Isolation holds only as long as every statement filters on the right tenant. Row-level security (RLS) moves that filter out of application code and into the database, so a forgotten WHERE clause returns nothing instead of leaking data.

Schema per tenant gives each tenant its own copy of the tables inside a separate namespace in one database. The boundary is the namespace. Queries resolve against whichever schema the session points to, so a missing filter cannot cross tenants, but a wrong schema setting can.

Database per tenant gives each tenant its own database, and sometimes its own server. The boundary is the catalog or the server. Crossing it requires a different connection.

This article's position: default to shared tables with RLS enforcement. Treat schema per tenant as a deliberate escape hatch for specific tenants with a concrete reason, not as the starting architecture.

CopySHARED TABLES            SCHEMA PER TENANT        DATABASE PER TENANT
boundary: query          boundary: namespace      boundary: catalog/server

+----------------------+ +----------------------+ +----------------------+
|        App           | |        App           | |        App           |
| SET app.tenant = A   | | SET search_path = A  | |  pick conn for A     |
+----------+-----------+ +----------+-----------+ +---+--------------+---+
           |                        |                 |              |
           v                        v                 v              v
+----------------------+ +----------------------+ +---------+  +---------+
| Database             | | Database             | | DB: A   |  | DB: B   |
| +------------------+ | | +-------+  +-------+ | | orders  |  | orders  |
| | RLS policy:      | | | |schema |  |schema | | | users   |  | users   |
| | tenant_id = A    | | | |  A    |  |  B    | | +---------+  +---------+
| +------------------+ | | |orders |  |orders | |  server 1     server 2
| | orders (shared)  | | | |users  |  |users  | |
| | A | A | B | B    | | | +-------+  +-------+ |
| +------------------+ | +----------------------+
+----------------------+

fails if: policy absent  fails if: wrong path     fails if: wrong conn

Each tenancy model places the tenant boundary at a different layer: an RLS filter on shared rows, a schema selected by the session's search_path, or a separate database reached through a separate connection.

How shared tables work: tenant_id, keys, and row-level security

Every tenant-owned table gets a non-null tenant_id column, including child tables whose parent already carries one. Denormalizing it this way means any row can be checked for ownership without a join, and the database can enforce that relationships never cross tenants.

Lead keys with tenant_id. A composite primary key such as (tenant_id, id) lets a child table declare a foreign key on (tenant_id, parent_id) that references it. That foreign key makes it impossible for tenant A's invoice to point at tenant B's customer. Composite indexes that start with tenant_id also match the shape of nearly every query, since the application almost always filters by tenant first.

Unique constraints usually need the same treatment. A global UNIQUE (email) blocks two tenants from each registering the same user, and the resulting error tells one tenant that the other's data exists. UNIQUE (tenant_id, email) expresses the actual rule.

PostgreSQL row-level security is the backstop for the query that forgets its WHERE tenant_id = .... Create a policy whose predicate compares tenant_id to current_setting('app.tenant_id'), cast to the column's type. Then run both ENABLE ROW LEVEL SECURITY and FORCE ROW LEVEL SECURITY on the table, because table owners skip policies unless the table is forced. The application should also connect as a role that does not own the tables and lacks BYPASSRLS. Superusers ignore RLS entirely.

Keep predicates simple. A plain equality check is cheap and plays well with indexes, while predicates that call functions or run subqueries against a membership table can change query plans noticeably. Measure on your own workload before you rely on them.

Copy-- Parent table: tenant_id leads the primary key.
CREATE TABLE customers (
  tenant_id uuid   NOT NULL,
  id        bigint GENERATED ALWAYS AS IDENTITY,
  email     text   NOT NULL,
  PRIMARY KEY (tenant_id, id),
  UNIQUE (tenant_id, email)  -- unique per tenant, not globally
);

-- Child table: the composite FK keeps invoices inside their own tenant.
CREATE TABLE invoices (
  tenant_id   uuid   NOT NULL,
  id          bigint GENERATED ALWAYS AS IDENTITY,
  customer_id bigint NOT NULL,
  PRIMARY KEY (tenant_id, id),
  FOREIGN KEY (tenant_id, customer_id) REFERENCES customers (tenant_id, id)
);
CREATE INDEX invoices_tenant_customer_idx ON invoices (tenant_id, customer_id);

-- RLS backstop: a plain equality check against a per-transaction setting.
-- With missing_ok = true, an unset value yields NULL, so no rows match.
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;  -- apply to the owner too
CREATE POLICY tenant_isolation ON invoices
  USING      (tenant_id = current_setting('app.tenant_id', true)::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::uuid);
-- Repeat the ALTER TABLE and CREATE POLICY statements for customers.

-- The application connects as a non-owner without BYPASSRLS.
CREATE ROLE app_user LOGIN NOBYPASSRLS PASSWORD 'change-me-placeholder';
GRANT SELECT, INSERT, UPDATE, DELETE ON customers, invoices TO app_user;

-- Per request, as app_user: scope the setting to the transaction.
BEGIN;
SET LOCAL app.tenant_id = '6f1c2a9e-3b4d-4e8f-9a0b-1c2d3e4f5a6b';
SELECT id, customer_id FROM invoices;  -- returns only this tenant's rows
COMMIT;

PostgreSQL DDL for the shared-table model: composite keys and foreign keys that lead with tenant_id, a per-tenant unique constraint, and an enabled and forced RLS policy for a non-owner application role without BYPASSRLS.

Setting tenant context safely with connection pools

Session-level SET app.tenant_id = '42' attaches the value to the database connection, not to the request. When the request finishes and the connection goes back to the pool, the setting is still there. Application-side pools usually don't reset session state unless you configure them to. External poolers running in transaction mode reassign server connections after every transaction, so a later request from a different tenant can pick up a connection that still carries tenant 42's value. If that request forgets to set its own context, or its set fails silently, RLS evaluates against the wrong tenant and returns that tenant's rows.

SET LOCAL, or set_config('app.tenant_id', $1, true), scopes the value to the current transaction. At COMMIT or ROLLBACK the value reverts, so nothing survives into the next checkout. The safe per-request pattern follows from this:

1. Open a transaction.
2. Set the tenant from the authenticated identity as a bound parameter, never from request input.
3. Run the request's queries.
4. Commit.

If a query runs without a value, current_setting('app.tenant_id', true) returns NULL or an empty string. Write policies so that case matches no rows, or so that the cast to the key type raises an error. Either way the query fails closed.

RLS still leaks data when any of these is true:

• The app connects as the table owner. Owners bypass policies unless the table has FORCE ROW LEVEL SECURITY.
• The role is a superuser or has BYPASSRLS. FORCE does not restrict these roles.
• Context is set outside a transaction. In autocommit mode, SET LOCAL has no effect and Postgres only issues a warning. set_config(..., true) lasts for that one statement, so the queries that follow run with no tenant context.

Copy-- One-time setup, run as a migration/owner role, not as the app role.
CREATE TABLE invoices (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id bigint  NOT NULL,
  amount    numeric NOT NULL
);
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
-- FORCE makes the policy apply to the table owner too.
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;

-- Fail closed: an unset or reset value gives NULL, and NULL matches no rows.
CREATE POLICY tenant_isolation ON invoices
  USING      (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::bigint)
  WITH CHECK (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::bigint);

-- The app connects as a role that is not the owner, not a superuser,
-- and does not have BYPASSRLS.
CREATE ROLE app_user LOGIN NOSUPERUSER NOBYPASSRLS PASSWORD 'change-me-placeholder';
GRANT SELECT, INSERT, UPDATE, DELETE ON invoices TO app_user;

-- Per request, as app_user. The tenant id comes from the authenticated
-- identity and the driver sends it as a bound parameter ($1).
BEGIN;
SELECT set_config('app.tenant_id', $1, true);  -- is_local = true: transaction-scoped
SELECT id, amount FROM invoices;               -- only this tenant's rows
INSERT INTO invoices (tenant_id, amount)
  VALUES (current_setting('app.tenant_id')::bigint, 120.00);
COMMIT;  -- the setting reverts here, so the pooled connection carries no tenant

-- Anti-patterns:
--   SET app.tenant_id = '42';   -- session-level, survives into the next checkout
--   SET LOCAL ... outside BEGIN -- only a warning, no effect in autocommit

A table with forced, fail-closed RLS, a non-owner app role without BYPASSRLS, and a per-request transaction that scopes the tenant id with set_config(..., true) so the setting reverts at COMMIT and does not stay on the pooled connection.

How schema per tenant works and what it costs to operate

With schema per tenant, every tenant gets the same set of tables inside its own Postgres schema, such as tenant_acme.orders and tenant_globex.orders. The application routes queries either by setting search_path to the tenant's schema or by building schema-qualified names. Queries no longer need a tenant_id predicate, because the boundary is the namespace.

Migrations become fleet operations

A migration that used to run once now runs N times, once per schema. Any of those runs can fail partway: a lock timeout on one large tenant, a deploy interrupted at tenant 412. The migration runner therefore has to track the version of each schema, skip schemas that are already migrated, and resume from where it stopped. Each step must also be safe to re-run. Until the whole fleet finishes, some tenants are on the old schema and some are on the new one. Application code has to work against both versions during that window. In practice this means expand/contract changes: add the column, deploy code that tolerates either shape, then remove the old shape later.

Catalog growth

Every table, index, sequence, and TOAST relation is a row in the system catalogs. The number of objects grows with the number of tenants:

Illustrative estimate (assumed numbers, not a benchmark): objects ≈ tenants × (tables + indexes + sequences + TOAST tables). With 2,000 tenants × (40 + 80 + 30 + 20), that comes to 340,000 relations.

More objects slow down every process that walks the catalog:

• pg_dump and restore have more catalog entries to read and more objects to recreate.
• Autovacuum has more relations to check and process.
• Each backend's relation cache grows as it touches more tables.
• ORM introspection and monitoring queries scan more catalog rows on every run.

Schemas also give no resource isolation. All tenants share the same CPU, memory, I/O, WAL stream, and connection limit.

Copy#!/usr/bin/env bash
# Fleet migration: apply version 42 to every tenant schema.
# Safe to re-run after a partial failure: finished schemas are skipped.
# Credentials come from ~/.pgpass or PGPASSWORD, not this script.
set -euo pipefail
DB="postgresql://migrator@db.internal:5432/app"
VERSION=42
failed=0

# Discover tenant schemas from the catalog instead of a hard-coded list.
schemas=$(psql "$DB" -At -c "SELECT nspname FROM pg_namespace WHERE nspname LIKE 'tenant\_%' ORDER BY nspname")

for s in $schemas; do
  # Each schema records its own version, so the runner can resume where it stopped.
  current=$(psql "$DB" -At -c "SELECT coalesce(max(version), 0) FROM \"$s\".schema_version")
  if [ "$current" -ge "$VERSION" ]; then
    continue
  fi

  # One transaction per tenant: a failure rolls back only this tenant.
  if psql "$DB" -q -v ON_ERROR_STOP=1 <<SQL
BEGIN;
-- Give up on a busy tenant instead of queueing behind its traffic.
SET LOCAL lock_timeout = '5s';
SET LOCAL search_path = "$s";
-- Expand step only. IF NOT EXISTS makes the step safe to re-run.
ALTER TABLE orders ADD COLUMN IF NOT EXISTS currency text;
INSERT INTO schema_version (version) VALUES ($VERSION);
COMMIT;
SQL
  then
    echo "migrated $s"
  else
    # Keep going. Rerun the script later to retry the failed tenants.
    echo "FAILED $s" >&2
    failed=1
  fi
done

# Non-zero exit means part of the fleet is still on the old version.
exit "$failed"

A resumable, idempotent migration runner that applies one expand-only change to each tenant schema in its own transaction, records each schema's version, and skips tenants that are already migrated so a failed run can be repeated safely.

Performance, noisy neighbors, and tenant lifecycle under each model

In shared tables, the planner works from statistics gathered across the whole table, not per tenant. When one tenant owns 40% of the rows and thousands of others own a few hundred each, the estimates can be badly wrong for both groups. The planner may choose an index scan that suits a small tenant and run it against the large one, or the reverse. Three mitigations help:

• Composite indexes led by tenant_id (for example (tenant_id, created_at)). Every tenant-scoped query becomes a narrow range scan, which reduces how much the planner's estimates matter.
• Partitioning by tenant_id, usually by list for the few large tenants and by hash for the long tail. Each partition gets its own statistics, its own vacuum cycle, and can be detached on its own.
• Per-tenant query budgets, such as statement timeouts and rate limits applied in the application layer. These stop one tenant's report from saturating the I/O that every other tenant shares.

Lifecycle operations

Schema per tenant makes deletion and restore close to atomic. Shared tables keep reporting simple.

When to use each model, when to avoid it, and the hybrid path

Start with shared tables and row-level security unless you have a specific reason not to. One schema means one migration run, one set of indexes to tune, and cross-tenant queries that work without extra plumbing. RLS also enforces tenant scoping in the database, so a query that forgets its filter doesn't leak data.

Move an individual tenant to a dedicated schema or database when one of these applies:

• A contract or regulator requires physical isolation.
• Data residency pins the tenant to a region.
• The tenant needs heavy customization that would clutter the shared schema.
• The tenant is large enough to dominate shared resources.

Avoid schema per tenant as the default if your tenant count is high or growing fast, if you ship migrations often, or if a small platform team would have to maintain the tooling that applies, tracks, and retries migrations across every schema.

The hybrid path keeps most tenants in shared tables and moves the exceptions out. It only works if data access goes through a tenant-aware routing layer from day one, so that changing where a tenant lives is a configuration change and not a code change. Without that layer, switching models later is a large migration. You either dual-write while you backfill and reconcile, or you take a cutover window and accept the risk of data left behind.

Impact on development cost and engineering time

Shared tables front-load the work. You design composite keys that lead with tenant_id, write RLS policies, and build a test suite that proves a session scoped to tenant A cannot read or write tenant B's rows. You also need export and deletion tooling that filters every table by tenant, because a tenant's data is spread across the whole database. After that the cost stays flat: one migration, one schema to reason about, and onboarding is an insert.

Schema per tenant moves the effort into operations, and it grows with tenant count. Every DDL change becomes a fan-out job that needs orchestration, retries, and a record of which schemas are on which version. Partial failures create version drift that application code must tolerate until someone fixes it. Catalog size, backup duration, and connection-level schema switching all scale with the number of tenants.

Illustrative estimate (assumed numbers, not a benchmark)

Per-release migration cost = (N × t ÷ p) + (N × f × r)

• N = 2,000 schemas
• t = 3 s per schema
• p = 10 parallel workers
• f = 0.5% failure rate
• r = 15 min of manual triage per failure

That gives 600 s (10 min) of runtime plus 10 failures × 15 min = 150 min of engineer time per release. Shared tables pay this once rather than N times. Large tables may still need online migration techniques, so the shared-table cost is not zero.

Defaulting to shared tables behind a tenant-aware routing layer keeps analytics to a single query and onboarding instant. If one tenant later needs its own schema or database, you move that tenant and update its route. Callers don't change.

Alternatives to consider

Moving a tenant is only one option. Some designs change the boundary entirely, and they trade different costs.

Database per tenant

Each tenant gets its own database, so backups, restores, encryption keys, and data residency apply to one customer at a time. That isolation costs connection capacity. A pool can't be shared across databases, so connections grow with tenant count. Schema migrations also become a fleet rollout that has to track partial failures. This model fits a few large tenants with contractual isolation requirements. It does not fit thousands of small ones.

Partitioning shared tables by tenant

Declarative table partitioning keyed on tenant_id keeps one logical schema while splitting storage physically. Removing a tenant becomes a partition detach instead of a large DELETE, and vacuum and index maintenance stay proportional to each partition. The planner can prune partitions only when queries filter on the partition key. Partitioning by hash or list also does nothing about compute contention.

Sharding on tenant ID

Distributing tenants across nodes, with tenant_id as the shard key, scales shared tables past one machine. Most queries stay single-node because they are scoped to one tenant. Cross-tenant reporting, though, becomes a distributed query, and rebalancing a hot tenant needs a data move.

Cell-based deployment

Run several complete copies of the monolith, each serving a group of tenants, and route requests by tenant. A bad deploy or a runaway tenant then affects one cell instead of every customer. The tradeoff is more environments to provision, monitor, and keep on the same release.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to multi-tenant monolith database design, this is where it helps:

Saves time

• Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
• One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
• Reviewers see the exact diff and the list of affected services up front, so approval is a decision rather than an investigation.

Saves cost

• Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
• Less duplicated configuration to maintain: one shared definition inherited by many services.
• Per-service approve/reject means a bad shared value can be refused where it would break something, before it is live.
• OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.