Two ways to isolate tenants in one database

Multi-tenancy on Rails and PostgreSQL really means choosing where the isolation boundary lives. Schema-per-tenant puts it in the catalog: each tenant gets its own namespace of tables. Row-level security (RLS) keeps one physical schema and pushes isolation into every query through tenant_id policies. Both work, and they fail in opposite ways – the failure mode decides which one you ship.

Schema-per-tenant: the wall is migration time

Isolation is airtight, restoring a tenant is a single schema, and you will never leak a query across tenants because there is no shared table to leak into. The apartment gem sets search_path per request and that is the whole integration. That is why we run it for the handful of enterprise tenants with contractual data isolation.

Then you hit the wall. Every tenant schema duplicates the catalog – roughly 700-800 rows of pg_class, pg_attribute, and pg_index before you have written any code. At 1,000 tenants that is ~700k catalog rows that every connection pays to look through. Worse, migrations multiply: a CREATE INDEX that takes 2 minutes in one schema takes 2 minutes times the number of tenants. At 1,000 tenants that migration runs for over 33 hours. And search_path breaks prepared-statement plan caching: PostgreSQL caches a plan per (query, schema), so with per-tenant schemas you either lose the cache or hold thousands of plans in memory.

We do not run schema-per-tenant past the low hundreds, and only when isolation is a legal requirement rather than a product decision.

RLS: one schema, a policy, and a session variable

With RLS the app owns one physical schema. Every tenant-owned table gets tenant_id bigint NOT NULL and a policy:

1
2
3
4
5
ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;
ALTER TABLE accounts FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON accounts
  USING (tenant_id = current_setting('app.current_tenant_id', true)::bigint);

FORCE ROW LEVEL SECURITY matters: table owners bypass RLS by default, and your app connects as the owner. Forget it and the policy is decorative. The app role must also be non-superuser, or the whole mechanism is a paper sign.

The Rails side sets the session variable at the start of every request:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
class ApplicationController < ActionController::Base
  around_action :scope_tenant

  private

  def scope_tenant
    tenant = Tenant.find_by!(subdomain: request.subdomain)
    ActiveRecord::Base.transaction do
      ActiveRecord::Base.connection.execute(
        "SET LOCAL app.current_tenant_id = #{tenant.id.to_i}"
      )
      yield
    end
  end
end

Two details bit us in production:

1. SET LOCAL only lives inside a transaction. Call it outside one and it applies to nothing. The next query runs without a tenant variable, and the policy compares tenant_id against NULL – so RLS silently filters out every row. That is the safe failure, but it looks like “the database is empty.” Wrapping the whole request in a transaction is the clean fix; the cost is one nearly read-only transaction per request.
2. Background jobs must set the same variable or they run with no tenant scope at all. We extracted a helper used by controllers, jobs, and the console so the scoping mechanism is identical everywhere:

1
2
3
4
5
6
7
8
9
10
module TenantSession
  def self.with_tenant(tenant_id)
    ActiveRecord::Base.transaction do
      ActiveRecord::Base.connection.execute(
        "SET LOCAL app.current_tenant_id = #{tenant_id.to_i}"
      )
      yield
    end
  end
end

If the code inside the block opens its own transaction and commits, the SET LOCAL scope ends at that commit – call with_tenant in the outermost transaction only.

Costs and query plans

The overhead of RLS is a policy check per row. On indexed point queries with EXPLAIN ANALYZE we measure 2-4%; on range scans, under 1%. The real requirement is brutal: every table carrying tenant data needs an index that leads with tenant_id, and every query must use it, or the policy recheck becomes a filter over a full scan. We gate this with a lint that fails any migration adding a tenant-owned table without a tenant_id index, and we re-run EXPLAIN ANALYZE on the hot queries after every schema change.

We also use current_setting('app.current_tenant_id', true) – the true is missing_ok. That way a test that forgets to set the variable fails loudly on the cast to bigint instead of silently comparing against NULL.

What we run

For the self-serve product we use RLS: tens of thousands of tenants in one schema, tenant_id on every table, about 2% overhead on our hottest queries, one app-level failure mode (scoping forgotten) instead of a catalog that grows with every tenant. Schema-per-tenant is reserved for the handful of accounts with contractual isolation, where the catalog and migration costs are acceptable because the tenant count is fixed and tiny.

• Always FORCE ROW LEVEL SECURITY; table owners bypass policies by default.
• Set SET LOCAL inside a transaction and reuse the same helper in controllers, jobs, and the console.
• Index tenant_id on every tenant-owned table; verify with EXPLAIN ANALYZE after each migration.
• Keep the app role non-superuser or ownership quietly disables your policies.
• Budget schema-per-tenant as migration time times tenant count; it is a headcount limit, not a performance limit.