How to design a multi-tenant Postgres schema

The default multi-tenant Postgres schema is a shared database with a tenant_id column on every tenant-scoped table, a composite index of (tenant_id, ...) on every query path, and Row Level Security policies that pin each connection to one tenant. That pattern carries you from your first paying customer to roughly 5,000 tenants on a single Postgres instance without architectural changes. Pick a different pattern only when compliance, noisy-neighbor risk, or per-tenant restore requirements force your hand.

That is the answer most teams need. The longer answer is that there are four real patterns, each with a different operating cost, blast radius, and migration story. Picking the wrong one early is expensive, but picking the most complex one upfront is more expensive.

Why this matters more in 2026 than it did in 2021

Three things changed. Cloud Postgres got cheap enough that small teams now ship multi-tenant SaaS on managed Neon, Supabase, or RDS from day one instead of self-hosting. Row Level Security stopped being a niche feature and became the default isolation primitive in every serious SaaS template. And LLM-generated query traffic broke a lot of naive tenant_id indexes by issuing wider, more variable queries than human-written SQL ever did.

If you are launching a SaaS this year, you are building on a stack where the schema choice is the most expensive one to undo and the cheapest one to get right. A two-hour design conversation at the start saves a six-month migration at month 18.

The four real patterns

Every multi-tenant Postgres design is a variant of one of four shapes:

Pattern	Best for tenant count	Hosting cost profile	Ops burden	Per-tenant blast radius
Shared schema, tenant_id column	1 to ~5,000	Lowest. One DB, one connection pool.	Lowest. One migration runs everywhere.	Highest. One bad query can degrade everyone.
Schema-per-tenant	~10 to ~500	Medium. One DB, many schemas. Pooling gets tricky.	Medium. Migrations fan out per schema.	Medium. Locks on pg_catalog start to bite past 1k schemas.
Database-per-tenant	~5 to ~200	Highest per tenant. One DB instance or one logical DB each.	Highest. Per-DB migrations, per-DB backups.	Lowest. Full physical isolation.
Hybrid (pools of shared DBs)	1,000+ with enterprise tier	Mid-high. Pool-aware routing layer.	High. Tenant placement, rebalancing.	Tunable. Big tenants get isolation; long tail shares.

Read that table twice before reading anything else. The pattern you pick is the constraint that defines the next two years of your engineering work.

Pattern 1: Shared schema with tenant_id (the default)

Every tenant-scoped table gets a tenant_id uuid not null column. Every query filters by it. Every index is composite, leading with tenant_id. Row Level Security pins each request-scoped database connection to one tenant id, so a missing WHERE tenant_id = $1 in application code is caught by the database, not by your customers.

This is what Linear, Notion's early architecture, PostHog Cloud, and most YC SaaS run on. It is boring, well-understood, and scales further than people expect. The failure modes are well-known: index bloat from misordered columns, a single noisy tenant burning CPU for everyone, and the moment when one enterprise customer asks for "their own database" in their SOC 2 questionnaire.

Pattern 2: Schema-per-tenant

One Postgres database. One Postgres schema per tenant. Tables look like acme.users, globex.users. The search_path is set per connection so application code stays unaware of which tenant it is hitting.

This buys you logical separation without paying for separate instances. Backups are still one pg_dump. Connection pooling still works through PgBouncer. The catch is that pg_catalog operations get slow past about 1,000 schemas, and any cross-schema migration script has to iterate. Run a ALTER TABLE across 800 schemas during business hours once and you will understand why teams move off this pattern when they cross the 500-tenant line.

It is a good fit if you already know your tenant ceiling is in the low hundreds, for example a niche B2B vertical with 200 named accounts on the roadmap.

Pattern 3: Database-per-tenant

One physical or logical Postgres database per tenant. Each gets its own backup schedule, its own connection limits, its own credentials. A noisy tenant cannot affect any other tenant because they are not on the same instance.

This is what Heroku Connect, some healthcare SaaS, and several enterprise-only products run on. It is the only pattern that gives you a clean answer to "can you restore one customer's data to last Tuesday without touching anyone else?" The honest cost is that you now operate a fleet. You need a control plane that provisions databases, runs migrations against all of them with a rollout strategy, monitors them individually, and has a clear story for the long tail of small tenants who do not justify a dedicated instance.

Per-tenant cost on managed Postgres starts around $15-25/month at the smallest tier, which becomes the floor on your customer LTV.

Pattern 4: Hybrid (pools of shared DBs)

Run several shared-schema databases. Place each tenant in one of them based on size, plan, or compliance flag. Enterprise tenants get their own database. Free and starter tenants share a pool. A routing table (often in a separate metadata Postgres) tells the application which shard to connect to for a given tenant.

This is what GitHub, Shopify, Atlassian Cloud, and Figma operate at scale. You should not start here. You should arrive here, somewhere between tenant 1,000 and tenant 10,000, after a shared-schema design has proved out the product. The work involved in retrofitting hybrid sharding onto a healthy shared-schema codebase is real but tractable, often shipped by a single Senior engineer in a 4-6 week project, plus a backfill migration.

Row Level Security: column-level and row-level isolation

If you pick shared-schema, RLS is non-optional in 2026. The pattern looks like this on the database side:

alter table invoices enable row level security;

create policy invoices_tenant_isolation on invoices
  using (tenant_id = current_setting('app.tenant_id')::uuid);

On the application side, every connection checked out of the pool runs set local app.tenant_id = '...' inside the transaction before any query. If app code forgets the WHERE tenant_id, Postgres still filters. If a misconfigured background job runs without the setting, no rows come back at all.

For column-level secrets (encryption keys, billing fields, PII), combine RLS with a separate tenants_secrets table that requires a second elevated role, or push those fields into a dedicated secret store. For the full RLS policy patterns, role separation, and the pooler gotchas in detail, the deep dive in Postgres RLS for SaaS is the companion read to this section, and the playbook in how to handle secrets in production translates directly to a multi-tenant setup.

One gotcha: superusers and table owners bypass RLS by default. Your application role should never be the table owner. Create a separate migration role that owns the schema and an application role that does not.

Indexing the tenant_id correctly

The most common indexing mistake is putting tenant_id last in a composite index, or worse, indexing it alone. The right rule is simple: tenant_id goes first in almost every composite index on a tenant-scoped table.

-- Wrong: a single-column index on tenant_id alone is rarely useful past 50 rows per tenant.
create index on invoices (tenant_id);

-- Wrong: tenant_id last means the planner cannot use it to prune.
create index on invoices (status, created_at, tenant_id);

-- Right: tenant_id first so the planner can pin the scan to one tenant's rows.
create index on invoices (tenant_id, status, created_at desc);

For tables where a single tenant has many millions of rows (event logs, audit trails, message bodies), partition by tenant_id using Postgres declarative partitioning, or move that table to a dedicated TimescaleDB hypertable. Both buy you per-tenant pruning at the planner level.

If you are testing this, write the test suite using the patterns in how to write production-grade tests in 2026. A query that returns one tenant's rows by accident is a P0 incident, and the test for it is a five-line check that runs in CI.

Migration patterns that survive contact with paying customers

The fundamental hard part of multi-tenant migrations is that a schema change runs once for shared-schema, N times for schema-per-tenant, and N times across N databases for database-per-tenant. Plan the rollout, not just the SQL.

Three patterns work in practice:

1. Expand-contract for shared schema. Add the new column nullable. Backfill in batches. Add the not-null constraint last. Drop the old column in a separate release. This is the only pattern that survives a multi-hour backfill without taking the app down.

2. Per-tenant migration runners for schema-per-tenant and database-per-tenant. Build a worker that iterates the tenant list, runs the migration against each, records success or failure, and supports resuming from the failure point. Never run a script that does for tenant in tenants: migrate(tenant) synchronously across 500 tenants at 2pm on a Tuesday.

3. Online schema changes for the largest tables. For hundreds of millions of rows per tenant, use pg_repack or the CONCURRENTLY family of statements. Lock-waiting on a 200GB invoices table will time out every web request for whichever tenant owns it.

The same care applies to anything that touches schema during a feature ramp; the gates described in how to roll out feature flags safely are the right harness for schema-touching changes too.

Common pitfalls

Five patterns we see go wrong, paired with the symptom they produce in production:

• Forgetting RLS on a new table. Symptom: a customer sees another customer's data in a new feature within hours of shipping. Catch it with a migration linter that fails CI when any create table statement is committed without enable row level security.
• Connection pooler that pools across tenants. Symptom: app.tenant_id from one request leaks into another because PgBouncer's transaction pooling reuses sessions. Fix: set tenant id inside the transaction with set local, never set.
• Cross-tenant joins by accident. Symptom: an analytics endpoint runs select count(*) from invoices without RLS context and silently returns rows for everyone, then the result lands in a customer's dashboard. Fix: a single non-RLS analytics role, used only by jobs that explicitly want cross-tenant data.
• select queries that ignore the composite index. Symptom: query latency creeps up linearly with total customer count, not per-tenant row count. Fix: enable pg_stat_statements, sort by mean time, find the queries that lack tenant_id in the planner's chosen index, rewrite.
• One enterprise tenant ten times the size of everyone else. Symptom: that one tenant's queries dominate the buffer cache and degrade every other tenant. Fix: move them to their own database in a hybrid setup. This is the typical trigger for graduating from Pattern 1 to Pattern 4.

When something does go wrong here, the discipline in how to write a postmortem after an incident is what turns a cross-tenant leak into a one-time event instead of a recurring one.

When you can skip most of this

If you are pre-revenue with two founders shipping the first prototype, you do not need RLS on day one. A tenant_id column on five tables and a coding convention is enough for the first 20 customers. Add RLS the day you take a credit card from someone whose security review you would not pass without it.

If your product is single-tenant by design (a deployed-per-customer app, an open-source self-hosted tool, a desktop sync engine), none of this applies. Use a normal Postgres schema and ship.

The pattern decision matters when you cross any of three lines: handling regulated data, signing your first enterprise contract, or crossing about 100 active paying tenants. Before that, the answer is almost always Pattern 1.

How Cadence engineers usually approach this

Most multi-tenant migrations we ship at Cadence are mid-stage SaaS teams moving from Pattern 1 to Pattern 4, or healthcare and fintech teams that need to bolt RLS plus a per-tenant audit trail onto a six-month-old codebase. These are scoped, four-to-eight week engagements that a Senior engineer ($1,500/week) handles end-to-end: schema design, migration plan, online backfill, the rollout, the runbook, and the postmortem template.

Every engineer on Cadence is AI-native by default, vetted on Cursor and Claude Code fluency in a voice interview before they unlock bookings, so the schema-design conversation moves at the speed of a paired session rather than a week of doc back-and-forth. If your team has a multi-tenant schema decision in front of you and nobody internally has done it before, auditing the current stack on Ship-or-Skip is a sensible 20-minute first step before booking anyone.

For the rarer cases (greenfield database-per-tenant control planes, Postgres-to-Citus or pgvector sharding work, fractional CTO oversight on a regulated build), the Lead tier ($2,000/week) is the right call.

Need a vetted engineer for a multi-tenant schema rollout this week? Book a Senior engineer on Cadence in 2 minutes with a 48-hour free trial; you only pay if the first 2 days are useful, weekly billing, no notice period.

FAQ

When should I move from shared-schema to database-per-tenant?

When one of three things is true: a paying enterprise contract requires physical isolation, a single tenant generates more than 20% of your total query load, or your compliance regime (HIPAA, certain SOC 2 controls, regional data residency) requires per-tenant restoration. Before any of those, the operational cost of database-per-tenant is not worth the isolation.

Does Row Level Security hurt query performance?

It adds a small planning-time cost (typically under 2%) when the policy is a simple equality on an indexed column. It hurts noticeably when the policy involves a subquery or a function call. Keep policies trivial: using (tenant_id = current_setting('app.tenant_id')::uuid) is the sweet spot.

How do I handle a tenant that needs custom fields?

Add a jsonb column called custom_fields to the relevant table. Index specific keys with expression indexes only when a tenant's query patterns demand it. Avoid per-tenant ALTER TABLE statements; they turn your schema into N divergent shapes and break every migration after.

Can I mix patterns in one product?

Yes, and most large SaaS do. Shared-schema for the core OLTP tables, database-per-tenant for the audit log or event store, and a separate read-replica per large tenant for analytics. The hybrid pattern is what this evolves into. Plan for it; do not let it happen by accident.

What about pg_partman and partitioned tables for huge tenants?

pg_partman plus declarative partitioning by tenant_id is the right move when one tenant has more than ~50 million rows in a single table. It gives you per-tenant pruning at the planner level and lets you detach an old tenant's partition cleanly when they churn. Set it up before you need it; retrofitting partitioning onto a hot table is its own multi-week migration.