PostgreSQL RLS Performance: Common Slowdowns and Fixes

Add the index the ownership policy expects

create index projects_owner_id_idx
  on public.projects (owner_id);

create policy "owners read projects"
on public.projects
for select
to authenticated
using (owner_id = (select auth.uid()));

The useful index depends on the policy and query together. A policy index does not replace indexes needed for ORDER BY, joins, or additional filters.

Test as the affected role

Table owners normally bypass RLS, as can roles with BYPASSRLS. An administrative EXPLAIN may therefore omit the policy that makes the application query slow. Reproduce the application role, claims, parameters, and result size.

Use a staging database or a read-only transaction when running EXPLAIN ANALYZE. The command executes the query, including writes when the statement is an INSERT, UPDATE, DELETE, or MERGE.

Avoid repeated request-function work

In Supabase policies, wrapping auth.uid() in SELECT can allow PostgreSQL to evaluate it as an initialization plan once per statement. Without that shape, the function may be invoked for every candidate row.

This is appropriate for a value that remains stable for the statement. It is not a blanket rule for arbitrary functions, especially volatile functions whose result is meant to change per row.

Prefer one statement-level identity value

-- Repeated for candidate rows:
using (auth.uid() = owner_id);

-- Available as an initialization plan:
using ((select auth.uid()) = owner_id);

Keep membership checks narrow

A team policy commonly checks project_members for project_id and user_id. A composite primary key or index supporting that pair avoids scanning membership rows for every project.

If a policy joins several tables, consider whether a carefully reviewed security-definer function can perform a small indexed lookup. Pin its search_path, qualify object names, revoke unnecessary EXECUTE privileges, and keep the function's responsibility narrow.

Separate policy filtering from application filtering

The application should still filter by tenant or owner when it knows the value. RLS remains the security boundary, while the explicit predicate gives the planner more context and reduces candidate rows earlier.

Do not remove RLS because the application query has a WHERE clause. Application bugs and direct API access are exactly why the database policy exists.

Read the complete plan

Look for row-estimate errors, scans repeated under nested loops, filters that remove most rows late, and membership subplans executed many times. Compare actual rows multiplied by loops, not only the top-level execution time.

Use the EXPLAIN ANALYZE guide for plan reading and the Supabase RLS examples for complete policy shapes.