Multi-tenant Postgres without performance cliffs — why I chose RLS

Three models, three failure modes

When building multi-tenant systems, you pick your isolation model early and live with it. The three common approaches:

Database-per-tenant: Strongest isolation. Falls apart operationally — connection pools don't scale beyond a few hundred tenants, migrations become a distributed systems problem, monitoring overhead is O(n tenants).

Schema-per-tenant: Better operationally. Postgres connections can be pointed at the right schema with SET search_path. Breaks down around 500 schemas due to catalog bloat, and shared Postgres processes still see all schema names.

Row-level security (RLS): Single schema, single connection pool. Isolation enforced at the database kernel level — even buggy application code can't read another tenant's data.

Implementing RLS

-- Enable RLS on every tenant-scoped table
ALTER TABLE courses ENABLE ROW LEVEL SECURITY;
ALTER TABLE enrollments ENABLE ROW LEVEL SECURITY;

-- Create a policy — Postgres evaluates this for EVERY query
CREATE POLICY tenant_isolation ON courses
  USING (tenant_id = current_setting('app.current_tenant_id')::UUID);

-- Same for enrollments
CREATE POLICY tenant_isolation ON enrollments
  USING (tenant_id = current_setting('app.current_tenant_id')::UUID);

Setting tenant context safely

The application must set the tenant context before every query. In Node.js with Prisma:

export async function withTenant<T>(
  tenantId: string,
  fn: (tx: Prisma.TransactionClient) => Promise<T>
): Promise<T> {
  return prisma.$transaction(async (tx) => {
    // Set config at transaction level — resets when transaction ends
    await tx.$executeRaw`SELECT set_config('app.current_tenant_id', [tenantId], true)`;
    return fn(tx);
  });
}

Critical: use set_config(..., true) (the third argument) to scope it to the current transaction. Never trust the tenant ID from the client request directly.

Testing isolation

In your CI pipeline, add adversarial tests:

it('cannot read another tenant\'s data', async () => {
  // Set context for tenant A
  const result = await withTenant(TENANT_A_ID, async (tx) => {
    // Try to query data that belongs to tenant B
    return tx.courses.findMany({ where: { tenant_id: TENANT_B_ID } });
  });
  // RLS should return empty array, not throw
  expect(result).toHaveLength(0);
});

Performance

RLS adds a predicate to every query. On indexed columns, this is essentially free — Postgres adds it to the query plan the same way a WHERE clause would. The key is always indexing tenant_id.

CREATE INDEX idx_courses_tenant ON courses(tenant_id);
CREATE INDEX idx_enrollments_tenant ON enrollments(tenant_id);

With these indexes and a warm connection pool, tenant isolation costs under 0.5ms per query in my benchmarks.

The one gotcha

Superuser connections bypass RLS. Make sure your application user is not a superuser. Create a dedicated application role:

CREATE ROLE app_user LOGIN PASSWORD 'your_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
-- app_user is NOT a superuser, so RLS applies