High latency with supabase client

[bonsaibuddy-unchartedlabs]

This is a copy of a troubleshooting article on Supabase's docs site. It may be missing some details from the original. View the original article.

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.

-- Create table
CREATE TABLE your_table_name (
    id UUID PRIMARY KEY,
    column1 TEXT,
    column2 INT,
    column3 BOOLEAN
);

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

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

import time
from supabase import Client, create_client

import psycopg


def psycop_call(): #user_ids: list[str]):
    user="YOUR_SUPABASE_USER"
    password="YOUR_SUPABASE_PASSWORD"
    host="SUPABASE_HOST"
    port=5432
    database="postgres"

    with psycopg.connect(f"host={host} port={port} dbname={database} user={user} password={password}") as conn:
        # Open a cursor to perform database operations
        results = []
        with conn.cursor() as cur:
            start = time.time()
            # Execute a command: this creates a new table
            cur.execute("SELECT * FROM public.your_table_name")
            cur.fetchall()
            for record in cur:
                results.append(record)
            stop = time.time()
            return (stop - start)


def supabase_call():
    supabase: Client = create_client("SUPABASE_URL", "SUPBASE_SERVICE_ROLE_KEY")
    start = time.time()
    result = supabase.table("your_table_name").select("*").execute()
    stop = time.time()
    return (stop - start)


if __name__ == "__main__":
    ref = psycop_call()
    sup = supabase_call()
    print(f"postgres: {ref}, supabase: {sup}, ratio: {sup/ref}")

3. 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.

[encima]

Converting to discussion for the following reasons:

• Issue may not have a direct fix as latency can be impacted by a number of different factors
• More information on the setup is needed (region of instance, region of client, methods of connecting when not using PostgREST)
• Timings experienced across both methods and sustained benchmarks

Lets continue to discuss here and re-open as an issue if there is a single source identified

[bonsaibuddy-unchartedlabs]

Sorry, why is this a discussion? I provided an exact script for replicating it on any machine.

• Issue may not have a direct fix as latency can be impacted by a number of different factors
  Ok, maybe that's the case, but we don't know right now?

• More information on the setup is needed (region of instance, region of client, methods of connecting when not using PostgREST)
  You can run the above script against any supabase database with the postgres login/pw and appropriate supabase anon/role api key.

• Timings experienced across both methods and sustained benchmarks
  Again, I provided the most minimal script possible (just fill in the relevant credentials) and a basic create table command which can replicate the scenario in like 10 minutes.

[encima]

This can be a discussion until we have identified if the source of the issue is in configuration or the application.

Using your creation example, I see timings of 50ms for a direct connection and 82ms for the REST API so we would need to see timings and something to benchmark against. Thank you for providing the steps, please also provide your results and setup. i.e. location, deployed region, ipv6 support etc

With many issues to work on, if you can make those 10 minutes shorter and comparable then it is a great help to the team here; as well as the contributors 🙏