Shared Schema Looks Simple Until It Isn't

What Does "Multitenant" Even Mean?

A tenant is simply an organization, team, or customer using your application. A multitenant architecture means your single deployment of that application serves multiple tenants simultaneously.

Think of it like an apartment building. The building (your app) is one structure, but each apartment (tenant) is isolated your neighbor can't walk into your unit, even though you share the same walls, plumbing, and roof.

The alternative is a single-tenant architecture, where each customer gets their own completely separate deployment. Some enterprise products do this. It's simpler to reason about, but the operational overhead is brutal you're essentially managing N copies of your entire infrastructure.

Multitenant is how basically every modern SaaS product works: Slack, Notion, Linear, GitHub. One codebase, one deployment, thousands of customers.

The Three Models (and the Tradeoffs Nobody Talks About)

1. Shared Everything: One Database, One Schema

This is the simplest approach. Every tenant's data lives in the same tables, and you add a tenant_id column to distinguish between them.

CREATE TABLE posts (
  id          UUID PRIMARY KEY,
  tenant_id   UUID NOT NULL,  -- The magic column
  title       TEXT NOT NULL,
  content     TEXT,
  created_at  TIMESTAMPTZ DEFAULT now()
);

Every query you write has to include a WHERE tenant_id = ? clause. Your application layer is the only thing separating one customer's data from another's.

Why it's tempting: It's dead simple to start. One migration, one connection pool, no infrastructure complexity. Perfect when you have 10 tenants and want to move fast.

Why it gets scary: The isolation is purely logical. One bug a missing WHERE clause, a copy-paste error in a query and you're serving Tenant A's invoices to Tenant B. It also makes compliance harder. If a customer asks you to delete all their data for GDPR reasons, you're running a DELETE FROM ... WHERE tenant_id = ? across dozens of tables and hoping you didn't miss one.

Who should use it: Early-stage SaaS where you're validating, and you don't yet have enterprise customers demanding data isolation guarantees.

2. Shared Database, Separate Schemas

Instead of a single shared schema, each tenant gets their own schema (a namespace) within the same database. The tables have the same structure, but they're partitioned at the database level.

-- Tenant A's data
CREATE SCHEMA tenant_acme;
CREATE TABLE tenant_acme.posts (...);

-- Tenant B's data  
CREATE SCHEMA tenant_bosch;
CREATE TABLE tenant_bosch.posts (...);

Your application dynamically sets the schema on every connection: SET search_path TO tenant_acme. Now the database itself enforces the boundary not just your application code.

Why it's better: You get actual database-level isolation without the cost of running separate databases. Backups, migrations, and monitoring are still centralized. Running a migration means applying it across schemas, but tools like pg_dump and Flyway have good support for this.

Why it gets painful: You can easily end up with thousands of schemas in a single database instance if you have a lot of tenants. Schema-per-tenant migrations get complicated if you mess up a migration script and it only ran on 800 of 1000 schemas, debugging that is a nightmare. Connection pooling also gets tricky since you need to set search path correctly per session.

Who should use it: Mid-stage SaaS where you're starting to get customers who care about compliance and isolation, but you're not ready to manage separate database infrastructure per tenant.

3. Separate Database Per Tenant

The strictest model. Each tenant gets a completely isolated database instance.

acme.yourapp.com  → acme_db (Postgres instance A)
bosch.yourapp.com → bosch_db (Postgres instance B)

Your application holds a connection string per tenant (usually fetched from a config store or a "master" database) and routes every request to the right database.

Why it's the gold standard for enterprise: The isolation is absolute. No shared infrastructure means one tenant's slow queries can't impact another's performance. Compliance is much cleaner deleting a tenant's data means dropping a database. You can also run different database versions or configurations per tenant if needed.

Why most teams avoid it early: The operational complexity is real. Provisioning a new tenant now means spinning up infrastructure, not just inserting a row. Connection pools multiply. Running migrations means running them across every database that's infrastructure automation work, not just app work. Costs scale linearly with tenants.

Who should use it: Enterprise-focused products where customers have strict data residency requirements, or you're charging enough per customer that separate infrastructure is justified.

The Part Everyone Skips: Tenant Resolution

Before any of the database isolation strategies matter, your application needs to figure out which tenant is making a request. There are three common approaches:

Subdomain-based: acme.yourapp.com → extract acme from the host header, look up the tenant. This is the cleanest UX and the most common approach for B2B SaaS.

Path-based: yourapp.com/acme/dashboard → extract the slug from the URL path. Simpler to implement (no DNS wildcard needed), but looks less polished.

JWT claim: The tenant ID is embedded in the auth token at login. On every request, you verify the token and extract the tenant ID from the claims. Great when you're building an API-first product.

Most production systems use a combination: subdomain for routing, JWT for enforcement.

Row-Level Security: The Underrated Safety Net

If you're going with the shared schema approach, Postgres's Row-Level Security (RLS) is worth knowing about. Instead of relying on your application to attach tenant_id to every query, you define policies at the database level that enforce it automatically.

-- Enable RLS on the table
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- Create a policy using a session variable
CREATE POLICY tenant_isolation ON posts
  USING (tenant_id = current_setting('app.current_tenant_id')::UUID);

Now you set the session variable at connection time, and the database itself rejects any query that tries to access rows for a different tenant even if your application code forgot to add the WHERE clause. It's a second layer of defense that's saved teams from serious incidents.

Migrations: The Hardest Part at Scale

Pick any multitenant model and migrations will eventually be the thing that keeps you up at night.

In a single-tenant world, you run one migration and call it done. In a multitenant world:

Shared schema: One migration, but it touches every tenant's data simultaneously. A bad migration is a bad day for all customers at once.
Schema-per-tenant: You run migrations sequentially across all schemas. If you have 500 tenants, that's 500 schema updates. You need rollback strategies and the ability to detect which schemas succeeded.
Database-per-tenant: Same as above but across database instances, often across different regions or cloud accounts.

The pattern that works: treat migrations as a background job, not a deployment step. Maintain a migration state table, run migrations asynchronously, and build tooling to inspect which tenants are on which schema version. It's boring infrastructure work, but it's the difference between a smooth upgrade and a 3am incident.

Picking the Right Model

There's no universally correct answer, but here's a simple decision framework:

Signal	Recommended Model
You're pre-PMF, moving fast	Shared schema
You have >50 tenants and some compliance needs	Schema-per-tenant
Enterprise deals, data residency requirements	Database-per-tenant
You have a mix of SMB and enterprise customers	Hybrid (shared schema for SMB, dedicated DB for enterprise)

The hybrid model is what a lot of mature SaaS products actually run. Your standard plan tenants share infrastructure, while enterprise customers get dedicated resources at a premium price point. The complexity is real, but the economics usually justify it.

Wrap Up

Multitenancy is one of those architectural decisions you make early and live with for years. The good news is you don't have to get it perfect on day one starting with shared schema and migrating to stricter isolation as you grow is a completely valid path, as long as you design your data access layer to make that migration possible.

The worst thing you can do is scatter raw SQL with inline tenant IDs across your entire codebase. Keep tenant resolution and data scoping in one layer a service, a middleware, a database policy so when you do need to change the model, you're changing code in one place instead of hunting down WHERE tenant_id = across 200 files.

Get the isolation layer right early, even if the underlying strategy is simple. Your future self (and your future enterprise customers) will thank you.