Recently we experienced some inserts were happening at a progressively slower pace in our application. (We make a tool that helps developers build and sell interactive programming courses). Inserting a new block type into a lesson was taking over 3 seconds!
After some investigation we discovered a Row Level Security policy was slowing down inserts on our lesson content table. We found a way to test RLS performance and narrow down the issue. We'll explore how to do the same for your application in this tutorial.
I tweeted a before and after if you'd like to visually see the speed improvement we were able to make.
How Row Level Security (RLS) and Supabase Work Together
RLS works with Supabase through a combination of the GoTrue Auth and PostgREST part of their system. (Supabase is comprised of a stack of open-source tools including PostgreSQL, PostgREST, and GoTrue).
PostgREST is a way to easily serve an API for your Postgres database. Supabase uses PostgREST for their auto-generated API, which can be used via one of the Supabase client libraries.
We use the combination of supabase-js + Supabase Auth + Row Level Security policies to authenticate and filter queries based on authenticated users.
This combination makes it easy to write client-side sql calls without manually filtering. RLS policies handle the filtering automatically based on who the authenticated user is.
Check out this code example from the Supabase docs to get an idea of how it simplifies your client code.
Instead of writing the query filter when you make the database call
If you're authenticated via Supabase Auth, a
uuid representing your user account is passed in through a JWT to the PostgREST endpoint for your Supabase project on every call.
From there, the RLS policy for that table is applied, allowing you to only interact with the data the policy specifies you have access to.
In the example above, the RLS policy is setup to only allow a user to select their own data.
Why you can't test RLS inside the Supabase SQL editor
If you're experiencing slow Postgres queries, chances are you want to use the Postgres
EXPLAIN command to dive deeper into what is causing the slow down.
The first thing we did when we noticed the slow insert was to test it out inside the Supabase sql editor in the browser.
We ran a query like this and got the following result.
Insert only took 1.697ms. It didn't have the same slowdown we were experiencing in prod. Remember, the query was taking over 3 seconds!
The reason why the slowdown didn't occur in the Supabase SQL editor is that RLS policies are disabled for the user
supabase_admin. When you run queries inside the Supabase SQL editor, they are ran as the user
Running EXPLAIN as an authenticated user
To test your RLS policy performance, you'll have to run a query as an authenticated user. Before you can do this, you'll need to set the
postgres user as a superuser.
Head to the web based sql editor and run the following command to make your
postgres user a superuser.
Next, you need to connect to your database as the
postgres user. You won't be able to run queries as the
postgres user inside the web based sql editor. You can find your connection string details inside of your Supabase dashboard.
After you connect to your database outside of the Supabase UI as the
postgres user, you're ready to run your commands as an authenticated user.
These two lines will let you run a query as any authenticated user who's uuid you set for
We can now run our same query from above and find out what is slowing it down.
From here, we pasted the resulting query plan into this visualizer tool called explain.dalibo.
Here's an example shot of the visualization you'll get.
In our case, we had an expensive RLS policy that included tallying up the total amount of lesson blocks in a lesson before allowing insert using a function called
total_lesson_blocks_for_lesson(). It was slowing down inserts because it scanned every row in the lesson contents table before every insert. The function looked something like this:
We remedied the issue by removing the restriction for total lesson blocks from our RLS policies.
Ultimately we brought insert times at the database level down from 1.5s to just 3ms by making the change!
If you'd like to learn more Advanced Supabase Techniques, check out our course! Or if you want some more free content take a peak at how we manage Supabase Migrations in another tutorial I wrote.
If you have any feedback on this article, shoot me a dm on twitter, or email me at firstname.lastname@example.org.