Stop Filtering by tenant_id and Let the Database Do It

The Mistake

You add tenant_id to every table. You filter by it everywhere. You ship.

Three hundred endpoints later, someone writes a reporting route at midnight and forgets the filter. TenantA reads TenantB's invoice history. This bug has hit Shopify, Slack, GitHub—every team that built SaaS fast and filtered in the application layer. It will hit you if you have not fixed the architecture.

The tenant_id column is a convention enforced by code you write. It has no authority over code you forget to write.

Three Ways It Breaks

**Missing WHERE clause.** You have 400 routes. A new hire adds a webhook handler. They use the ORM's default query helper that does not apply the tenant scope. Nobody catches it in review. You find out from a customer.

**Noisy neighbor.** TenantC runs a daily export job. The query is unindexed and takes 45 seconds. It holds a connection. Your pool maxes out. TenantD's /healthcheck returns 503. You have no isolation lever to pull—you kill TenantC's query manually and hope they do not notice.

**Graduation trap.** TenantF signs an enterprise deal. They want a dedicated database, their own backup window, a GDPR data residency guarantee in the EU. You look at your schema and realize moving one tenant's data is a multi-week project you have never planned for.

The Fix: RLS Plus Tiered Routing

PostgreSQL's Row-Level Security enforces tenant scoping at the engine level, independent of application code. You set a session-local variable at connection open; every subsequent query filters automatically. A developer who forgets the WHERE clause does not create a breach—the database rejects the leak.

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

In your connection setup (or at transaction start with PgBouncer in transaction mode):

SET LOCAL app.tenant_id = '550e8400-e29b-41d4-a716-446655440000';
SELECT * FROM invoices;  -- DB filters automatically

RLS costs roughly 5% on simple queries. On analytical queries with multiple JOINs, run EXPLAIN (ANALYZE, BUFFERS) after enabling it. The planner occasionally misses a predicate push-down; adding tenant_id as a leading index column fixes it and benefits query performance anyway.

RLS handles the leak problem. It does not handle noisy neighbor or graduation. For those you need tiers.

Tiered Tenancy Routing

Assign each tenant a tier at signup. Route requests to the right isolation level based on a metadata lookup at request time.

**Shared schema + RLS** covers free and starter tenants. Operationally cheap. One migration touches every tenant at once. RLS provides the isolation.

**Schema-per-tenant** fits growth customers. Separate Postgres schemas (tenant_abc.invoices, tenant_def.invoices) live within one cluster. You set connection_limit on the schema's role to cap one tenant's impact on the pool. Per-tenant restores and per-tenant index tuning become possible.

**Dedicated database** is for enterprise. You move them to a different region, give them their own backup schedule, and offer SLA terms you can actually honor.

Cache the tier resolution in Redis with a 30-second TTL. If the metadata store is unavailable, default to shared rather than returning a 503. Keep the router to one job: mapping a tenant ID to a tier. No business logic lives there.

The Tradeoffs

**Schema-per-tenant hits max_connections fast.** Postgres has a hard ceiling, often 100-500 on managed services. If each growth-tier tenant gets its own connection pool, you reach the limit with 20 active tenants. PgBouncer in transaction mode is mandatory here. Skip it and schema-per-tenant collapses under real traffic.

**Migrations multiply by tenant count.** N schemas means running every migration N times. You need an orchestrator that fans out in parallel with rollback support—Atlas, Flyway, or a custom script. The upside is you can run risky DDL against one canary tenant before rolling it everywhere. The downside is a real tooling investment that a shared-schema setup does not require.

**The routing layer adds a dependency.** Keep it simple: Redis cache, one fallback path, no conditional branching based on feature flags or plan details. Overcomplicate it and you have built a service mesh for a problem that needed a hash lookup.

**RLS and SET LOCAL require transaction-mode pooling.** Session-mode PgBouncer reuses connections across requests, so the tenant context from a previous request bleeds into the next one. Transaction mode resets the session variable on every checkout. This is the correct mode for any high-throughput API anyway, but it rules out prepared statements cached across transactions.

Where to Start

If you are early-stage: enable RLS before you have 300 endpoints. The migration cost is an afternoon. The protection is permanent. Tiers can come later without touching application logic.

If you already have hundreds of routes with manual filters, add RLS as a second layer. Your existing filters still run. RLS catches the cases that slip through.

When an enterprise deal closes and the customer asks about dedicated infrastructure, tiered routing gives you the path: point the router at a new database, copy their rows, flip the metadata record. Their application does not change.

The tenant_id column stays. You still need it on indexes, in cross-tenant admin tooling, in analytics queries. As an isolation mechanism, though, it relies on every developer in every PR writing the WHERE clause. The database has better memory than that.