High latency with supabase client

描述一下这个漏洞 #

🌐 Describe the bug

使用 Supabase 客户端查询表比直接对 Postgres 数据库查询要慢很多。

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

繁殖 #

🌐 To reproduce

执行下面的DDL语句。

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

运行以下脚本（你可能除了 Supabase 客户端外，还需要 pip install psycopg[binary]。

1
import time
2
from supabase import Client, create_client
3
4
import psycopg
5
6
7
def psycop_call(): #user_ids: list[str]):
8
    user="YOUR_SUPABASE_USER"
9
    password="YOUR_SUPABASE_PASSWORD"
10
    host="SUPABASE_HOST"
11
    port=5432
12
    database="postgres"
13
14
    with psycopg.connect(f"host={host} port={port} dbname={database} user={user} password={password}") as conn:
15
        # Open a cursor to perform database operations
16
        results = []
17
        with conn.cursor() as cur:
18
            start = time.time()
19
            # Execute a command: this creates a new table
20
            cur.execute("SELECT * FROM public.your_table_name")
21
            cur.fetchall()
22
            for record in cur:
23
                results.append(record)
24
            stop = time.time()
25
            return (stop - start)
26
27
28
def supabase_call():
29
    supabase: Client = create_client("SUPABASE_URL", "SUPABASE_SECRET_KEY")
30
    start = time.time()
31
    result = supabase.table("your_table_name").select("*").execute()
32
    stop = time.time()
33
    return (stop - start)
34
35
36
if __name__ == "__main__":
37
    ref = psycop_call()
38
    sup = supabase_call()
39
    print(f"postgres: {ref}, supabase: {sup}, ratio: {sup/ref}")

你会发现，Supabase 客户端执行同样的查询需要更长的时间，尤其是对于较小的表或只返回一行的查询。

预期行为 #

🌐 Expected behavior

PostgREST 的开销最多也不应该超过几毫秒。60-70 毫秒就太高了。这尤其容易误导，因为你可以在 SQL 编辑器页面运行查询，它显示的时间和直接在 Postgres 中查询的时间一样，但实际上并不是这样的。

🌐 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 happens.