High latency with supabase client

Last edited: 1/17/2025

Describe the bug

Querying a table using the Supabase client is much slower than querying against the Postgres database directly.

To reproduce

  1. Execute the below DDL statements.

_30
-- Create table
_30
CREATE TABLE your_table_name (
_30
id UUID PRIMARY KEY,
_30
column1 TEXT,
_30
column2 INT,
_30
column3 BOOLEAN
_30
);
_30
_30
-- Insert statements
_30
INSERT INTO your_table_name (id, column1, column2, column3) VALUES
_30
(uuid_generate_v4(), 'value1', 10, TRUE),
_30
(uuid_generate_v4(), 'value2', 20, FALSE),
_30
(uuid_generate_v4(), 'value3', 15, TRUE),
_30
(uuid_generate_v4(), 'value4', 8, FALSE),
_30
(uuid_generate_v4(), 'value5', 25, TRUE),
_30
(uuid_generate_v4(), 'value6', 12, FALSE),
_30
(uuid_generate_v4(), 'value7', 18, TRUE),
_30
(uuid_generate_v4(), 'value8', 30, FALSE),
_30
(uuid_generate_v4(), 'value9', 22, TRUE),
_30
(uuid_generate_v4(), 'value10', 5, FALSE),
_30
(uuid_generate_v4(), 'value11', 17, TRUE),
_30
(uuid_generate_v4(), 'value12', 9, FALSE),
_30
(uuid_generate_v4(), 'value13', 14, TRUE),
_30
(uuid_generate_v4(), 'value14', 28, FALSE),
_30
(uuid_generate_v4(), 'value15', 11, TRUE),
_30
(uuid_generate_v4(), 'value16', 7, FALSE),
_30
(uuid_generate_v4(), 'value17', 19, TRUE),
_30
(uuid_generate_v4(), 'value18', 26, FALSE),
_30
(uuid_generate_v4(), 'value19', 16, TRUE),
_30
(uuid_generate_v4(), 'value20', 21, FALSE);

  1. Run the following script (you may need to pip install psycopg[binary] in addition to supabase client.

_39
import time
_39
from supabase import Client, create_client
_39
_39
import psycopg
_39
_39
_39
def psycop_call(): #user_ids: list[str]):
_39
user="YOUR_SUPABASE_USER"
_39
password="YOUR_SUPABASE_PASSWORD"
_39
host="SUPABASE_HOST"
_39
port=5432
_39
database="postgres"
_39
_39
with psycopg.connect(f"host={host} port={port} dbname={database} user={user} password={password}") as conn:
_39
# Open a cursor to perform database operations
_39
results = []
_39
with conn.cursor() as cur:
_39
start = time.time()
_39
# Execute a command: this creates a new table
_39
cur.execute("SELECT * FROM public.your_table_name")
_39
cur.fetchall()
_39
for record in cur:
_39
results.append(record)
_39
stop = time.time()
_39
return (stop - start)
_39
_39
_39
def supabase_call():
_39
supabase: Client = create_client("SUPABASE_URL", "SUPBASE_SERVICE_ROLE_KEY")
_39
start = time.time()
_39
result = supabase.table("your_table_name").select("*").execute()
_39
stop = time.time()
_39
return (stop - start)
_39
_39
_39
if __name__ == "__main__":
_39
ref = psycop_call()
_39
sup = supabase_call()
_39
print(f"postgres: {ref}, supabase: {sup}, ratio: {sup/ref}")

  1. You will see that the Supabase client takes longer to execute the same query, especially for smaller tables or queries returning just one row.

Expected behavior

The overhead from PostgREST shouldn't be higher than a few milliseconds at max. 60-70 ms is way too high. This is particular deceiving because one can run the query on the SQL Editor page and it reports the same time as the direct postgres query, which is not what actually happens.