Row Level Security (RLS) is standard practice for multi-tenant applications using PostgreSQL. By defining access policies directly on database tables, application code can query data without explicitly adding tenant clauses to every single SQL statement. However, when application complexity grows and secured tables are joined across large datasets, query execution times can suddenly degrade from milliseconds to full table scans.
When building multi-tenant SaaS platforms and custom web apps, we frequently encounter query performance bottlenecks caused by RLS policy evaluation order. Understanding how the PostgreSQL optimizer handles security boundaries is critical to preventing sudden performance degradation.
The Problem: Suboptimal Query Plans Under RLS
Consider a standard database setup with an organizations table, a project table, and an issues table. Every issue belongs to a project, and every project belongs to an organization. To enforce tenant isolation, an RLS policy is attached to the issues table using a session variable or subquery check.
When you execute a query joining issues with projects, you expect PostgreSQL to use existing foreign key indexes on both tenant_id and project_id. Instead, the query planner may ignore available B-tree indexes and perform full sequential scans on the issues table.
The sequential scan occurs because PostgreSQL wraps table access in a security barrier layer before applying non-security predicates, preventing the optimizer from pushing join constraints down into index scans.
Why PostgreSQL Prevents Subquery Inlining for RLS
PostgreSQL treats RLS policies as security barrier views. The engine must guarantee that user-defined functions or join conditions cannot leak restricted row data to unauthorized users through side-channel error messages or function execution side effects.
PostgreSQL treats RLS policies as security barrier views, preventing the optimizer from reordering user join predicates ahead of policy security filters.
If the optimizer evaluated user WHERE clauses or join predicates before evaluating the RLS policy, a malicious user could pass a custom function in the WHERE clause that logs or throws error output containing unexposed data from rows they should not see. To prevent this security risk, PostgreSQL forces policy filters to evaluate strictly prior to un-trusted query predicates.
How to Fix RLS Join Performance Degradation
There are three distinct fixes depending on whether your RLS policy relies on session variables, helper functions, or subqueries.
Mark Custom Helper Functions LEAKPROOF
Informs the query planner that a function contains no side effects and cannot leak data, allowing the optimizer to reorder predicates safely.
If your RLS policy calls a custom PL/pgSQL function to verify permissions, PostgreSQL will always evaluate it as untrusted unless explicitly declared. Marking verified helper functions as LEAKPROOF allows the optimizer to perform index scans and reorder join conditions.
When managing complex application architectures and cloud infrastructure, ensuring helper functions are strictly defined prevents database nodes from burning CPU cycles on redundant evaluations.
Optimize Session Variable Types
Avoid runtime type coercion inside RLS policies to allow standard B-tree index matches.
When session settings are fetched using current_setting(), the returned value is always of type text. Casting the table column to text or casting the function result dynamically inside the policy can suppress index usage. Instead, cast the session setting directly to the matching column type in an optimized policy structure.
- › Create a composite B-tree index on (tenant_id, project_id) on child tables.
- › Ensure all session variables are cast to the exact column data type inside the policy definition.
- › Avoid calling non-immutable functions inside RLS expressions where possible.
- › Utilize security invoker views in PostgreSQL 15 and above to safely handle multi-table joins.
Verifying the Query Execution Plan Fix
After creating a composite index and applying the optimized policy with direct type casts, we can re-evaluate the query execution plan using EXPLAIN ANALYZE.
The execution plan now shows an Index Scan using idx_issues_tenant_project. Total execution time drops from 418 milliseconds down to 0.082 milliseconds.
- Check EXPLAIN ANALYZE output for unexpected Seq Scan nodes on RLS-enabled tables.
- Verify composite indexes cover both tenant filter keys and join target keys.
- Confirm user-defined functions used in policies are marked LEAKPROOF.
- Test query plans using SET ROLE to mirror real application role environments.
By aligning your index design with PostgreSQL security barrier behavior, you can run multi-tenant row level security at scale without sacrificing query throughput. If you need help diagnosing complex query bottlenecks or scaling database performance, reach out to our team through our performance engineering audit services.