Skip to content
|By Muhammad Wilayat Shah|9 views • 9 unique IPs

How to Design a Scalable Multi-Tenant SaaS Database

Choose the right multi tenant database design for your SaaS or ERP: shared schema vs schema or database per tenant, PostgreSQL row level security, and Laravel and Node.js code.

ShareLinkedInX
How to Design a Scalable Multi-Tenant SaaS Database

Your SaaS or ERP product started with one customer and one database. Now there are dozens or hundreds of tenants, a customer reports seeing another company's invoice, a single large client slows everyone down, and nobody is sure how to onboard the next enterprise account that wants its data kept separate. Good multi tenant database design fixes this by choosing an isolation model on purpose, enforcing tenant boundaries in the database itself, and leaving room to move big tenants later. This guide walks through the options and shows how to implement them with PostgreSQL, Laravel and Node.js.

The Problem: Tenant Data Mixed Without Clear Boundaries

Many applications add a company_id column late, filter by it in most queries, and hope nobody forgets. That works until a report, a background job or a new developer's query skips the filter. At the same time every tenant shares the same indexes, connection pool and maintenance windows, so one tenant's import can hurt everyone. Tenant isolation and SaaS database architecture are decisions, not accidents.

Symptoms of a Weak Multi-Tenant Database Design

  • A user sees records belonging to another tenant, often in exports, search results or cached pages.
  • Queries are missing a tenant_id condition and only code review catches it.
  • One large tenant's reports or imports cause slow queries for all others (the noisy neighbour problem).
  • Deleting or exporting one tenant's data for a contract or privacy request is a manual, risky script.
  • Unique constraints such as invoice numbers clash between tenants.
  • Schema migrations take longer and longer because thousands of schemas or databases must be updated.

A quick check for tables that do not carry a tenant column in PostgreSQL:

Code
SELECT t.table_name
FROM information_schema.tables t
WHERE t.table_schema = 'public'
  AND t.table_type = 'BASE TABLE'
  AND NOT EXISTS (
    SELECT 1 FROM information_schema.columns c
    WHERE c.table_schema = t.table_schema
      AND c.table_name = t.table_name
      AND c.column_name = 'tenant_id');

Root Cause: Isolation Is Enforced Only in Application Code

The real cause is usually that tenant isolation lives only in application code, and the isolation model was never chosen deliberately. There are three main models, each with clear trade-offs:

  • Shared database, shared schema: every table has a tenant_id. Cheapest to run and easiest to migrate, but isolation depends on every query or on database policies.
  • Shared database, schema per tenant: each tenant gets its own PostgreSQL schema with identical tables. Better separation and easy per-tenant export, but migrations run once per schema and catalog size grows with many tenants.
  • Database per tenant: strongest isolation, per-tenant backups and easy data residency, but the highest operational cost: connections, migrations and monitoring multiply.

Most products do best with a shared schema by default and a hybrid escape hatch: large or regulated tenants move to their own database.

multi tenant database design with PostgreSQL row level security filtering rows by tenant_id

Step-by-Step Solution: Multi Tenant Database Design for SaaS

Step 1: Pick the isolation model per tenant tier

Write down how many tenants you expect, how large the biggest will be, and whether any contract requires a dedicated database. Small and medium tenants go into the shared schema; enterprise tenants can be placed in a dedicated database through a tenant directory table.

Step 2: Add tenant_id everywhere and put it first in keys

Every tenant-owned table gets a non-null tenant_id. Put it first in composite indexes and unique constraints, so lookups stay fast and values such as invoice numbers are unique per tenant rather than globally.

Step 3: Enforce tenant isolation in the database with row level security

PostgreSQL row level security (RLS) lets the database reject rows from other tenants even if a query forgets its filter. The application sets the current tenant per transaction, and a policy compares it with each row.

Step 4: Resolve the tenant once per request

Identify the tenant from the subdomain, a header or the authenticated user in middleware, then set it on the database session, the cache prefix and the queue job payload. Background jobs must carry the tenant explicitly. For a deeper Laravel walkthrough see multi-tenant data isolation in Laravel SaaS.

Step 5: Plan for growth

Keep a tenants directory table recording where each tenant lives. When one database is not enough, the same tenant_id becomes your shard key, as explained in database sharding explained.

Code/Commands: Implementing Tenant Isolation

Shared schema table with tenant-scoped keys

Code
CREATE TABLE tenants (
  id         bigserial PRIMARY KEY,
  name       text NOT NULL,
  db_conn    text NOT NULL DEFAULT 'shared',
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE invoices (
  id         bigserial PRIMARY KEY,
  tenant_id  bigint NOT NULL REFERENCES tenants(id),
  number     text   NOT NULL,
  total      numeric(12,2) NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, number)
);

CREATE INDEX invoices_tenant_created_idx ON invoices (tenant_id, created_at DESC);

PostgreSQL row level security policy

Code
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;

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

-- The application role must not be a superuser or have BYPASSRLS
CREATE ROLE app_user LOGIN PASSWORD 'change-me' NOSUPERUSER NOBYPASSRLS;
GRANT SELECT, INSERT, UPDATE, DELETE ON invoices TO app_user;
GRANT USAGE ON SEQUENCE invoices_id_seq TO app_user;

If app.tenant_id is not set, current_setting(..., true) returns NULL and no rows match, so a forgotten tenant context fails closed.

Laravel middleware and global scope

Code
<?php
// app/Http/Middleware/SetTenant.php
namespace App\Http\Middleware;

use Closure;
use Illuminate\Support\Facades\DB;

class SetTenant
{
    public function handle($request, Closure $next)
    {
        $tenantId = $request->user()->tenant_id;
        app()->instance('tenant_id', $tenantId);

        // set_config(..., false) keeps it for the session; PgBouncer users
        // should use transaction pooling with SET LOCAL inside transactions
        DB::statement("SELECT set_config('app.tenant_id', ?, false)", [(string) $tenantId]);

        return $next($request);
    }
}

// app/Models/Concerns/BelongsToTenant.php
trait BelongsToTenant
{
    protected static function bootBelongsToTenant(): void
    {
        static::addGlobalScope('tenant', fn ($q) =>
            $q->where($q->getModel()->getTable().'.tenant_id', app('tenant_id')));

        static::creating(fn ($model) => $model->tenant_id ??= app('tenant_id'));
    }
}

The global scope keeps queries efficient and readable; RLS is the safety net underneath it. Also prefix cache keys with the tenant, for example Cache::tags(["tenant:$id"]) with Redis, so cached pages never leak across tenants.

Node.js with a transaction-scoped tenant

Code
const { Pool } = require('pg');
const pool = new Pool({ connectionString: process.env.DATABASE_URL });

async function withTenant(tenantId, fn) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    await client.query("SELECT set_config('app.tenant_id', $1, true)", [String(tenantId)]);
    const result = await fn(client);
    await client.query('COMMIT');
    return result;
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

// Usage in a Next.js route handler or Express controller
const invoices = await withTenant(req.user.tenantId, (db) =>
  db.query('SELECT id, number, total FROM invoices ORDER BY created_at DESC LIMIT 50'));

Passing true as the third argument makes the setting local to the transaction, which is safe with pooled connections.

Schema per tenant (alternative)

Code
CREATE SCHEMA tenant_42;
CREATE TABLE tenant_42.invoices (LIKE public.invoices INCLUDING ALL);
-- per request:
SET search_path TO tenant_42, public;

Verification: Prove That Tenants Cannot See Each Other

Code
psql -U app_user -d app <<'SQL'
SELECT set_config('app.tenant_id', '1', false);
SELECT count(*) FROM invoices;               -- only tenant 1 rows
SELECT count(*) FROM invoices WHERE tenant_id = 2;   -- must return 0
INSERT INTO invoices (tenant_id, number, total) VALUES (2, 'X-1', 10);  -- must fail
SQL

# Confirm RLS is on for every tenant table
psql -d app -c "SELECT relname, relrowsecurity, relforcerowsecurity FROM pg_class WHERE relname = 'invoices';"

# Confirm the app role cannot bypass RLS
psql -d app -c "SELECT rolname, rolsuper, rolbypassrls FROM pg_roles WHERE rolname = 'app_user';"

Add an automated test that creates two tenants, logs in as the first and asserts every list endpoint returns nothing from the second. Run EXPLAIN ANALYZE on the busiest tenant queries to confirm the (tenant_id, ...) indexes are used.

Prevention: Keep the SaaS Database Architecture Healthy

  • Fail closed: no tenant context means no rows, never all rows.
  • Never connect the application as a superuser or table owner, because those roles skip RLS unless it is forced.
  • Carry the tenant id in every queued job, scheduled task, cache key, file storage path and log line.
  • Track per-tenant row counts and query time so noisy neighbours are visible before they hurt others.
  • Keep the tenant directory table so a large tenant can move to a dedicated database without code changes.
  • Run migrations with one tool and version table; with schema or database per tenant, script them to run across all tenants and stop on the first failure.
  • Review the broader approach in high-availability architecture so tenant databases are backed up and replicated too.

FAQs

What is the best multi tenant database design for a new SaaS?

For most new products, a shared database with a shared schema, a tenant_id column on every tenant table and PostgreSQL row level security. It is cheap, simple to migrate and can grow into a hybrid model later.

How do I guarantee tenant isolation in PostgreSQL?

Enable and force row level security on tenant tables, set the current tenant with set_config per request or transaction, and use an application role without superuser or BYPASSRLS rights.

Schema per tenant or database per tenant?

Schema per tenant suits a moderate number of tenants that need logical separation. Database per tenant suits few, large or regulated tenants that need separate backups, regions or resources.

Does row level security slow down queries?

The policy adds a simple condition on tenant_id; with indexes that start with tenant_id the overhead is usually small. Measure it on your own queries with EXPLAIN ANALYZE.

How does multi-tenancy work with Redis caching?

Prefix every key with the tenant id (for example tenant:42:dashboard) or use per-tenant cache tags, and flush only that tenant's keys when its data changes.

ShareLinkedInX
Back to blog

Keep reading

Newsletter

Occasional notes on Laravel, React, and shipping production systems — no spam.

Comments

Join the discussion. Replies load on demand, up to 5 levels.

0 threads

Loading editor…
Reviewed before publishing

No comments yet

Be the first to start the conversation.