RLS Performance Considerations

---

Performance Matters

RLS is powerful, but poorly designed policies can significantly impact query performance. Understanding how PostgreSQL evaluates policies helps you write efficient ones.

---

How PostgreSQL Evaluates RLS

Query Transformation

When you query a table with RLS enabled, PostgreSQL rewrites your query:

-- Your query
SELECT * FROM posts WHERE status = 'published';

-- With RLS policy: USING (user_id = auth.uid())
-- PostgreSQL transforms to:
SELECT * FROM posts
WHERE status = 'published'
  AND (user_id = auth.uid());  -- RLS condition added

-- With multiple policies (OR):
SELECT * FROM posts
WHERE status = 'published'
  AND (user_id = auth.uid() OR visibility = 'public');

Execution Order

┌─────────────────────────────────────────────────────────┐
│                Query Execution                          │
│                                                         │
│  1. Parse query                                        │
│  2. Collect RLS policies                               │
│  3. Add policy conditions to WHERE clause              │
│  4. Plan query (optimizer considers indexes)           │
│  5. Execute query                                      │
│                                                         │
└─────────────────────────────────────────────────────────┘

The key insight: RLS conditions are evaluated for every row that matches your query filters.

---

Common Performance Problems

Problem 1: Subqueries in Policies

-- Slow: Subquery executes for every row
CREATE POLICY "Team members"
  ON documents FOR SELECT
  USING (
    team_id IN (
      SELECT team_id FROM team_members WHERE user_id = auth.uid()
    )
  );

For large tables, this subquery runs many times.

Problem 2: Complex Joins

-- Slow: Complex join in policy
CREATE POLICY "Can view if following"
  ON posts FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM follows f
      JOIN users u ON f.following_id = u.id
      JOIN user_settings s ON u.id = s.user_id
      WHERE f.follower_id = auth.uid()
        AND f.following_id = posts.user_id
        AND s.allow_followers = true
    )
  );

Problem 3: Function Calls

-- Potentially slow: Function called per row
CREATE POLICY "Complex role check"
  ON data FOR SELECT
  USING (
    complex_permission_check(auth.uid(), id)  -- Function call per row
  );

---

Optimization Strategies

Strategy 1: Use Simple Equality Checks

The fastest policies use simple equality on indexed columns:

-- Fast: Direct column comparison
CREATE POLICY "User owns"
  ON posts FOR SELECT
  USING (user_id = auth.uid());

-- Ensure index exists
CREATE INDEX idx_posts_user_id ON posts(user_id);

Strategy 2: Materialize User Data

Instead of subqueries, store computed data:

-- Slower: Subquery for teams
USING (
  team_id IN (SELECT team_id FROM team_members WHERE user_id = auth.uid())
)

-- Faster: Denormalized user data in JWT or session
-- Add team_ids to user's app_metadata during login

-- Policy using JWT claim
USING (
  team_id = ANY(
    (SELECT array_agg(x::uuid)
     FROM jsonb_array_elements_text(auth.jwt()->'app_metadata'->'team_ids') x)
  )
)

Strategy 3: Use Security Definer Functions

Functions with SECURITY DEFINER can be optimized:

-- Create an optimized function
CREATE FUNCTION get_user_team_ids()
RETURNS uuid[]
LANGUAGE sql
SECURITY DEFINER
STABLE  -- Important: tells optimizer results don't change within query
AS $$
  SELECT array_agg(team_id)
  FROM team_members
  WHERE user_id = auth.uid()
$$;

-- Use in policy
CREATE POLICY "Team access"
  ON documents FOR SELECT
  USING (team_id = ANY(get_user_team_ids()));

The STABLE marker helps PostgreSQL optimize by caching the function result.

Strategy 4: Index Policy Columns

Ensure indexes exist for columns used in policies:

-- Policy uses these columns
USING (user_id = auth.uid() AND deleted_at IS NULL AND status = 'published')

-- Create appropriate indexes
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_posts_status ON posts(status) WHERE deleted_at IS NULL;

-- Or composite index
CREATE INDEX idx_posts_user_status ON posts(user_id, status)
  WHERE deleted_at IS NULL;

Strategy 5: Partial Indexes Match Policies

-- Policy only allows non-deleted
USING (deleted_at IS NULL AND user_id = auth.uid())

-- Partial index matches exactly
CREATE INDEX idx_active_posts_user
  ON posts(user_id)
  WHERE deleted_at IS NULL;

---

Analyzing RLS Performance

EXPLAIN ANALYZE with RLS

-- See how PostgreSQL executes with RLS
SET LOCAL role TO authenticated;
SET LOCAL request.jwt.claims TO '{"sub": "user-uuid"}';

EXPLAIN ANALYZE SELECT * FROM posts;

Look for Warning Signs

-- Bad: Seq Scan (scanning entire table)
Seq Scan on posts (cost=0.00..1523.00 rows=1 ...)
  Filter: ((user_id = 'uuid'::uuid) AND (status = 'published'))

-- Good: Index Scan
Index Scan using idx_posts_user_id on posts (cost=0.42..8.44 rows=1 ...)
  Index Cond: (user_id = 'uuid'::uuid)

Check Subquery Execution

-- Bad: SubPlan executes many times
->  SubPlan 1
      ->  Index Scan (loops=10000)  -- Executed 10,000 times!

-- Better: Single execution
->  Hash Semi Join
      ->  ...

---

Performance Checklist

Before Creating a Policy

1. Identify columns used: What columns does the policy check?
2. Check for indexes: Do indexes exist on those columns?
3. Estimate row count: How many rows will be checked?
4. Test with EXPLAIN: See actual execution plan

Policy Design Guidelines

┌─────────────────────────────────────────────────────────┐
│              Policy Performance Rules                    │
│                                                         │
│  ✓ DO                                                   │
│  - Use simple column = value comparisons               │
│  - Index columns used in policies                      │
│  - Use STABLE/IMMUTABLE function markers               │
│  - Prefer equality over IN/ANY when possible           │
│  - Use partial indexes matching policy filters         │
│                                                         │
│  ✗ DON'T                                               │
│  - Complex subqueries in every policy                  │
│  - Functions without STABLE marker                     │
│  - Multiple table joins in USING clause               │
│  - Unindexed columns in policies                       │
│                                                         │
└─────────────────────────────────────────────────────────┘

---

Real-World Optimization Example

Before: Slow Policy

-- Checking team membership via subquery
CREATE POLICY "Team access"
  ON projects FOR SELECT
  USING (
    organization_id IN (
      SELECT o.id
      FROM organizations o
      JOIN organization_members om ON o.id = om.organization_id
      JOIN roles r ON om.role_id = r.id
      WHERE om.user_id = auth.uid()
        AND r.can_view_projects = true
    )
  );

Problems:

• Three-way join in subquery
• Executes for every row
• No caching

After: Optimized Approach

-- Step 1: Create materialized permission function
CREATE FUNCTION user_can_view_org_ids()
RETURNS uuid[]
LANGUAGE sql
SECURITY DEFINER
STABLE  -- Results cached within query
AS $$
  SELECT array_agg(DISTINCT om.organization_id)
  FROM organization_members om
  JOIN roles r ON om.role_id = r.id
  WHERE om.user_id = auth.uid()
    AND r.can_view_projects = true
$$;

-- Step 2: Simple policy using function
CREATE POLICY "Team access"
  ON projects FOR SELECT
  USING (organization_id = ANY(user_can_view_org_ids()));

-- Step 3: Ensure index exists
CREATE INDEX idx_projects_org_id ON projects(organization_id);

Result:

• Function executes once per query
• Simple array check using index
• Orders of magnitude faster for large tables

---

When Performance Doesn't Matter

Not all tables need optimization:

• Small tables (< 1000 rows): Simple policies are fine
• Admin-only tables: Few queries, optimization unnecessary
• Infrequent access: Occasional queries don't need tuning
• Complex but correct > Fast but wrong: Security first

The 80/20 Rule

Focus optimization on:

• Tables with high query volume
• Tables with many rows
• Policies that appear in slow query logs

---

Key Takeaways

1. RLS adds WHERE conditions: Policies become part of every query
2. Index policy columns: Most impactful optimization
3. Avoid complex subqueries: Use functions with STABLE marker
4. Test with EXPLAIN ANALYZE: See actual execution plans
5. Partial indexes help: Match index conditions to policy filters
6. Security over performance: Don't sacrifice correctness for speed

---

Module Summary

In this module, you've mastered Row Level Security:

• Why RLS provides better security than application code
• How to write policies with USING and WITH CHECK
• Common patterns for typical scenarios
• Performance optimization techniques

RLS is Supabase's most powerful security feature. Used well, it lets you build secure applications with confidence. Next, we'll explore realtime subscriptions.

---

A slow secure system can be optimized. A fast insecure system is a liability. Get security right first, then optimize the critical paths.