Multi-Tenant PostgreSQL Data Model Template: Shared Schema, RLS & Migration Path

Multi-Tenant PostgreSQL Data Model - Annotated with Migration Path

Three years of production SaaS experience compressed into one document and a set of SQL files you can actually run.

PostgreSQL Multi-tenancy Row-Level Security SaaS Architecture Migration Indexes Shared Schema

The multi-tenant data model decision is one of the few SaaS architecture choices that's cheap to make and very expensive to change. Most teams start with shared schema because it ships fastest. That's fine - it's often the right call. The problem is that the migration path out of it, when you eventually need schema or database isolation, is significantly harder than teams realise when they commit to shared schema on day one. This template gives you the shared-schema setup done right, including the RLS policies and index configurations that prevent the performance problems that typically force the migration conversation, plus the documented migration path so you understand what you're signing up for.

What's inside

The document is structured into 6 sections. Each is self-contained - you can use individual sections as standalone references or work through the document in sequence.

Shared schema baseline

Complete SQL for a production-ready shared-schema setup: tenant table, RLS policy templates, the specific GIN and B-tree index combinations that keep queries fast at 5,000+ tenants, and the connection pool configuration that works with PgBouncer in transaction mode.

RLS policy patterns

Annotated policy templates for read, write, and delete isolation. Includes the superuser bypass configuration that's easy to miss and the test setup that verifies your RLS actually works under a non-superuser role - the gap that lets bugs through in most CI pipelines.

Performance index guide

The specific composite index patterns for tenant-scoped queries, partial indexes for common filter combinations, and the query patterns that look fine in testing but degrade at scale - with the index changes that fix each one.

Migration path: shared → schema-per-tenant

A documented, step-by-step migration approach covering schema creation, data copy order (foreign key constraints matter), cutover window management, application-layer changes, and the validation framework we use to confirm data integrity post-migration.

Application layer checklist

The ORM and query layer changes required to support schema routing - the part that's often larger than the database migration itself. Covers Django, Rails, and raw psycopg2/node-postgres patterns.

When to migrate - decision guide

A decision framework with specific signals that indicate shared schema is approaching its limits for your product, and a rough effort estimate for migration at different tenant counts.

What this doesn't cover

This template covers PostgreSQL on self-managed instances and managed services (RDS, Cloud SQL, Supabase). It does not cover CockroachDB, PlanetScale, or NoSQL multi-tenancy patterns. Database-per-tenant architecture is described but not templated - it requires per-tenant provisioning automation that's specific to your infrastructure.

Who this is for

SaaS engineers choosing a multi-tenancy model for a new product

Platform teams evaluating whether to migrate from shared schema to schema isolation

Engineering leads preparing for enterprise customer sales that require isolation guarantees

Backend engineers who inherited a shared-schema setup and need to understand its limits

How it was built

Built from three production SaaS engagements where we ran the migration from shared schema to schema-per-tenant. Updated January 2025 to include Supabase RLS configuration and Prisma multi-schema patterns.

Every resource Sequere publishes is written by the engineers who ran the actual engagement - not by a content team working from secondhand notes. The trade-off is that we publish less frequently. The benefit is that the specifics are real.

Download

This resource is free. Enter your email address and the download link is sent immediately - no marketing sequences, just the download.

If you use this resource on a real project and have feedback - things that were missing, out of date, or wrong - we want to hear it. Every update to this document has come from people who used it in production.