BlogWoWSQLGet started
engineering··5 min read·WoWSQL Team

Multi-Tenant SaaS Postgres Design: A Practical Guide

Multi-tenant SaaS Postgres design explained: compare shared tables, schema-per-tenant and database-per-tenant, then build tenant_id isolation step by step.

For most multi-tenant SaaS apps on Postgres, the right starting point is one shared database where every tenant-owned table carries a tenant_id column, every query filters on it, and Postgres Row Level Security acts as a safety net underneath. Schema-per-tenant and database-per-tenant have their place, but they cost more to run and migrate, so save them for tenants who really need hard separation. This guide compares the three patterns, then builds the shared-table pattern step by step on WoWSQL.

If you're still choosing where your Postgres should live, our managed Postgres in India guide covers hosting options first. This post assumes you have a database and want to design it for many customers.

Key facts

  • Three patterns: shared tables with a tenant_id column, one schema per tenant, or one database (project) per tenant.
  • Default choice: shared tables plus a composite index that starts with tenant_id, and RLS as a second layer of defence.
  • Row Level Security is a built-in Postgres feature (ALTER TABLE ... ENABLE ROW LEVEL SECURITY plus CREATE POLICY), documented on postgresql.org.
  • WoWSQL SDK: new WowSQLClient({ projectUrl, apiKey }).table('invoices').select('*').eq('tenant_id', id).get().
  • WoWSQL pricing (wowsql.com/pricing, 1 November 2026): Free at $0, Pro at $25 a month (100,000 monthly active users included), Scale at $99 a month (2,000,000 included).
  • Regions: Mumbai and US East 1. WoWSQL is built in India, DPIIT-recognised and part of NVIDIA Inception.

The three multi-tenant patterns

1. Shared tables (pool model). Every tenant lives in the same tables. A tenant_id column on each tenant-owned row says who owns it. This is the cheapest and simplest to operate: one schema to migrate, one connection setup, one set of indexes. The risk is a missing WHERE tenant_id = ... that leaks one customer's data to another, which is why you add a second layer of protection.

2. Schema per tenant (bridge model). Each tenant gets their own Postgres schema (tenant_acme.invoices, tenant_globex.invoices) inside one database. Isolation is clearer and a per-tenant export is easy, but every migration now runs once per tenant. With hundreds of tenants, migrations get slow and a half-applied migration leaves schemas out of step. The Postgres catalog also grows with every schema, which slows some tooling.

3. Database per tenant (silo model). Each tenant gets a separate database or a separate WoWSQL project. This gives the strongest isolation and lets you place a tenant in a specific region, which matters when an enterprise customer asks for data to stay in India. The trade-off is cost and operations: each project is billed and managed separately, and cross-tenant reporting needs a separate pipeline.

A common, sensible path is to start every customer on shared tables and move only the largest or most regulated tenants to their own project later. Design your tenant_id column from day one and that move stays a data copy rather than a rewrite.

Step 1: Model tenants and memberships

Start with a table for tenants and a table that links users to tenants. A user can belong to more than one tenant (an agency working for several clients is the usual example), so don't put tenant_id on the users table itself.

create table tenants (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  plan text not null default 'free',
  created_at timestamptz not null default now()
);

create table tenant_members (
  tenant_id uuid not null references tenants(id) on delete cascade,
  user_id uuid not null,
  role text not null default 'member',
  primary key (tenant_id, user_id)
);

The user_id here is the ID from your auth system. For sign-up and login on WoWSQL, see our Postgres authentication for apps guide.

Step 2: Put tenant_id on every tenant-owned table

Every table that holds customer data gets a non-null tenant_id, and it goes first in your indexes:

create table invoices (
  id bigserial primary key,
  tenant_id uuid not null references tenants(id),
  customer_name text not null,
  total numeric(12,2) not null default 0,
  status text not null default 'draft',
  created_at timestamptz not null default now()
);

create index on invoices (tenant_id, created_at desc);

Two rules keep this pattern healthy. First, make tenant_id not null, so a row can never be orphaned. Second, lead composite indexes with tenant_id, because almost every query filters on it. If you need uniqueness, scope it to the tenant, for example unique (tenant_id, invoice_number), so two customers can both have invoice number 1001.

Step 3: Filter by tenant in your server code

The simplest reliable approach is to resolve the tenant on the server and add the filter in one place. Here's a small helper with the WoWSQL TypeScript SDK:

import WowSQLClient from '@wowsql/sdk';

const client = new WowSQLClient({
  projectUrl: process.env.WOWSQL_PROJECT_URL!,
  apiKey: process.env.WOWSQL_SERVICE_KEY!, // server only
});

// tenantId comes from the signed-in user's membership, never from the request body
export function tenantTable(table: string, tenantId: string) {
  return client.table(table).select('*').eq('tenant_id', tenantId);
}

export async function listInvoices(tenantId: string) {
  return tenantTable('invoices', tenantId)
    .order('created_at', 'desc')
    .limit(50)
    .get();
}

export async function createInvoice(tenantId: string, customerName: string) {
  return client.table('invoices').insert({
    tenant_id: tenantId,
    customer_name: customerName,
  });
}

The important part is where tenantId comes from. Look it up from the signed-in user's row in tenant_members, and never trust a tenant ID sent by the browser. Keep the service key on the server; it bypasses the protections a public key has. For a full server setup, see our Next.js Postgres backend guide.

Step 4: Add Row Level Security as a safety net

Application filters are easy to forget in one new endpoint. Postgres Row Level Security lets the database enforce the rule too. A typical pattern reads the current tenant from a session setting:

alter table invoices enable row level security;

create policy tenant_isolation on invoices
  using (tenant_id = current_setting('app.tenant_id', true)::uuid)
  with check (tenant_id = current_setting('app.tenant_id', true)::uuid);

Your code then runs set local app.tenant_id = '...' inside each transaction. Note that table owners and roles with BYPASSRLS skip policies unless you also use ALTER TABLE ... FORCE ROW LEVEL SECURITY, so test with the role your app actually uses.

How the signed-in user's identity reaches Postgres differs between platforms. WoWSQL's open-source self-hosted edition ships auth.uid(), auth.role() and auth.jwt() helpers and opt-in RLS (since version 1.2.0). On the hosted platform, check the current WoWSQL docs for how auth claims reach your policies before you rely on RLS alone, and keep the server-side tenant filter from Step 3 either way.

Step 5: Plan for the tenant who outgrows the pool

Sooner or later a large customer asks for their own environment or for data in a specific region. Because every row already has a tenant_id, you can create a new WoWSQL project in Mumbai or US East 1, run the same migrations, and copy that tenant's rows across with a WHERE tenant_id = ... export. Point that tenant's traffic at the new project URL and you're done. Check wowsql.com/pricing for how additional dedicated projects are billed before you promise this to a customer. For more on keeping data in India, read our data residency guide.

Common mistakes to avoid

  • Trusting a tenant ID from the client. Always derive it from the session on the server.
  • Global unique constraints. Scope them to the tenant, or customers will collide.
  • Indexes without tenant_id first. Queries will scan other tenants' rows.
  • Schema-per-tenant too early. It turns one migration into hundreds.
  • Forgetting background jobs. Cron jobs and webhooks need the same tenant filter as your API routes.

FAQ

What is the best multi-tenant pattern for a SaaS on Postgres?

For most early and mid-stage SaaS apps, shared tables with a tenant_id column. It's the cheapest to run and the easiest to migrate. Move individual tenants to their own database only when size, compliance or a contract requires it.

Is Row Level Security enough to isolate tenants?

It's a strong second layer, but don't make it your only one. Filter by tenant in server code as well, test policies with your real app role, and remember that owners and BYPASSRLS roles skip policies unless RLS is forced.

Should I use a separate schema for each tenant?

Only if you have a small number of large tenants that need separate exports or custom tables. With many tenants, per-schema migrations become slow and error-prone.

Can I give one big customer their own database on WoWSQL?

Yes. Create a separate project for them, in Mumbai or US East 1, run the same migrations and copy their rows across. Each project is managed separately, so check wowsql.com/pricing for current costs.

How much does it cost to start a multi-tenant SaaS on WoWSQL?

The Free plan is $0 with one project and 256 MB of database storage. Pro is $25 a month and Scale is $99 a month, as shown on wowsql.com/pricing on 1 November 2026. For a broader comparison of hosts, see our REST API for Postgres guide.