0x55aa
← Back to Blog

Multi-Tenancy Architecture: One Database or Many? The Decision That Will Haunt You 🏗️🏢

•8 min read
"architecture""scalability""system-design""multi-tenancy""saas"

Multi-Tenancy Architecture: One Database or Many? The Decision That Will Haunt You 🏗️🏢

Unpopular opinion: The most consequential architecture decision in a SaaS product isn't your framework, your cloud provider, or even your programming language.

It's how you store data for multiple customers.

Make the wrong call and you'll be rewriting database schemas under production load at 2am three years from now. I know because I did exactly that. 🙃

When designing our e-commerce backend to serve hundreds of merchants — each with their own products, orders, customers, and inventory — we had to pick a tenancy strategy in week 1. We picked based on vibes. We paid for it in blood (and AWS bills).

Let me save you that pain.

What Even Is Multi-Tenancy? 🤔

Multi-tenancy means one instance of your application serves multiple customers ("tenants"), and each tenant's data needs to be isolated, secure, and independently manageable.

Think: Shopify serving thousands of stores on shared infrastructure, or Slack running thousands of workspaces from one platform.

The core tension is always: isolation vs. efficiency.

More isolation = more resources, more complexity, more cost. Less isolation = cheaper, faster — until one tenant's query tanks everyone's performance.

The Three Strategies (And Their Dark Sides) ☠️

Strategy 1: Row-Level Isolation — One Big Happy Table 🍽️

All tenants in the same database, same schema. Every table has a tenant_id column.

┌───────────────────────────────────────────────────┐
│                   products table                  │
├────────────┬──────────────┬──────────┬────────────┤
│ tenant_id  │ product_id   │ name     │ price      │
├────────────┼──────────────┼──────────┼────────────┤
│ merchant-1 │ prod-abc     │ T-Shirt  │ 29.99      │
│ merchant-2 │ prod-def     │ Mug      │ 14.99      │
│ merchant-3 │ prod-ghi     │ Sticker  │ 3.99       │
└────────────┴──────────────┴──────────┴────────────┘
       ↑
  ONE table, everybody's data

// In Laravel: every query must scope to tenant
$products = Product::where('tenant_id', $currentTenant->id)->get();

The good:

  • Simple to set up — one schema, one migration for everyone
  • Cheap — no per-tenant provisioning
  • Reporting across tenants is trivial
  • Adding a new tenant is just a database row

The nightmare:

  • You WILL forget the tenant_id filter somewhere. And when you do, Merchant A sees Merchant B's orders. This is a GDPR incident waiting to happen.
  • One noisy tenant with a table scan query slows down everyone else
  • Backup and restore is all-or-nothing — can't easily restore just one tenant's data
  • Enterprise clients will ask "where is my data?" and the answer "mixed in with everyone else" does not inspire confidence

As a Technical Lead, I've learned: Row-level isolation is a trap for early-stage products. It feels fast and easy until you have your first security audit or your first enterprise prospect asking for a data isolation guarantee.

We had a bug where a missing where tenant_id = ? clause let two merchants see each other's order histories for 4 hours. Not fun to explain to anyone.

Strategy 2: Shared Database, Separate Schemas 📦

One database, but each tenant gets their own schema namespace.

┌────────────────────────────────────────────────────────────┐
│                    PostgreSQL Database                      │
│                                                            │
│  ┌──────────────────┐    ┌──────────────────┐             │
│  │  merchant_1 schema│    │  merchant_2 schema│  ...       │
│  │  ── products      │    │  ── products      │            │
│  │  ── orders        │    │  ── orders        │            │
│  │  ── customers     │    │  ── customers     │            │
│  └──────────────────┘    └──────────────────┘             │
│                                                            │
└────────────────────────────────────────────────────────────┘

In PostgreSQL, switching schemas per request looks like:

// Set search path at connection time
DB::statement("SET search_path TO merchant_{$tenantId}");

// Now all queries hit merchant_1.products, not public.products
$products = Product::all(); // No tenant_id filter needed!

The good:

  • No tenant_id columns — impossible to accidentally cross-contaminate queries
  • Schema-level isolation feels cleaner
  • Per-tenant backups are straightforward (pg_dump --schema=merchant_1)
  • Works great for hundreds to low thousands of tenants

The honest downsides:

  • Schema migrations become painful — ALTER TABLE products ADD COLUMN needs to run for every schema. With 500 merchants, that's 500 migrations. Hope you have good tooling.
  • PostgreSQL has shared connection pool overhead — 500 schemas in one database still shares buffer cache
  • Cross-tenant reporting requires UNION queries across schemas. Fun to write once. Horrifying at scale.
  • Still sharing one database — noisy neighbor problem for heavy read workloads

A scalability lesson that cost us: We chose this strategy at 200 merchants. At 800 merchants, schema migrations during deploys took 45 minutes. We had to migrate to a dedicated tenant migration queue that ran in the background. Always think about day-1000, not just day-1.

Strategy 3: Database Per Tenant — Full Isolation 🏰

Each tenant gets their own database (or database cluster).

┌──────────────────────────────────────────────────────────┐
│                      Your App                            │
│              (Tenant Router Middleware)                  │
└───────────┬─────────────────┬────────────────┬──────────┘
            │                 │                │
            ▼                 ▼                ▼
    ┌───────────────┐ ┌───────────────┐ ┌───────────────┐
    │  merchant-1   │ │  merchant-2   │ │  merchant-3   │
    │  RDS MySQL    │ │  RDS MySQL    │ │  RDS MySQL    │
    └───────────────┘ └───────────────┘ └───────────────┘
     Asia-Pacific      US-East-1         EU-West-1

The good:

  • Maximum isolation — one tenant's data literally cannot touch another's
  • Enterprise and regulated industries (healthcare, finance) often require this
  • Per-tenant scaling — upgrade one merchant's database independently
  • Backup/restore is trivially tenant-scoped
  • Can place tenant data in their required geographic region

The reality check:

  • Expensive. 200 tenants = 200 databases = 200 RDS instances. AWS bills hurt.
  • Schema migrations multiplied by tenant count (same as schema-per-tenant but with more moving parts)
  • Cross-tenant analytics requires an ETL pipeline or data warehouse
  • Connection pool management becomes a project in itself — you can't maintain a live connection to 500 databases simultaneously

When designing our e-commerce backend for enterprise merchants with GDPR requirements in different regions, we eventually moved our top-tier customers to dedicated databases. For a €299/month plan, a shared-schema database is fine. For a €5,000/month enterprise plan, "your data is physically separate" closes deals.

How to Pick? The Honest Decision Matrix ⚖️

┌─────────────────────────────────────────────────────────────────┐
│                 How many tenants? How much money?               │
├───────────────────────┬─────────────────────────────────────────┤
│  < 100 tenants        │  Row-level isolation is fine            │
│  Startup / free tier  │  (Just don't forget tenant_id filters!) │
├───────────────────────┼─────────────────────────────────────────┤
│  100 - 5,000 tenants  │  Schema-per-tenant in PostgreSQL        │
│  Growing SaaS         │  (Invest in good migration tooling)     │
├───────────────────────┼─────────────────────────────────────────┤
│  Enterprise / regulated│ Database-per-tenant                    │
│  High compliance needs │ (Or hybrid: shared + dedicated tier)   │
└───────────────────────┴─────────────────────────────────────────┘

The pattern I now recommend: Start with row-level isolation, but build a clean abstraction layer (TenantScope, middleware that sets context) from day 1. When you need to graduate to schema-per-tenant, you swap the implementation, not the entire application.

The Hybrid Model (What We Actually Do Now) 🎛️

After our painful migration journey, here's what we run for our e-commerce platform:

  • Starter merchants (< 10,000 orders/month) → row-level isolation, shared database
  • Growth merchants (> 10,000 orders/month) → dedicated schema in a shared cluster
  • Enterprise merchants (custom contract) → dedicated RDS instance, BYOK encryption, their region

One codebase. A TenantConnectionResolver that picks the right database connection at request time based on the tenant's tier. The tenant doesn't know which tier they're on — the infrastructure changes under their feet.

Common Mistakes I Made First 🪤

Mistake #1: No global tenant context

Passing tenant_id to every function manually. Missed it in two service classes. Leaked data. Never again — use a global context object set by middleware.

Mistake #2: Ignoring the migration problem

Our first schema-per-tenant setup had no plan for running migrations. We ended up with a shell script that ran Artisan migrations in a loop. It failed silently on tenant #47. We discovered it 3 weeks later.

Mistake #3: Building cross-tenant reports against the live database

A merchant-analytics query that joined across all tenant schemas nearly brought down production. Analytics go to a read replica or a data warehouse. Always.

TL;DR ⚡

Multi-tenancy is a spectrum from "cheap and risky" to "expensive and safe":

  1. Row-level — Fast to build, dangerous at scale, never forget the WHERE tenant_id = ?
  2. Schema-per-tenant — Great middle ground, invest in migration tooling early
  3. Database-per-tenant — Maximum isolation, enterprise-grade, your AWS bill will look like a phone number

Pick based on your compliance requirements, tenant count, and how much you trust your team not to forget a filter clause.

The rule I live by: Build the abstraction layer that lets you change your tenancy strategy without changing your business logic. Future-you will send a thank-you note.

Dealing with multi-tenancy in production? Hit me up on LinkedIn — I've got war stories and working migration scripts I'm happy to share.

Want to see the TenantConnectionResolver code? Check GitHub for the pattern we use to dynamically route connections in Laravel.

Now go isolate your tenants before they isolate you. 🏗️🏢

← Back to all posts