Case Study: Your Supabase RLS Works. But Will It Scale?

How Row Level Security can hide performance problems on small datasets and become a database bottleneck as your data grows.

Photo by Naomi August on Unsplash

Introduction: The Production Incident

The application had been running without any obvious performance issues. The first tenants were onboarded, and everything appeared to work as expected.

Then the first tenants began generating data at a volume that exposed a performance problem that had not been visible before.

Some application queries began timing out. From the application side, the symptom was relatively generic: requests going through the Supabase API were returning HTTP 500 errors. The response itself did not tell us that PostgreSQL had exceeded its query timeout.

The first clue came from the Supabase logs.

By correlating the failed Supabase REST requests with the database logs, we found the underlying PostgreSQL error: query cancellation due to statement timeout. The configured statement timeout by default is 8 seconds, meaning that some of these queries were taking longer than eight seconds to complete.

With this query you can check what is the timeout settings for your authenticated role:

SELECT
   rolname,
   rolconfig
FROM pg_roles
WHERE rolname = 'authenticated';

This changed the direction of the investigation.

The application wasn't simply experiencing a generic API problem. PostgreSQL was unable to complete specific queries within the configured timeout once the amount of production data had grown sufficiently.

And the surprising part was still ahead: the queries were protected by Row Level Security (RLS), and RLS turned out to be a significant part of the performance problem.

The First Breakthrough: The Query Wasn't the Problem

The application uses database-level security rules to determine which data each user is allowed to access. These rules are evaluated automatically by the database whenever data is requested.

The challenge appears when a single request involves a large amount of related data. For example, a user may request a list of records, where each record contains many related entries. The database does not only retrieve this data it also needs to verify that the user is allowed to see each relevant entry.

When several security rules are applied at the same time, some of these checks can trigger additional permission checks. As a result, the database may end up performing the same type of authorization work many times during a single request.

Conceptually:

1. User request

2. Large set of records

3. Security checks

4. Additional permission checks

5. Repeated across many entries

   
   

As the amount of data grows, the cost of these authorization checks can grow significantly and may eventually become a major part of the query execution time.

Nested RLS Evaluation

The technical cause is typically a combination of multiple permissive(An RLS policy that can grant access when its condition is satisfied. Multiple permissive policies are combined with OR) and restrictive policies(An RLS policy that adds a condition that must also be satisfied. Restrictive policies are combined with AND with the applicable permissive policies) containing nested or correlated queries. A policy may use an EXISTS condition that depends on the current row, causing PostgreSQL to evaluate that check repeatedly for many rows.

What kind of policies you have you may check like that:

SELECT
   schemaname,
   tablename,
   policyname,
   permissive,
   roles,
   cmd,
   qual,
   with_check
FROM pg_policies
WHERE tablename = 'resources';

The situation becomes more complex when a policy references a table that is itself protected by RLS. This creates nested RLS evaluation, where one authorization check can trigger additional authorization checks.

Restrictive policies can have a particularly strong impact because they are mandatory conditions: a row must satisfy all applicable restrictive policies in addition to satisfying the permissive access conditions.

The key performance indicator is often visible in the execution plan as a high loops value for a SubPlan (A subquery that PostgreSQL executes as part of a larger query, potentially multiple times.). A high number of loops does not automatically mean there is a problem, but when each execution performs meaningful work, the cumulative cost can become substantial.

Why this is difficult to solve

This is not always a simple indexing or SQL optimization problem. The RLS policies form a combined authorization model, so changing one condition can affect both performance and security semantics.

Potential optimization areas include:

1. simplifying nested authorization logic;

2. reducing correlated subqueries;

3. separating session-level checks from row-level checks;

4. improving indexes used by permission lookups;

5. reducing unnecessary repeated authorization checks;

6. restructuring the permission model so that access can be determined more directly.

This article from Supabase is a great introduction to optimization techniques you can add your slow queries which use RLS here. 

The objective is not simply to make the query faster. The optimization must preserve the existing security guarantees while making authorization evaluation more scalable.

The first step is to analyze the actual execution plan using (A PostgreSQL command that actually executes a query and shows how it performed, including execution time and how many times operations ran.):

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...;

This allows us to identify which authorization checks contribute most to execution time and where the repeated processing occurs.

Discovering Performance Problems with Supabase RLS Early

Performance problems are much easier to solve when they are discovered before production. One of the biggest challenges is that development and test environments often contain too little data to expose problems that will appear at scale.

A query that performs well with a small dataset may behave very differently when the database contains thousands or millions of records. This is especially relevant for RLS, where authorization checks can be applied across large numbers of rows and relationships.

The first step is therefore to test against a realistic amount of data. The test environment should contain representative volumes of users, records, relationships, and permissions. The goal is not to reproduce production exactly, but to create enough data for queries, RLS policies, indexes, joins, and other database operations to behave realistically.

End-to-End Performance Testing

Performance testing should also represent how users actually interact with the application.

A good solution is to combine Playwright and Artillery, we wrote about that already here:

• Playwright represents realistic end-user journeys through the application.

• Artillery generates load and measures how the application behaves as the number of users increases.

  
  

This approach allows performance problems to be discovered at the level where users experience them, while still providing enough load to expose backend and database bottlenecks.

The tests should cover representative critical journeys rather than attempting to run the entire E2E test suite under load.

What the tests should reveal

The goal is not simply to measure whether a page is fast. Performance testing should help identify how the system behaves as data volume and user load increase.

Important signals include:

• response and transaction latency;

• p95/p99 performance;

• error rates;

• throughput;

• database utilization;

• infrastructure utilization;

• slow or increasingly expensive database queries.

  
  

These results should be correlated with application logs, traces, and database metrics to identify the underlying bottleneck.

For example, an apparently slow page may ultimately be caused by a database query whose execution time increases significantly with data volume, or by RLS policies that introduce repeated authorization checks.

Detect problems before they become incidents

The most valuable outcome is discovering scaling problems before the application reaches production scale.

A useful testing cycle is:

1. Realistic data

2. Representative user journeys (think about using Gherkin, our article about that here)

3. Increasing load

4. Performance measurements

5. Observability & database analysis

6. Identify bottlenecks

7. Fix and test again

   
   

This turns performance from a reactive activity into an engineering feedback loop.

For database-heavy applications, this is particularly important because problems such as inefficient RLS evaluation, missing indexes, expensive joins, or poorly scaling queries may remain completely invisible with a small development dataset.

The earlier these problems are discovered, the more options the team has to fix them before they become production performance issues.

From Reactive to Proactive Performance Management

Performance testing is only one part of the solution. It is also advisable to introduce an observability layer that allows user actions to be correlated with concrete database/API requests and Edge Function executions.

This creates visibility across the entire user journey:

1. User action

2. Application

3. Supabase request / Edge Function

4. Database operation

5. Performance & errors

With this visibility, the team does not have to wait for a client to report that "the application is slow." Performance degradation can be detected, investigated, and addressed before it becomes a user-facing incident.

This changes the operating model from reactive to proactive: instead of discovering problems when clients experience them, the team can identify emerging performance issues and investigate them before they reach the customer.

In our next case study, we will show how we introduced this approach for our clients and how combining realistic performance testing with end-to-end observability created an early-warning system for application and database performance problems.

And here you can read our case study from performance audit.