StackOS Community Q&A • best-practice
What is the best way to handle multi-tenant SaaS database architecture?
We're building a multi-tenant B2B SaaS application. Expected: 500 tenants, 10K users total within 2 years. Each tenant needs data isolation. Three approaches we're considering: 1. Shared database, shared schema (tenant_id column) 2. Shared database, separate schemas per tenant 3. Separate database per tenant We're using PostgreSQL. What are the pros, cons, and scaling limits of each approach?
Top Community Answer
We built a multi-tenant platform serving 2,000+ tenants on PostgreSQL. Here's our real-world experience: **Option 1: Shared DB, Shared Schema (tenant_id)** - Scaling limit: ~5,000-10,000 tenants before queries slow down - Pros: Simplest to manage, easy cross-tenant queries, lowest cost - Cons: Row-level security is mandatory, tenant data leakage risk, backup granularity is limited - Best for: Early-stage with <1,000 tenants **Option 2: Shared DB, Separate Schemas** - Scaling limit: ~1,000-2,000 tenants (connection pool limits) - Pros: Better isolation, can backup per-schema, moderate complexity - Cons: Schema migrations are painful (need to run per-tenant), connection pooling required (PgBouncer), cross-tenant queries need dblink - Best for: Medium stage with moderate isolation needs **Option 3: Separate Databases** - Scaling limit: Virtually unlimited (add more DB servers) - Pros: Strongest isolation, per-tenant backups, can scale horizontally - Cons: Highest cost and ops complexity, cross-tenant queries impossible, schema migrations need coordination - Best for: Enterprise SaaS with strict compliance requirements **Our recommendation for your case:** Start with Option 1 (shared schema + RLS). At 500 tenants, it will perform well. Design your schema with RLS from day one. Add a tenant_id column to every table. When you hit ~2,000 tenants or need stronger isolation, migrate to Option 3 using a schema-per-DB approach. Critical: Use Citus (Citus extension for PostgreSQL) if you go with Option 1 and need to scale beyond 5K tenants. It distributes data by tenant_id automatically.