PostgreSQL Multi-tenancy: Choosing Between Schemas and RLS for Your SaaS

Table of Contents

Context & Why: The Multi-tenancy Nightmare

The moment you start architecting a Software-as-a-Service (SaaS) platform, one question will keep you up at night: How do we keep Tenant A from seeing Tenant B’s data? If you’re managing 500+ customers, you can’t just dump everything into a massive table and pray your WHERE tenant_id = ? filters never fail. A single missing clause in your application logic could leak a CEO’s private invoices to a competitor. That is a career-ending event.

Having wrestled with MySQL, MongoDB, and PostgreSQL across various high-traffic projects, I’ve found that PostgreSQL is the undisputed heavyweight champion for multi-tenancy. Its native support for Schemas and Row-Level Security (RLS) offers a level of safety that most other relational databases struggle to match. Usually, the choice comes down to a trade-off: deep isolation versus operational sanity.

I typically categorize the strategies into three buckets:

Database-per-tenant: Total isolation. However, it’s expensive and a nightmare to manage once you hit 100+ customers.
Schema-per-tenant: A solid middle ground. Each tenant gets a dedicated namespace within one database.
Shared-schema (Row-level): Everyone lives in the same tables. The database enforces isolation via internal policies.

Let’s walk through the two most practical strategies for modern backends.

Installation: Setting the Foundation

To follow along, you’ll need a Postgres instance. If you have Docker installed, you can spin up a local environment in about ten seconds. I recommend using Postgres 16 or newer to take advantage of the latest RLS performance optimizations.

# Spin up a Postgres 16 container
docker run --name saas-db -e POSTGRES_PASSWORD=mysecretpassword -p 5432:5432 -d postgres:16

# Jump into the CLI
docker exec -it saas-db psql -U postgres

Once you’re in, create a fresh database. Moving away from the default postgres database immediately is a small but vital habit for better environment management.

CREATE DATABASE itfromzero_saas;
\c itfromzero_saas;

Configuration: Implementing Isolation Strategies

Strategy 1: Schema-based Multi-tenancy

Treat a Postgres “Schema” like a dedicated folder. Each tenant gets their own folder. This approach is fantastic for high-compliance industries because it allows you to back up or restore a single customer’s data without touching anyone else’s records.

When a new company signs up, your backend should trigger a script to provision their workspace.

-- Provision schemas for two new customers
CREATE SCHEMA tenant_apple;
CREATE SCHEMA tenant_banana;

-- Deploy identical table structures
CREATE TABLE tenant_apple.users (id SERIAL PRIMARY KEY, name TEXT);
CREATE TABLE tenant_banana.users (id SERIAL PRIMARY KEY, name TEXT);

-- Insert tenant-specific data
INSERT INTO tenant_apple.users (name) VALUES ('Steve');
INSERT INTO tenant_banana.users (name) VALUES ('Bob');

The real win here is the search_path. Instead of hardcoding schema names in your SQL, your application sets the session context immediately after pulling a connection from the pool.

-- Switch to Apple's context
SET search_path TO tenant_apple;
SELECT * FROM users; -- Steve appears

-- Switch to Banana's context
SET search_path TO tenant_banana;
SELECT * FROM users; -- Bob appears

Strategy 2: Row-Level Security (RLS)

Schemas are clean until you hit 1,000 tenants. At that scale, running a simple ALTER TABLE to add a column can take hours as the database iterates through 1,000 separate tables. This is where RLS shines. Everyone shares one table, but the database acts as an invisible gatekeeper.

First, we define a shared table with a mandatory tenant_id column.

CREATE TABLE projects (
    id SERIAL PRIMARY KEY,
    tenant_id TEXT NOT NULL,
    name TEXT NOT NULL
);

-- Turn on the gatekeeper
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

Next, we need a way to tell Postgres who the “current tenant” is for the active request. I use a custom session variable combined with a security policy.

-- Define the policy: Only show rows matching the session's tenant ID
CREATE POLICY tenant_isolation_policy ON projects
    USING (tenant_id = current_setting('app.current_tenant'));

-- Create a restricted user for the app to prevent privilege escalation
CREATE ROLE app_user WITH LOGIN PASSWORD 'password';
GRANT ALL ON projects TO app_user;

Your application then executes these steps within a single transaction:

BEGIN;
-- Identify the tenant for this specific web request
SET LOCAL app.current_tenant = 'customer_123';

-- This query will NEVER see customer_456's data
SELECT * FROM projects;
COMMIT;

This is your safety net. Even if a developer forgets a WHERE clause in a complex join, the database simply won’t return the unauthorized rows. It has saved my skin in production more than once.

Verification & Monitoring: Keeping it Solid

Isolation isn’t a “set it and forget it” feature. You need to prove it works. I always write integration tests that intentionally try to cross-pollinate data. If Tenant A can successfully query Tenant B’s ID, the build must fail immediately.

Performance is the other half of the equation. RLS adds an implicit filter to every query, which can be a silent killer if you aren’t careful. You must index your tenant_id column. Without it, your database will perform sequential scans as your table grows, turning a 10ms query into a 500ms lag-fest.

-- Non-negotiable index for RLS performance
CREATE INDEX idx_projects_tenant_id ON projects(tenant_id);

In a recent project, switching to RLS allowed us to manage 2,500 tenants in a single table. Our migration time for schema updates dropped from 45 minutes to under 30 seconds. However, if you are in Fintech or Healthcare, the physical separation of the Schema-per-tenant model might still be the price you have to pay for passing a security audit. Whichever path you choose, make sure your isolation logic lives as close to the data as possible.