Optimize Supabase RLS policies with one simple query trick

Early in the development of Equip, an open-source learning management system, the team at Equip Bible relied on Supabase’s row-level security (RLS) policies to enforce access controls. Most of these policies used auth.uid() directly, a function that returns the authenticated user’s ID. What seemed like a minor implementation detail became a hidden performance bottleneck—one that only surfaced after scaling up user activity.

How a single function call can slow down your queries

Supabase’s RLS policies often include expressions like auth.uid() to verify user permissions. For example, a policy might check whether a user is a teacher or admin before allowing an update:

CREATE POLICY "assignments_update_teacher"
  ON public.assignments
  FOR UPDATE TO authenticated
  USING (
    EXISTS (
      SELECT 1
      FROM public.profiles p
      WHERE p.id = auth.uid()
      AND p.role IN ('teacher', 'admin')
    )
  );

At first glance, this looks efficient. The function auth.uid() appears to return a constant value. However, PostgreSQL treats it as a STABLE function, meaning it returns the same value within a single query but isn’t automatically recognized as a candidate for optimization. Without explicit intervention, the database re-evaluates the function for every row scanned during policy checks. For small datasets, this overhead is negligible. But for tables with tens of thousands of rows—such as chapter_progress, which tracks student progress per chapter, or quiz_attempts, which logs every submission—the repeated function calls add up quickly.

The ten-character fix that transforms performance

The solution is surprisingly simple: wrap the auth.uid() call in a subquery. This subtle change signals to the PostgreSQL query planner that the function’s result can be computed once and reused across all rows in the query.

CREATE POLICY "assignments_update_teacher"
  ON public.assignments
  FOR UPDATE TO authenticated
  USING (
    EXISTS (
      SELECT 1
      FROM public.profiles p
      WHERE p.id = (SELECT auth.uid())
      AND p.role IN ('teacher', 'admin')
    )
  );

By enclosing auth.uid() in (SELECT auth.uid()), the planner treats it as a subquery that returns a single value. This allows the query optimizer to hoist the function call out of the per-row loop, executing it just once at the start of the query. The result is stored as a literal, reducing overhead and improving performance—especially as tables grow larger.

How to verify the improvement

You can confirm this optimization by running EXPLAIN ANALYZE on both versions of the policy. The original query will show the function call embedded in the filter logic, while the optimized version will display an InitPlan at the top of the execution plan. The optimized plan references a constant value ($0), proving that the function was evaluated only once.

Why this mistake goes unnoticed until it’s too late

The deceptive nature of this issue lies in its invisibility during early development. Policies written with direct auth.uid() calls work perfectly in small-scale testing environments. They pass all automated tests, and users don’t perceive any latency. The problem only emerges when tables scale to thousands or millions of rows.

Two types of tables are particularly vulnerable:

• Per-user write tables like chapter_progress or quiz_attempts, where each row represents a user’s interaction with content and the table grows linearly with engagement.
• Many-to-many junction tables such as enrollments, which are frequently scanned during authenticated reads and often include RLS checks.

Smaller lookup tables or static catalogs may never trigger noticeable slowdowns, giving developers a false sense of security. By the time performance issues arise, multiple policies may have been written in the same inefficient pattern, compounding the problem.

Catching the issue with Supabase’s built-in advisor

Equip’s team discovered the problem not through user complaints, but by running Supabase’s built-in Advisors tool, which scans for common performance pitfalls in PostgreSQL. One of the advisors, auth_rls_initplan, specifically flags RLS policies that call auth.uid() directly without wrapping it in a subquery.

The advisor can be accessed in two ways:

• Using the Supabase CLI (version 2.81.3 or later) with the command:

supabase db advisors

• Through the Supabase dashboard under the Advisors tab in the Performance section.

Once flagged, fixing the policies is a mechanical process. Each affected policy requires dropping and recreating it with the wrapped auth.uid() call. For Equip, this meant a single migration file that addressed 76 policies:

DROP POLICY "assignments_update_teacher" ON public.assignments;

CREATE POLICY "assignments_update_teacher"
  ON public.assignments
  FOR UPDATE TO authenticated
  USING (
    EXISTS (
      SELECT 1
      FROM public.profiles p
      WHERE p.id = (SELECT auth.uid())
      AND p.role IN ('teacher', 'admin')
    )
  );

A hidden gotcha: helper functions in PL/pgSQL

If you’ve abstracted RLS logic into reusable functions, such as is_admin() to keep policies DRY, be cautious about language selection. Functions declared in PL/pgSQL cannot be inlined by the query planner, even if they wrap auth.uid() correctly. This means every call to the function remains opaque to the optimizer, potentially reintroducing the same overhead.

For example:

-- Inefficient: PL/pgSQL functions aren't inlined
CREATE FUNCTION public.is_admin()
  RETURNS boolean
  LANGUAGE plpgsql STABLE
AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1
    FROM public.profiles p
    WHERE p.id = (SELECT auth.uid())
    AND p.role = 'admin'
  );
END;
$$;

Switching to SQL language allows the function to be inlined into the policy during query planning, preserving the optimization:

-- Efficient: SQL functions are inlined
CREATE FUNCTION public.is_admin()
  RETURNS boolean
  LANGUAGE sql STABLE
AS $$
  SELECT EXISTS (
    SELECT 1
    FROM public.profiles p
    WHERE p.id = (SELECT auth.uid())
    AND p.role = 'admin'
  );
$$;

As Equip scaled, identifying and fixing these inefficiencies became critical. By adopting this small but powerful optimization, the team ensured their RLS policies remain performant even as usage grows. For any Supabase project using RLS, this is a quick win with lasting benefits.