Why am I maxing out my available Supabase connections?

Use case

"I'm seeing an error like this one, when connecting to my Postgres instance:"

Connection to `db.cpmambtxtorfkxeerdij.supabase.co:5432` failed with error: connection to server at "db.cpmambtxtorfkxeerdij.supabase.co" (54.215.149.69), port 5432 failed: FATAL:  remaining connection slots are reserved for non-replication superuser connections

TLDR

Without compute add-ons, you get 60 default connections from Supabase.
Switch to the Supavisor (pooled) connection strings, to get up to 200 concurrent connections.
Turn on transaction mode (instead of session mode) from the Supabase database settings page.
Check what size compute you're using in Supabase, and increase compute add-ons to get even more.

Solution

We also experienced this when getting started with Supabase, and here's how to fix it. This should help your app, and make Velvet work better too.

Supabase deprecated the default connections strings a while ago (see here). Without compute add-ons, you get 60 default connections. Supabase itself uses up a good amount of your allotted connections, so a handful of users accessing your app simultaneously can easily use all these up.

You can switch to the Supavisor (pooled) connection strings, to get up to 200 concurrent connections. Then Increase compute add-ons to get even more as needed. Read more about optimizing Supabase connections.

Steps to fix it:

Go to your Supabase project settings here.
The connection string box has been updated to the new pooled connection string
Switch the postgres user with your read-only user and add the password.
Update your Velvet remote config connection string (URI).
You'll probably need to do the same thing in your main app.

Example from Velvet's read-only user setup:

-- Create a new user with read-only access to the database
-- Recommend to run each step one by one, as some steps may
-- fail if the user does not have the necessary privileges.

-- Step 1: Create the user
CREATE USER velvet_readonly WITH LOGIN PASSWORD 'your_secure_password';

-- Step 2: Grant connect privilege to the database 
-- (replace your_database_name with the actual name - on supabase it's postgres)
GRANT CONNECT ON DATABASE postgres TO velvet_readonly;

-- Step 3: Grant usage on schemas. This allows the user to see the schemas
GRANT USAGE ON SCHEMA public TO velvet_readonly;
GRANT USAGE ON SCHEMA auth TO velvet_readonly; -- optional

-- Step 4: Grant select on all tables and views in the schemas
GRANT SELECT ON ALL TABLES IN SCHEMA public TO velvet_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA auth TO velvet_readonly;

-- Step 5: Ensure future tables and views in these schemas are also accessible
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO velvet_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA auth GRANT SELECT ON TABLES TO velvet_readonly;

-- [OPTIONAL] Step 6: Bypass RLS policies
ALTER ROLE velvet_readonly BYPASSRLS;

Keep an eye on what size compute you're using. Supabase scales up the default max connections per db user depending on your setup (basically there is some overhead to run each db user). Configure the per db user max connections on Supabase here.

Additionally - Supabase recently switched the default to session mode which does not immediately release connections. This means you can overload your database if you're using either (1) a lot of database queries simultaneously or (2) you have a serverless architecture like we do.

Read another knowledge base help article to resolve this issue if you're experiencing it - How do I optimize my Supabase connection pooler with Velvet?

Use case

TLDR

Solution

Suggested reading on this topic