Designing Global Multi-Tenant Postgres Architectures with Row Level Security (RLS) and Schema Sharding

When building software-as-a-service (SaaS) platforms, deciding how to isolate and scale tenant data is the most critical decision in database architecture. A weak isolation model can lead to catastrophic data leaks (where Tenant A accidentally sees Tenant B's data). Conversely, over-engineered isolation (like spinning up a separate database instance for every small customer) leads to astronomical cloud costs and complex schema migrations.

PostgreSQL offers two production-grade patterns to solve this:

1. 2.
   Shared Database with Row Level Security (RLS): Storing all tenant data in the same tables, utilizing Postgres-native RLS policies to enforce isolation at the SQL parser level.
2. 4.
   Schema-Based Sharding: Allocating an isolated, dedicated database schema for each tenant within the same database cluster.

In this deep dive, we will compare these architectures, write raw SQL configurations for both, and outline the scaling telemetry.

⚡ 1. Shared Database with Row Level Security (RLS)

The RLS approach is the most cost-effective and easiest to maintain. Every multi-tenant table includes a tenant_id column.

By default, even if you query SELECT * FROM products;, Postgres forces the query execution engine to filter rows based on the active session's tenant variable, completely preventing data cross-over at the engine core.

[App Query: SELECT * FROM products] ──> [Postgres Engine] 
                                              │
                                   (Applies RLS Filter: WHERE tenant_id = 'tenant_142')
                                              ▼
                                   [Targeted Tenant Rows]

Let's configure a production RLS schema in Postgres:

sql
-- 1. Create a typical multi-tenant table
CREATE TABLE products (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id VARCHAR(50) NOT NULL DEFAULT current_setting('app.current_tenant'),
  name TEXT NOT NULL,
  price NUMERIC(10,2) NOT NULL
);

-- 2. Turn on Row Level Security
ALTER TABLE products ENABLE ROW LEVEL SECURITY;

-- 3. Define the isolation policy
-- Only allow access if the row's tenant_id matches the session variable 'app.current_tenant'
CREATE POLICY tenant_isolation_policy ON products
AS RESTRICTIVE
USING (tenant_id = current_setting('app.current_tenant'))
WITH CHECK (tenant_id = current_setting('app.current_tenant'));

Safe Database Access in Node.js/Go:

Before running any query on a shared connection pool, your application code must execute a transaction and set the local session context:

javascript
async function getTenantProducts(client, tenantId) {
  const tx = await client.connect();
  try {
    await tx.query('BEGIN;');
    
    // Set the session variable for the duration of this transaction
    await tx.query("SELECT set_config('app.current_tenant', $1, true);", [tenantId]);
    
    // This query is now fully secured by RLS!
    const result = await tx.query("SELECT * FROM products;");
    
    await tx.query('COMMIT;');
    return result.rows;
  } catch (err) {
    await tx.query('ROLLBACK;');
    throw err;
  } finally {
    tx.release();
  }
}

🏗️ 2. Schema-Based Sharding (Tenant-per-Schema)

For enterprise SaaS applications where customers require strict security audits, schema sharding is preferred. Each tenant gets their own isolated Postgres schema namespace within the same database.

• Pros: Extreme isolation. You can backup or restore a single tenant's schema independently. You can even run custom columns or indexes for specific VIP tenants.
• Cons: Higher migration overhead. Running ALTER TABLE migrations means executing the query across thousands of dynamic schemas.

Let's configure dynamic schema routing in Postgres:

sql
-- 1. Create schema for Tenant 142
CREATE SCHEMA tenant_142;

-- 2. Create products table inside the tenant schema
CREATE TABLE tenant_142.products (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  price NUMERIC(10,2) NOT NULL
);

To route queries in your application code, instead of executing raw schema name interpolations (which are prone to SQL injection), you manipulate the Postgres Search Path:

javascript
async function queryTenantSchema(client, tenantId) {
  const tx = await client.connect();
  try {
    // Set the search path so unqualified table names resolve to the tenant's schema
    await tx.query(`SET search_path TO tenant_\${tenantId};`);
    
    // Resolves automatically to tenant_142.products!
    const result = await tx.query("SELECT * FROM products;");
    return result.rows;
  } finally {
    tx.release();
  }
}

📊 3. Shared RLS vs Schema Sharding

🏁 4. Conclusion: Making the Architectural Choice

• Choose Shared RLS if you are building B2C or B2B SaaS with hundreds of thousands of small, fast-registering tenants where operational costs and simple migrations are critical.
• Choose Schema Sharding if you serve large enterprises that demand strict data isolation audits, independent backup schedules, or highly customized regional database instances.