Optimize RLS Policies for Performance

Why This Matters

RLS policies are evaluated for every row in every query. Complex policies with subqueries or function calls can cause significant performance degradation, especially on large tables.

Poorly optimized RLS policies can:

• Cause sequential scans even when indexes exist
• Execute expensive subqueries for every row checked
• Prevent query planner optimizations
• Slow down queries by 10-100x on large datasets

The Problem

Complex RLS policies with subqueries or function calls can be slow:

-- Policy with subquery: executes for every row
CREATE POLICY "Users can view team documents"
  ON documents
  FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM team_members
      WHERE team_members.team_id = documents.team_id
        AND team_members.user_id = auth.uid()
    )
  );

-- Query plan shows nested loop for each document row
-- With 10,000 documents, this executes 10,000 subqueries
SELECT * FROM documents;

This policy executes a subquery for every row in the documents table. With 10,000 documents, Postgres runs 10,000 subqueries, even if you only need 10 rows.

The Solution

Use SECURITY DEFINER functions wrapped in SELECT to optimize RLS policies:

-- Create security definer function (runs with creator's privileges)
CREATE OR REPLACE FUNCTION user_team_ids()
RETURNS SETOF UUID
LANGUAGE SQL
SECURITY DEFINER
STABLE
AS $$
  SELECT team_id FROM team_members WHERE user_id = auth.uid();
$$;

-- Wrap function call in SELECT for better optimization
CREATE POLICY "Users can view team documents"
  ON documents
  FOR SELECT
  USING (
    team_id IN (SELECT user_team_ids())
  );

The SECURITY DEFINER function runs with the creator's privileges, allowing better query planning. Wrapping it in SELECT helps the planner optimize the policy evaluation.

Index RLS Columns

Always index columns used in RLS policy conditions. Without indexes, policy evaluation requires sequential scans:

-- Index the column used in RLS policy
CREATE INDEX documents_user_id_idx ON documents (user_id);

-- Index foreign keys used in RLS policies
CREATE INDEX team_members_user_id_idx ON team_members (user_id);
CREATE INDEX team_members_team_id_idx ON team_members (team_id);

-- Policy can now use index scans instead of sequential scans
CREATE POLICY "Users can view own documents"
  ON documents
  FOR SELECT
  USING (user_id = auth.uid());

Best Practices

• Use SECURITY DEFINER for complex logic: Functions marked SECURITY DEFINER run with the creator's privileges, allowing better optimization and index usage.
• Mark functions as STABLE: If a function returns the same result within a transaction, mark it STABLE so Postgres can cache the result.
• Wrap function calls in SELECT: Use IN (SELECT function()) instead of calling functions directly in policy conditions.
• Index all RLS columns: Every column used in a policy's USING or WITH CHECK clause should have an index.
• Avoid correlated subqueries: Prefer joins or function-based approaches over correlated subqueries in policies.
• Test with EXPLAIN ANALYZE: Always check query plans to ensure policies use indexes and don't cause sequential scans.

Performance Comparison

Here's a complete example showing the performance difference:

-- Slow: Subquery executes for each row
CREATE POLICY "slow_policy"
  ON documents FOR SELECT
  USING (
    EXISTS (SELECT 1 FROM team_members 
              WHERE team_id = documents.team_id 
                AND user_id = auth.uid())
  );
-- Query time: ~450ms for 10,000 rows

-- Fast: Function evaluated once, uses index
CREATE INDEX team_members_user_team_idx ON team_members (user_id, team_id);

CREATE OR REPLACE FUNCTION user_team_ids()
RETURNS SETOF UUID LANGUAGE SQL SECURITY DEFINER STABLE
AS $$ SELECT team_id FROM team_members WHERE user_id = auth.uid(); $$;

CREATE POLICY "fast_policy"
  ON documents FOR SELECT
  USING (team_id IN (SELECT user_team_ids()));
-- Query time: ~45ms for 10,000 rows (10x faster)