Why We Stopped Using Row-Level Security After 1M Rows

It started as the right decision

Row-level security in PostgreSQL is an elegant solution to a real problem. Instead of relying on every query to include a WHERE tenant_id = ? clause, you define a policy at the database level. The database enforces the filter on every query, every time, regardless of what the application does.

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON orders
    USING (tenant_id = current_setting('app.current_tenant'))

No more worrying about a developer forgetting the filter. No more accidental cross-tenant data exposure from a missing WHERE clause. The database itself guarantees isolation.

For a SaaS application with 20 tenants and a few hundred thousand rows, this works beautifully. The policies are invisible to the application. Query performance is nearly identical to queries without RLS. The security posture is stronger than application-level filtering.

The problems emerge gradually, and by the time they are visible, the architecture is deeply embedded.

The performance curve

RLS policies are evaluated on every query. For a simple SELECT by primary key, the overhead is negligible. But as the table grows, the cost of the policy evaluation grows with it.

The policy check is essentially an additional predicate injected into every query plan. On a table with 1 million rows spread across 50 tenants, the planner must consider the RLS predicate alongside every other condition. Composite indexes that previously served queries efficiently now have an additional dimension to navigate.

Benchmarks vary by workload, but a consistent pattern emerges across production systems: RLS adds 5 to 15 percent overhead on simple queries, and the overhead increases on complex queries with joins, subqueries, and aggregations. On a table with 10 million rows across 200 tenants, some queries see 20 to 30 percent degradation compared to the same query on a tenant-specific database with the same data volume.

The individual overhead is acceptable. The cumulative overhead across every query, every request, every tenant, every day, is significant.

The debugging problem

When a query returns no results, there are two possible explanations: the data does not exist, or the RLS policy filtered it out. Without RLS, the answer is always the first one. With RLS, you can never be sure without checking.

-- Returns 0 rows. Is the order missing, or is the policy filtering it?
SELECT * FROM orders WHERE id = 12345;

-- To debug, you need to check as a superuser with RLS bypassed
SET ROLE postgres;
SELECT * FROM orders WHERE id = 12345;
-- Row exists. The policy was filtering it because app.current_tenant was wrong.

This debugging pattern repeats constantly in production. A customer reports missing data. The support team checks the table. RLS is active, so they see nothing. They escalate to engineering. Engineering checks with a superuser role and finds the data. The issue was a session variable not being set correctly in one specific code path.

Every engineer on the team learns to suspect the RLS policy whenever data appears to be missing. This creates a low-grade cognitive overhead that slows down every investigation.

The session variable dependency

RLS policies in PostgreSQL typically reference a session variable like app.current_tenant. Every connection must set this variable before executing any query.

SET app.current_tenant = 'acme';
SELECT * FROM orders WHERE status = 'active'

This means every connection pool, every ORM configuration, every migration tool, every administrative script, and every background job must correctly set the session variable. If the variable is not set, the policy either blocks all rows (safe but disruptive) or allows all rows (dangerous), depending on how the policy is written.

Connection poolers like PgBouncer add complexity. In transaction pooling mode, the session variable must be set at the start of every transaction because sessions are shared across clients. If the SET statement is missed or fails silently, the next query runs with the wrong tenant context or no tenant context at all.

Production incidents caused by session variable misconfiguration are difficult to detect because they do not produce errors. The query succeeds. It just returns the wrong data or no data. There is no exception, no log entry, no alert. The failure is silent.

What about other databases?

RLS is a PostgreSQL feature. But the underlying pattern, query-time filtering for tenant isolation, exists in every database engine.

In MySQL, the equivalent is typically implemented through views with embedded WHERE clauses or application middleware that rewrites queries. The same session variable dependency applies. The same debugging challenges apply. MySQL does not have a native RLS mechanism, so the enforcement is weaker and the application bears more responsibility.

In MongoDB, tenant isolation in a shared collection relies on including the tenant field in every query. There is no database-level policy enforcement. A missing filter in a single query path exposes all tenants' documents.

// Must include tenant_id in every query
db.orders.find({ tenant_id: "acme", status: "active" })

// Forgot the filter? Returns all tenants' data.
db.orders.find({ status: "active" })

In Redis, isolation relies on key naming conventions. Every key is prefixed with the tenant identifier. There is no enforcement mechanism. A KEYS * command returns every tenant's data. A mistyped prefix returns another tenant's data or nothing at all.

In every case, the isolation depends on the application consistently doing the right thing on every operation. The database does not enforce it (except PostgreSQL with RLS), and even PostgreSQL's enforcement comes with the costs described above.

The migration question

Removing RLS from a production system is not trivial. The policies are deeply integrated into the query execution path. Removing them means every query must now include explicit tenant filtering, which is the exact problem RLS was introduced to solve.

The alternative is not removing RLS and going back to application-level filtering. The alternative is removing the need for filtering entirely by giving each tenant their own database.

When each tenant has their own database, the concept of row-level security is irrelevant. There are no rows from other tenants to filter. Every query returns only the current tenant's data because the database contains only the current tenant's data.

-- No RLS, no WHERE clause, no session variable
-- The database only contains acme's data
SELECT * FROM orders WHERE status = 'active'

The performance overhead disappears because there is no policy to evaluate. The debugging problem disappears because missing data means missing data, not a filter issue. The session variable dependency disappears because there is no variable to set.

The performance comparison

On a table with 500,000 rows belonging to a single tenant, comparing a query with RLS on a shared 5-million-row table versus the same query on a dedicated 500,000-row database:

The shared table query must evaluate the RLS policy, navigate a larger B-tree index, and compete with other tenants for buffer cache space. The dedicated database query skips the policy, navigates a smaller index, and has the full buffer cache available for one tenant's data.

The difference is not just the RLS overhead. It is the compound effect of smaller indexes, warmer caches, no lock contention from other tenants, and simpler query plans. The dedicated database is faster for every query type, not just the ones where RLS is expensive.

When RLS still makes sense

RLS is a good fit for specific scenarios. Internal applications where the number of tenants is small and fixed. Systems where the total data volume will remain modest. Applications where adding a database provisioning layer is not feasible and the performance overhead is acceptable.

It is also a reasonable intermediate step. If you are not ready to move to database-per-tenant isolation, adding RLS to a shared table is strictly better than relying on application-level filtering alone. It catches the missing WHERE clause bug, which is the most common source of cross-tenant data leaks.

But for SaaS applications that are growing in tenant count, data volume, or compliance requirements, RLS becomes a ceiling that gets lower over time. The performance overhead accumulates. The debugging complexity compounds. The session variable becomes a source of silent failures.

Moving past query-time filtering

The fundamental limitation of RLS and all query-time filtering approaches is that they add a layer of enforcement on top of shared data. The data is co-mingled, and the enforcement must work perfectly on every query to maintain isolation.

Database-per-tenant isolation eliminates the need for this layer entirely. Isolation is structural, not enforced. There is no policy to evaluate, no session variable to set, no filter to forget. The architecture guarantees isolation by construction.

TenantsDB implements database-per-tenant isolation across PostgreSQL, MySQL, MongoDB, and Redis. Every tenant gets their own database, their own credentials, and their own connection string. Queries are routed through a proxy that resolves the tenant and connects to the correct database. The application never needs to set a session variable, include a tenant filter, or worry about which rows belong to which customer.

Start free with up to 5 tenants at docs.tenantsdb.com.