Row-Level Security Impact on Supabase Analytical Queries
Query performance tanks under RLS because analytical scans must evaluate policies against every row.
Row-Level Security exists because authorization logic placed only in application code eventually fails, usually from a missed check in one API route or a middleware guard that someone forgot to apply to a new endpoint. Supabase's answer to that failure mode is to move the check down into PostgreSQL itself, so the database, not the application, decides which rows a given request is allowed to see. A query arriving through a forgotten middleware layer gets the same treatment as one that went through every intended guard: the database refuses the row regardless of how the request got there.
The default behavior of RLS surprises a lot of teams the first time they turn it on. Enabling RLS on a table before writing any policies doesn't leave the table partially open. It locks the table down completely: zero rows returned on read, every write rejected. That deny-all starting point is the safe one, but it means a table can look broken in early testing when the real issue is simply that no policy yet exists to grant access.
The credential that sidesteps all of this is the service_role key, which bypasses RLS entirely and should never appear in client-side code. Any code shipped to a browser or a mobile app that carries that key hands out system-level access to every row in the database, defeating the purpose of having RLS.
Mechanically, PostgreSQL enforces RLS by rewriting the query itself before execution. A plain SELECT * FROM documents sent by the client doesn't run as written. PostgreSQL appends the matching policy's condition to it, so the query that actually executes looks closer to SELECT * FROM documents WHERE <policy expression>. The client never sees that added clause and has no way to strip it out. Two functions do most of the work inside those clauses: auth.uid(), which pulls the authenticated user's UUID out of their JWT, and auth.jwt(), which returns the whole payload and lets a policy check custom claims like org_id or a role string without a separate lookup. Policies apply to specific operations (SELECT, INSERT, UPDATE, DELETE) and specific roles (authenticated, anon), and when more than one permissive policy covers the same operation, they combine with OR logic: if any one of them passes, the row goes through. This rewriting is invisible and non-negotiable, and it is why some queries slow down far more than others once RLS is turned on.
Why analytical queries pay a steeper price
Not every query feels that rewritten WHERE clause the same way. A transactional query, the kind that fetches one user's profile or updates a single order by its primary key, touches a handful of rows, usually found through an index. The policy expression runs against that small candidate set, and the added cost barely registers.
Analytical queries sit on the opposite end of that spectrum. Aggregations, cohort reports, anything that scans a full table forces PostgreSQL to evaluate the policy expression against every row the table contains, because there's no narrow key to narrow the search down first. Supabase's own RLS performance guide is explicit that this applies to SELECT operations and updates that look at every row in a table, and it flags something less obvious: queries using LIMIT and OFFSET still usually have to examine every row to establish an order before they can decide which rows fall inside the requested slice. A paginated query that looks bounded by its LIMIT clause can end up just as exposed as an unbounded one.
A COUNT query, a SUM or AVG aggregation, a rolling-window report, or really any query lacking a selective WHERE clause all fall into this same category, because PostgreSQL has no way to know in advance which rows matter without checking them all. These queries look simple on the page. A SELECT COUNT(*) FROM events reads like the least demanding query imaginable, yet under RLS it demands exactly the full-table treatment that transactional lookups avoid. The practical consequence is that the queries product teams reach for to power dashboards, usage reports, and billing calculations are, structurally, the worst-case input for RLS, and the slowdown tends to stay invisible until the data volume forces it into view.
How PostgreSQL executes a naive policy's per-row function call
The specific mechanism behind that slowdown comes down to how PostgreSQL treats the functions inside a policy. When auth.uid() is called directly in a policy condition, rather than wrapped in a scalar subquery, PostgreSQL classifies it as a function whose result might change from one call to the next, so the planner assumes it might return a different result on every call and refuses to cache it. The result: the planner calls auth.uid() once for every single row it scans, not once per query. A check meant to run a single time per request turns into an operation that scales with the size of the table.
Picture the planner building its execution tree. A properly cached identity check becomes an InitPlan, computed once at the start and reused for every row that passes through the filter. A naive policy instead places the function call inside the Filter node itself, so it reruns on every row the scan touches, whether or not that row ultimately qualifies. On a large table, that naive policy issues one function call per row per query, a volume of work that never appears anywhere in the SQL a developer actually wrote. It only becomes visible by running EXPLAIN ANALYZE against the query, where the Filter node shows repeated function evaluations and the actual row count scanned dwarfs the row count finally returned.
A second, quieter version of the same waste occurs around the anon role. Omitting TO authenticated from a policy means PostgreSQL still runs the entire RLS expression for unauthenticated requests, even though those requests were always going to come back empty. That's database CPU spent evaluating logic whose outcome was already decided before the query began. Between the per-row function call and the unscoped anon evaluation, a naive policy can burn through meaningful compute on work that produces no useful result at all, and both problems trace back to the same root cause: PostgreSQL doing what the policy told it to do, literally and exhaustively, for every row and every request.
Four concrete fixes that restore analytical performance without weakening security
Four specific changes address these failure modes directly, and applying them together removes the overhead without loosening the security boundary RLS is meant to enforce.
The first and most consequential fix is wrapping the identity function in a scalar subquery. Changing auth.uid() = user_id to (SELECT auth.uid()) = user_id computes the function once, as an InitPlan, and reuses that cached result across every row. Supabase's own RLS performance documentation recommends this pattern for every JWT function, including auth.uid() and auth.jwt(), and for any other function whose output doesn't depend on which row is being checked. That constraint matters: the wrapping is safe only when the function's result is independent of the row in question. Session-scoped identity functions qualify. A function that reads a value out of the row itself must be recalculated for every row to produce correct results.
The second fix is adding a B-Tree index on whichever column the policy filters against, typically user_id. With that index in place, the planner can perform an index scan instead of a sequential scan, cutting down sharply on the number of rows the policy expression ever has to touch. Subquery wrapping and indexing reinforce each other: the subquery removes the cost of repeatedly calling the function, and the index removes most of the rows that function would otherwise need to run against. Used together, they produce the largest combined improvement of any pairing in this list.
The third fix concerns the direction of the join in team- or organization-based policies, where the order of operations changes the cost dramatically. Querying the membership table filtered by the authenticated user, then returning the set of permitted IDs and checking each row against that set, runs far cheaper than the reverse: querying the membership table filtered by the row's ID separately for every row in the scan. Supabase's documentation states the preferred pattern directly: team_id in (select team_id from team_user where user_id = auth.uid()) runs much faster than its correlated equivalent. For cases where that same permission check recurs across several policies, moving the logic into a SECURITY DEFINER function sidesteps RLS on the join table itself, which avoids two distinct failure modes: an infinite recursion error (Postgres error code 42P17) when two tables' policies end up referencing each other, and the compounding cost of chained subquery policies triggering still more policy checks downstream. A documented worst case shows the stakes: a role-based policy checking team_id = ANY(user_teams()) without wrapping the array ran past two minutes on a large table with many teams. The fix was to evaluate user_teams() once, wrap it, and filter against the resulting set.
The fourth fix is role scoping: every policy built on auth.uid() or auth.jwt() should specify TO authenticated. Without that scope, PostgreSQL evaluates the full policy expression even for anonymous requests that were always going to fail, spending CPU for no benefit. Supabase's guidance on this point is direct, stating that auth.uid() or auth.jwt() should never serve as the sole mechanism for excluding the anon role. Adding authenticated to the approved roles lets PostgreSQL reject anonymous requests before it even reaches the rest of the policy expression.
Choosing among these isn't a matter of applying all four everywhere by default. A reasonable starting point is inline policies with scalar subquery wrapping, reserving a security definer function for cases where the same permission check spans three or more tables, where query times exceed 50 milliseconds on typical data sizes, or where the identical logic repeats across five or more policies.
When routing around RLS beats optimizing it
Some analytical workloads simply don't belong on a production OLTP database running under RLS, regardless of how well the policies are tuned, and the honest response is to route that traffic to a separate access path. That move fixes the slow queries but means some access now bypasses tenant isolation entirely, which has to be managed on its own terms.
PostgreSQL offers two routes around RLS: the service_role key, or a custom Postgres role granted the bypassrls privilege. Both grant system-level access that crosses every tenant boundary in the database, and neither should ever appear in client-side code. The legitimate reasons to reach for either are narrow: server-side data exports, administrative batch jobs, and analytical queries that aggregate across every tenant by design, where the business itself requires cross-tenant visibility. Outside those cases, the risk is specific and severe. Any script, agent, or tool running with the service key operates completely outside tenant isolation, so a query intended to return one tenant's numbers can silently return every tenant's numbers if the filter that was supposed to scope it gets left out at the application layer.
There's also a point where RLS stops being the right tool regardless of performance. If a data model needs five-level-deep subqueries inside every policy just to express its access rules, enforcing that logic in an API layer with proper service decomposition is often easier to maintain than an RLS expression that keeps growing more convoluted with each new requirement. The bypass pattern holds up only when the surface area using the service key stays small, gets reviewed in code, and gets logged. It becomes a liability the moment it turns into the default path for any query that feels slow, because at that point the tenant isolation RLS was built to guarantee no longer applies to a shrinking share of the traffic touching the database.
What changes when AI agents query your data
Everything described above gets worse once an AI agent, rather than a person, sits on the other end of the query. Agents issue queries at a frequency no human workflow matches, they run without a person reviewing each individual query before it executes, and they are the software component most likely to be handed a service key for convenience. That combination means an agent can end up operating entirely outside tenant isolation by default, not as an edge case but as its normal mode of operation.
An agent using the service key to dodge RLS overhead inherits the exact risk described in the previous section, only faster. If its generated SQL omits a tenant filter, it can return cross-tenant data silently, and it will do so at machine speed rather than at the pace a human might need to notice something looks wrong. On top of that, AI-generated SQL carries its own distinct failure modes: hallucinated joins, column names that were never real, filters that look plausible but are subtly wrong, and assumptions built on stale data. None of these get caught by RLS once the agent is already bypassing it, because RLS was never built to validate the correctness of a query, only its authorization.
There's a resource problem layered on top of the governance one. Real-time agent access to a production OLTP database, running under full RLS evaluation at agent-level query frequency, creates CPU pressure that degrades transactional performance for the human users the database was built to serve. The analytical load an agent generates competes directly with the production workload for the same compute. Agents that need current data can't simply wait on a periodic batch refresh into a separate warehouse, but giving them direct production access recreates the same contention problem the batch refresh was meant to avoid. The architecture that actually resolves this tension is a governed data layer sitting between the agent and the production database: pre-modeled, pre-calculated datasets that already have tenant context built in, row-level filters applied at modeling time rather than query time, and outputs that have been checked for correctness before an agent ever queries them. That layer gives an agent somewhere to ask its questions that isn't the production database and isn't an unguarded service-key bypass either.
Diagnosing RLS performance before and after changes
The sources checked for this guide are listed below.
