Multi Tenant SaaS Database Design

By Kokil Thapa | Last reviewed: September 2026

Multi Tenant SaaS Database Design is the decision that outlives your first feature set. Pick the wrong isolation model and you either bleed margin on per-tenant infrastructure or spend years patching query leaks. On production Laravel applications I've maintained since 2010, the database layer—not the UI—determines whether a SaaS product survives its first hundred tenants. This guide walks through the three core patterns, the schema and indexing rules that keep shared databases safe, and the operational habits that prevent a migration from taking down every customer at once. If you are comparing application-layer approaches, start with our guide to building multi-tenant SaaS applications in Laravel.

What are the main Multi Tenant SaaS Database Design patterns?

Every multi-tenant product eventually lands on one of three storage models. They differ in cost, blast radius, and how painful compliance audits become. None is universally correct—the right choice depends on tenant count, data sensitivity, and how much ops capacity you actually have.

Shared database, shared schema

All tenants live in one database. Every business table carries a tenant_id (or organization_id) foreign key. This is the default for early-stage SaaS because hosting one MySQL 8.4 LTS or PostgreSQL 18 instance is cheap and backups stay simple.

The trade-off is isolation. A missing WHERE tenant_id = ? clause exposes another customer's rows. You compensate with application middleware, database row-level security on PostgreSQL, and code review discipline.

Shared database, separate schema

Each tenant gets its own PostgreSQL schema (or MySQL database within one server instance). Migrations run per schema. Connection pooling gets harder, but you can restore one tenant without touching others.

I have seen this on legal-tech portals where document tables grow fast but tenant count stays under a few thousand. Schema-per-tenant is a middle ground between cost and isolation.

Database per tenant

Each customer gets a dedicated database—sometimes on shared hardware, sometimes on isolated instances. Enterprise buyers and regulated industries often require this. Ops cost scales linearly with tenants unless you automate provisioning.

For a deeper pattern comparison, read how to architect multi-tenant databases: single DB vs shared DB vs hybrid.

How do you choose between shared database and database-per-tenant?

Start with three numbers: expected tenant count in 24 months, average rows per tenant per core table, and your monthly infra budget in NPR and USD. A directory platform like Gulfbizlist with thousands of small vendor accounts fits shared schema. A client portal handling confidential legal documents may justify siloed storage sooner.

When shared schema wins

Shared schema works when all tenants share identical tables and indexes. Reporting across tenants is a product feature, not a security risk. Your team can enforce tenant scoping in middleware, global query scopes, and automated tests.

On Laravel 12 or 13.x apps, packages like stancl/tenancy or a custom TenantScope trait on Eloquent models are common. The database still needs composite indexes starting with tenant_id. See Laravel multi-tenancy approaches compared for application-layer trade-offs.

When database-per-tenant wins

Choose siloed databases when contracts require physical separation, tenants need custom schema extensions, or one noisy neighbour can starve others on shared connection pools. The cost is real: automated provisioning, per-tenant backup jobs, and migration runners that iterate every database.

I've encountered this during production deployments where a single tenant's bulk import locked shared tables. Moving that customer to a dedicated database fixed latency for everyone else. That is a classic hybrid trigger.

Hybrid tiers

Many SaaS products offer shared infrastructure on starter plans and dedicated databases on enterprise tiers. Your Multi Tenant SaaS Database Design must support both from day one—even if you only ship shared schema initially. Store the tenant's isolation tier in a central registry table outside tenant-scoped data.

How do you enforce tenant isolation in a shared schema?

Application checks alone are not enough for regulated data. Layer defences so a single missed scope does not become a headline. PostgreSQL row-level security (RLS) and strict foreign keys are your safety net when the ORM fails.

Core schema rules

Every tenant-owned table needs tenant_id UUID NOT NULL or BIGINT NOT NULL. Reference it with a foreign key to a tenants table. Never store tenant identity only in session storage without a database constraint backing it.

A typical Laravel migration for a shared-schema invoices table:

Schema::create('invoices', function (Blueprint $table) {
    $table->id();
    $table->foreignId('tenant_id')->constrained()->cascadeOnDelete();
    $table->string('number');
    $table->unsignedBigInteger('total_cents');
    $table->timestamps();

    $table->unique(['tenant_id', 'number']);
    $table->index(['tenant_id', 'created_at']);
});

Notice the composite unique key: invoice numbers repeat across tenants, so uniqueness must include tenant_id. This is a frequent mistake covered in database schema design common mistakes.

PostgreSQL row-level security

On PostgreSQL 18, enable RLS so the database rejects cross-tenant reads even if application code forgets a filter. Set a session variable per connection, then attach policies to each table. The official PostgreSQL row security documentation describes the syntax.

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON invoices
    USING (tenant_id = current_setting('app.tenant_id')::bigint);

-- At connection time (middleware):
SET app.tenant_id = '42';

RLS adds a small query-plan overhead. For high-throughput tables, benchmark before enabling on every table. I still enable it on PII-heavy tables first—users, documents, payments.

Global scopes and audit trails

In Laravel, a global scope on base models auto-appends tenant_id to every query. Pair it with an observer that stamps tenant_id on create. Log cross-tenant admin actions in a separate audit table without tenant scope so security reviews have a trail.

Redis 8.10 cache keys must include the tenant prefix: tenant:42:invoice:summary. Queue jobs must call Tenant::run($id, fn () => ...) before touching models. For caching patterns see database query caching strategies.

How should you index and partition a multi-tenant database?

Bad indexing kills shared-schema SaaS faster than bad marketing. When every query filters by tenant_id, that column must lead every composite index on tenant-owned tables. A standalone index on created_at forces the optimiser to scan all tenants.

Indexing checklist

1. Lead every composite index with tenant_id.
2. Add covering indexes for hot list screens: (tenant_id, status, created_at DESC).
3. Audit JOIN paths—child tables need tenant_id even when reachable through a parent FK.
4. Run EXPLAIN ANALYZE with realistic multi-tenant data volumes, not empty dev databases.
5. Review slow-query logs monthly; one tenant's report should not table-scan others' rows.

For deeper index strategy, see database indexing for performance. On client portals like Mijar Law Associates, document search queries dominated latency until we added tenant-prefixed full-text indexes.

Partitioning and sharding triggers

When a single table crosses tens of millions of rows, consider partitioning by tenant_id hash or range. PostgreSQL declarative partitioning and MySQL 8.4 partitioning both support this. Sharding—splitting tenants across database clusters—is the next step when one server maxes out CPU or IOPS.

Do not shard prematurely. I've seen teams split databases at 200 tenants because queries felt slow. Proper indexing fixed it without the ops nightmare of cross-shard joins. Read database sharding explained with a real example before committing.

How do you plan migrations and backups for multi-tenant SaaS?

Schema changes in multi-tenant systems affect every customer simultaneously unless you design for staggered rollout. Backups must restore one tenant without exposing another's data. Encryption and restore testing are not optional extras.

Migration strategy

On shared schema, one migration updates all tenants at once. That is fine for additive changes—new nullable columns, new indexes built CONCURRENTLY on PostgreSQL. Destructive changes need expand-contract patterns: add new column, backfill per tenant in batches, switch reads, drop old column.

For database-per-tenant, wrap migrations in a runner that loops tenant connections and records failures per tenant. Never assume all databases migrated cleanly because staging did. Follow database migrations and seeding best practices in Laravel and coordinate with Laravel database transactions and deadlocks guidance when backfills run under load.

php artisan tenants:migrate --tenant=42
php artisan tenants:migrate --all --chunk=50

Long-running backfills belong in queued jobs with tenant context, not in synchronous deploy scripts. A deploy that blocks PHP-FPM workers hurts every tenant on shared hosting.

Backup, restore, and encryption

Shared-schema backups are simple: nightly logical dump or physical snapshot of one database. Per-tenant restore requires extracting rows by tenant_id—script this before a customer asks for it. Database-per-tenant backups scale linearly; automate with cron or your CI pipeline.

Encrypt data at rest and in transit. See database encryption at rest and in transit. Run restore drills quarterly; most teams discover broken backup scripts only during an incident. Database restore testing you should actually do lists the minimum checks.

Store tenant metadata—plan tier, database connection string, feature flags—in a central catalog database separate from tenant business data. Provisioning a new tenant becomes: insert registry row, run migrations on target, seed defaults, enqueue welcome workflow.

Choosing your stack

PHP 8.3 or higher supports Laravel 13.x; Laravel 12 runs on PHP 8.2+. MySQL 8.4 LTS remains the common managed-hosting choice. PostgreSQL 18 gives you RLS and stronger partitioning if your team knows it. Redis 8.10 handles tenant-prefixed cache and session storage across app servers.

If you are greenfield, read choosing a tech stack for a new SaaS alongside this database guide. For implementation help, enterprise application development and custom software development cover full-stack SaaS delivery. API-heavy products should align database tenant scope with API development auth boundaries.

When prototyping JSON payloads for tenant config webhooks, a JSON formatter saves time during schema design workshops. The MySQL reference on table partitioning documents hash and range options if you outgrow single-instance scaling.

Key Takeaways

• Default to shared schema with tenant_id on every business table until compliance or noisy-neighbour problems force siloing.
• Lead composite indexes with tenant_id; add PostgreSQL RLS on sensitive tables as a second isolation layer.
• Resolve tenant context in middleware before any query, cache read, or queue job executes.
• Plan expand-contract migrations and per-tenant migration runners before you have fifty production databases.
• Test backup restore per tenant tier quarterly—shared dumps do not prove per-customer recovery works.
• Document your isolation tier in a central registry so hybrid upgrades from shared to dedicated stay operable.

People Also Ask

What is the difference between multi-tenant and single-tenant database design?

Single-tenant design dedicates database resources to one customer. Multi-tenant design shares infrastructure while logically separating data via tenant_id, separate schemas, or separate databases. Multi-tenant lowers per-customer cost but demands strict scoping discipline.

Is PostgreSQL or MySQL better for multi-tenant SaaS?

Both work at scale on current versions. PostgreSQL 18 offers built-in row-level security and flexible partitioning—strong choices for shared-schema SaaS with compliance needs. MySQL 8.4 LTS is widely available on budget hosting in Nepal and abroad. Pick based on team skill and hosting options, not blog benchmarks.

How many tenants can share one database?

There is no fixed cap. Thousands of small tenants fit one well-indexed PostgreSQL or MySQL instance. Limits come from total row count, query complexity, and connection pool size—not tenant count alone. Monitor slow queries and IOPS; shard when vertical scaling stops helping.

Should tenant_id be UUID or integer?

Big integers are smaller and faster for indexed joins at high volume. UUIDs avoid sequential ID guessing and simplify offline data merges across regions. Many SaaS products use UUID for the public tenant identifier and bigint serial keys internally—either works if indexed consistently.

Build your multi-tenant foundation before tenant ten

Multi Tenant SaaS Database Design is not a refactor you schedule after launch—it is the contract your application, ops team, and customers inherit. Start with shared schema, enforce tenant scope in middleware and indexes, add RLS where data is sensitive, and automate migrations before siloed databases multiply. The patterns in this guide match what I use on production Laravel SaaS and client portals shipped since 2010. If you want help architecting or hardening a multi-tenant platform, contact us to discuss your tenant model, compliance requirements, and rollout plan.