How to Design a Multi-Tenant SaaS Database in Postgres
Your SaaS product just signed its tenth paying customer. Now tenant A's data must never bleed into tenant B's queries — and you need to sleep at night knowing that. The database architecture decision you make today will either scale cleanly to a hundred tenants or quietly accumulate technical debt that costs twice as much to fix later.
PostgreSQL gives you three credible paths: shared tables with Row-Level Security (RLS), schema-per-tenant isolation, and database-per-tenant separation. Each sits at a different point on the spectrum of cost, operational complexity, query performance, and data leakage risk. Here is how to reason through them honestly.
The Three Models at a Glance
| Model | Isolation Level | Ops Complexity | Cloud Cost | Leakage Risk |
|---|---|---|---|---|
| Shared tables + RLS | Logical | Low | Lowest | Medium |
| Schema per tenant | Logical + structural | Medium | Low-Medium | Low |
| Database per tenant | Physical | High | Highest | Very Low |
None of these is universally correct. Your choice should follow your tenant count, your compliance requirements, and — especially for African SaaS teams running on AWS Lightsail, DigitalOcean, or Render — your compute budget.
Option 1: Shared Tables With Row-Level Security
In this model every tenant's data lives in the same tables. A tenant_id column tags every row, and Postgres RLS policies enforce that a session can only see rows matching its own tenant context.
-- Enable RLS on the orders table
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
-- Policy: a session may only see its own tenant's rows
CREATE POLICY tenant_isolation ON orders
USING (tenant_id = current_setting('app.current_tenant_id')::uuid);
-- In your application connection pool, set the tenant context on each session
SET app.current_tenant_id = '3f7a1b2c-...';
Where it wins: One schema to migrate. One connection pool to manage. Operational overhead is minimal — exactly what a two-person engineering team needs. Storage costs stay low because indexes are shared across the dataset.
Where it hurts: RLS is only as strong as your discipline in setting the session variable. A missed SET call, a raw query that bypasses the ORM, or a superuser connection that ignores RLS altogether can silently expose cross-tenant data. For regulated industries — fintech, health tech, or any product handling PII — this risk profile is genuinely uncomfortable without rigorous application-layer enforcement and audit logging.
Performance is the other concern. On a table with ten million rows across five hundred tenants, even a well-indexed tenant_id scan will compete with neighbours for buffer cache. Noisy tenants degrade the experience for everyone else.
Option 2: Schema Per Tenant
Each tenant gets its own Postgres schema (tenant_abc.orders, tenant_xyz.orders). Tables are structurally identical, but the namespacing provides a clean boundary. Your application sets search_path at connection time.
Where it wins: Cross-tenant data leakage through application bugs becomes structurally harder — a query on the wrong search_path hits an empty schema, not another tenant's data. Migrations can be rolled out per tenant, which is valuable when enterprise customers negotiate custom data retention or schema extensions.
Where it hurts: Schema proliferation becomes a management headache above roughly 500–1,000 tenants. Running ALTER TABLE across 800 schemas is not a migration — it is a ceremony. Tools like pg_dump, monitoring agents, and ORMs that enumerate schemas all behave differently at scale. Connection pooling via PgBouncer also requires more care because search_path must be set correctly per session.
On a constrained cloud budget, schema isolation still runs on a single Postgres instance, so the cost delta versus shared tables is almost entirely operational time rather than infrastructure spend.
Option 3: Database Per Tenant
Every tenant gets a fully independent Postgres database — potentially on a separate instance. This is what large enterprise SaaS products offer as their "dedicated" tier.
Where it wins: Physical isolation is the highest standard available. A compromised tenant database does not touch others. Backups, restores, and failovers are scoped cleanly. Compliance teams love it.
Where it hurts: The cost curve is brutal. Even on managed Postgres (RDS, Supabase, Neon), each isolated instance carries a baseline compute and storage cost. At 50 tenants you may be running 50 small DB instances — that is real money on a startup runway. Operational complexity scales linearly: 50 migration pipelines, 50 backup schedules, 50 monitoring targets.
This model makes sense as a premium upsell tier or for a small number of high-value enterprise customers, not as a default architecture.
A Practical Decision Framework for SaaS Teams
Ask yourself these four questions before committing:
- How many tenants do you expect in 18 months? Under 200, any model is manageable. Over 1,000, schema-per-tenant starts to strain and shared-table RLS becomes attractive again.
- What is your compliance exposure? If you handle financial transaction data or health records under local regulatory frameworks (Bank of Ghana guidelines, NDPA in Nigeria, POPIA in South Africa), physical or structural isolation reduces audit risk significantly.
- What is your team's Postgres fluency? RLS is powerful but unforgiving. If the team is still growing into Postgres, schema isolation with disciplined
search_pathmanagement is often safer in practice. - Do you need per-tenant customisation? If enterprise customers will ask for custom columns, different retention windows, or tenant-specific indexes, schema isolation pays dividends. Shared tables make per-tenant schema divergence nearly impossible to maintain cleanly.
Hybrid Approaches Are Legitimate
Many mature SaaS products land on a tiered model: small and mid-market tenants share a table-per-schema setup on a single instance, while enterprise tenants get a dedicated database. This lets you optimise infrastructure cost at the low end while offering a credible isolation story at the high end — a meaningful sales lever.
The key is to build the abstraction layer in your application so that switching a tenant from shared to dedicated is an operational procedure, not a code change.
Choosing the Right Index Strategy
Whichever model you choose, composite indexes that lead with tenant_id are non-negotiable in shared-table designs.
-- Always lead with tenant_id in composite indexes on hot tables
CREATE INDEX idx_orders_tenant_created
ON orders (tenant_id, created_at DESC);
In schema-isolated designs, indexes are per-schema and can be tuned independently — a genuine advantage for tenants with unusual query patterns.
Why This Matters for Your Project
The multi-tenancy decision is one of the few architectural choices that is genuinely expensive to reverse. Getting it wrong means either a costly re-platforming exercise or a data incident that damages customer trust you spent months building. For SaaS teams building in Ghana and across Africa — often on lean infrastructure budgets with investors watching unit economics closely — the right model is the one that minimises leakage risk and operational overhead at your current scale, with a clear upgrade path as the tenant base grows. Design the abstraction layer now, and the migration later becomes a controlled operation rather than a crisis.
