1---
2title: Optimize RLS Policies for Performance
3impact: HIGH
4impactDescription: 5-10x faster RLS queries with proper patterns
5tags: rls, performance, security, optimization
6---
7 
8## Optimize RLS Policies for Performance
9 
10Poorly written RLS policies can cause severe performance issues. Use subqueries and indexes strategically.
11 
12**Incorrect (function called for every row):**
13 
14```sql
15create policy orders_policy on orders
16  using (auth.uid() = user_id);  -- auth.uid() called per row!
17 
18-- With 1M rows, auth.uid() is called 1M times
19```
20 
21**Correct (wrap functions in SELECT):**
22 
23```sql
24create policy orders_policy on orders
25  using ((select auth.uid()) = user_id);  -- Called once, cached
26 
27-- 100x+ faster on large tables
28```
29 
30Use security definer functions for complex checks:
31 
32```sql
33-- Create helper function (runs as definer, bypasses RLS)
34create or replace function is_team_member(team_id bigint)
35returns boolean
36language sql
37security definer
38set search_path = ''
39as $$
40  select exists (
41    select 1 from public.team_members
42    where team_id = $1 and user_id = (select auth.uid())
43  );
44$$;
45 
46-- Use in policy (indexed lookup, not per-row check)
47create policy team_orders_policy on orders
48  using ((select is_team_member(team_id)));
49```
50 
51Always add indexes on columns used in RLS policies:
52 
53```sql
54create index orders_user_id_idx on orders (user_id);
55```
56 
57Reference: [RLS Performance](https://supabase.com/docs/guides/database/postgres/row-level-security#rls-performance-recommendations)
58