DEBI PRAHARADIKA
← Back to Blog Index
Architecture2026-05-157 min read

Building Scalable Multi-Tenant SaaS with PostgreSQL Schemas

An in-depth look into architecting secure tenant separation using schema isolation on a single PostgreSQL database instance.

When designing a Software-as-a-Service (SaaS) platform, one of the most critical architectural decisions is multi-tenancy isolation. How do you ensure that Tenant A's private transaction records can never, under any circumstance, be leaked to Tenant B?

There are three classic ways to separate database tenants:

  1. Database-per-Tenant: Highest isolation, highest cost.
  2. Shared-Database, Shared-Schema (Row-level Isolation): Lowest cost, highest risk of developer query bugs leaking data.
  3. Shared-Database, Separate-Schemas (Schema Isolation): The sweet spot.

In this article, we'll dive deep into Opsi 3: PostgreSQL Schema Isolation, which is the exact architecture I implemented in the VeloSaaS Platform.

Why PostgreSQL Schemas?

PostgreSQL allows you to define isolated namespace schemas inside a single database instance. Think of a schema as a folder of tables.

By having tenant_1.invoices and tenant_2.invoices, we keep the logical data isolation extremely robust without the physical overhead of maintaining multiple server nodes or database clusters.

-- Create namespaces for individual tenants
CREATE SCHEMA tenant_company_a;
CREATE SCHEMA tenant_company_b;

-- Define tenant tables independently
CREATE TABLE tenant_company_a.users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255) UNIQUE
);

Routing Dynamically in Laravel/Express

To route incoming requests to the appropriate schema, you must resolve the tenant at runtime (usually via the subdomain or a JWT custom claim), then modify the database connection's search path dynamically.

For example, in a Node.js/Express environment, a middleware can intercept requests, identify the tenant slug, and set the query search path:

app.use(async (req, res, next) => {
  const tenantDomain = req.headers['x-tenant-id']; // e.g. "company-a"
  const schemaName = `tenant_${tenantDomain.replace(/[^a-zA-Z0-9]/g, '_')}`;

  // Dynamically set the PostgreSQL search_path for this request client
  await db.query(`SET search_path TO ${schemaName}, public`);
  
  next();
});

Migration Management at Scale

A common challenge with schema isolation is running database migrations. When you deploy a new table or add a column, you must iterate over all active schemas.

Using a custom runner, we can loop over all registered tenant records and execute migrations schema-by-schema:

# Custom CLI command to migrate all isolated schemas
php artisan tenants:migrate --step=1

By keeping our schemas strictly separated, we achieve clean scalability, optimal querying speeds, and a security baseline that will satisfy strict enterprise audits.

← Back to Blog Index