Multi-Tenancy Without Microservices: PostgreSQL RLS

When engineering teams design multi-tenant B2B software, they often look at architecture patterns from multi-billion-dollar enterprise platforms.

They consider three approaches:

1. Database-per-tenant: Provisioning a separate PostgreSQL instance or managed cloud database for every customer account.
2. Schema-per-tenant: Creating a new PostgreSQL schema with duplicate table definitions whenever an organization registers.
3. Shared database with Row-Level Security (RLS): Storing all tenant records in shared tables with an organization_id foreign key, isolated deterministically at the database engine level.

Many teams prematurely pick database-per-tenant or schema-per-tenant because they worry that a shared database might leak data between competing customers.

When building Ankik, our cloud accounting platform for small businesses, we evaluated all three models. We chose a shared single database with PostgreSQL Row-Level Security. Here is why that decision saved months of operational maintenance while providing absolute tenant data isolation.

---

Architectural trade-offs across tenancy models

flowchart TD
    subgraph ModelA["1. Database-Per-Tenant (Operational Nightmare)"]
        AppA["App Server"] --> PoolA["Connection Pooler"]
        PoolA --> DB1[("Tenant 1 DB")]
        PoolA --> DB2[("Tenant 2 DB")]
        PoolA --> DBN[("Tenant 500 DB...")]
    end

    subgraph ModelB["2. Schema-Per-Tenant (Migration Friction)"]
        AppB["App Server"] --> SingleDB1[("Single Database")]
        SingleDB1 --> S1["Schema: tenant_1 (50 tables)"]
        SingleDB1 --> S2["Schema: tenant_2 (50 tables)"]
        SingleDB1 --> SN["Schema: tenant_500..."]
    end

    subgraph ModelC["3. Shared Tables with PostgreSQL RLS (Ankik Architecture)"]
        AppC["App Server"] --> SingleDB2[("Single Database")]
        SingleDB2 --> SharedTables["Shared Tables (invoices, accounts, entries)<br/>WHERE organization_id = current_setting('app.current_org')"]
    end

Dimension	Database-Per-Tenant	Schema-Per-Tenant	Shared Database with RLS
Data Isolation	Physical separation	Logical schema boundary	Database engine kernel policies
Running 500 Migrations	500 connection runs, 45 minutes	500 schema loops, 15 minutes	1 migration transaction, 2 seconds
Connection Pooling	Hundreds of idle pools, RAM exhaustion	Shared pool, search_path churn	Standard connection pool, minimal RAM
Cross-Tenant Analytics	Requires external ETL / data warehouse	Complex cross-schema unions	Standard SQL aggregation with index
Monthly Hosting Cost	$1,500+ across cloud instances	$80 to $200 managed instance	$10 to $20 on standard VPS

---

The hidden pain of schema-per-tenant

Schema-per-tenant looks clean in early documentation: every tenant gets CREATE SCHEMA tenant_123 containing fresh tables.

The problems start when you release software updates:

• Migration duration multiplies: Adding a column to an invoices table requires running the ALTER TABLE statement 500 times in sequence. If schema 341 fails due to a lock timeout, your migration pipeline halts halfway through, leaving your system in an inconsistent multi-version state.
• Connection pool thrashing: Every request must execute SET search_path = tenant_123; before querying tables. This invalidates prepared statements in database connection poolers like PgBouncer, increasing query latency.
• System catalog bloat: A database with 50 tables across 500 schemas contains 25,000 table definitions. The PostgreSQL internal system catalog slows down, memory consumption spikes, and backups take hours.

---

How PostgreSQL Row-Level Security works

Row-Level Security moves authorization from application code into the database kernel.

Even if an application developer writes SELECT * FROM invoices; without an explicit where clause, PostgreSQL transparently appends the tenant security filter before running query execution plans.

Step 1: Enable RLS on the table

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

The FORCE directive ensures that table owners and background roles cannot bypass policies accidentally.

Step 2: Define the security policy

CREATE POLICY tenant_isolation_policy ON invoices
    FOR ALL
    USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid)
    WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid);

• USING controls read visibility (SELECT, UPDATE, DELETE).
• WITH CHECK controls write validity (INSERT, UPDATE), preventing tenant A from writing records stamped with tenant B’s identifier.

Step 3: Set tenant context per transaction

When an incoming HTTP request is authenticated, the application server opens a database connection and sets the session context within the active transaction:

export async function executeTenantQuery<T>(
  orgId: string, 
  callback: (client: pg.PoolClient) => Promise<T>
): Promise<T> {
  const client = await pool.connect();
  try {
    await client.query("BEGIN;");
    // Set tenant context for the duration of this single transaction
    await client.query("SELECT set_config('app.current_org_id', $1, true);", [orgId]);
    
    const result = await callback(client);
    
    await client.query("COMMIT;");
    return result;
  } catch (error) {
    await client.query("ROLLBACK;");
    throw error;
  } finally {
    client.release();
  }
}

The third argument in set_config(..., true) marks the parameter as transaction-local (is_local = true). When the transaction finishes or rolls back, the context resets automatically, preventing leakage when the connection returns to the connection pool.

---

Testing isolation in CI

We test tenant isolation using automated integration tests that intentionally attempt data leaks:

test("tenant B cannot read invoices created by tenant A", async () => {
  const orgA = await createTestOrganization();
  const orgB = await createTestOrganization();
  
  // Insert an invoice under Organization A
  const invoiceA = await executeTenantQuery(orgA.id, async (client) => {
    return createInvoice(client, { amountCents: 50000 });
  });

  // Attempt to query the same invoice ID under Organization B
  const fetchedByB = await executeTenantQuery(orgB.id, async (client) => {
    const res = await client.query("SELECT * FROM invoices WHERE id = $1;", [invoiceA.id]);
    return res.rows[0] ?? null;
  });

  expect(fetchedByB).toBeNull();
});

If any developer removes the RLS policy or changes the configuration key, this test fails immediately in CI.

---

The performance reality: Indexed RLS is fast

A common concern is that checking RLS on every row adds query overhead.

In practice, every table in a multi-tenant application must include a composite index starting with organization_id:

CREATE INDEX idx_invoices_org_date ON invoices (organization_id, created_at DESC);

When PostgreSQL applies the RLS policy, the query planner uses this index to jump directly to the tenant’s index partition. Query execution times on our production Ankik ledger remain under 12 milliseconds across queries joining multiple financial tables.

---

Keep your operational footprint minimal

Building a B2B SaaS does not require running complex multi-database infrastructure or coordinating dozens of isolated schema migrations.

By combining PostgreSQL Row-Level Security with transactional context variables, you get complete data isolation, single-transaction database migrations, and minimal cloud hosting costs. You spend your engineering time building customer features rather than managing database fleets.

If you are designing a multi-tenant data architecture or need guidance on PostgreSQL schema optimization, read our infrastructure teardown post or contact our engineering studio.