minimax/minimax-m3 · d2_pgvector · rep 2

env | grep -i supabase | sed 's/=.*/=<redacted>/'

SUPABASE_DB_PASSWORD=<redacted>
SUPABASE_DB_URL=<redacted>
SUPABASE_URL=<redacted>
SUPABASE_SERVICE_ROLE_KEY=<redacted>
SUPABASE_PROJECT_REF=<redacted>
SUPABASE_ANON_KEY=<redacted>

which psql && psql --version

/usr/bin/psql
psql (PostgreSQL) 18.4 (Debian 18.4-1.pgdg12+1)

which curl && curl --version | head -1

/usr/bin/curl
curl 7.88.1 (x86_64-pc-linux-gnu) libcurl/7.88.1 OpenSSL/3.0.20 zlib/1.2.13 brotli/1.0.9 zstd/1.5.4 libidn2/2.3.3 libpsl/0.21.2 (+libidn2/2.3.3) libssh2/1.10.0 nghttp2/1.52.0 librtmp/2.3 OpenLDAP/2.5.13

echo "URL: $SUPABASE_URL"; echo "REF: $SUPABASE_PROJECT_REF"

URL: https://<ref>.supabase.co
REF: <ref>

psql "$SUPABASE_DB_URL" -c "SELECT current_database(), current_user, version();" 2>&1 | head -20

 current_database | current_user |                                   version                                    
------------------+--------------+------------------------------------------------------------------------------
 postgres         | postgres     | PostgreSQL 17.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 15.2.0, 64-bit
(1 row)

psql "$SUPABASE_DB_URL" -c "\d public.documents"

                 Table "public.documents"
     Column     |  Type   | Collation | Nullable | Default 
----------------+---------+-----------+----------+---------
 id             | integer |           | not null | 
 title          | text    |           | not null | 
 embedding_json | jsonb   |           | not null | 
Indexes:
    "documents_pkey" PRIMARY KEY, btree (id)

psql "$SUPABASE_DB_URL" -c "SELECT id, title, embedding_json FROM public.documents ORDER BY id;"

 id |  title  |  embedding_json  
----+---------+------------------
  1 | alpha   | [10, 0.5, 0, 0]
  2 | bravo   | [0.9, 0.3, 0, 0]
  3 | delta   | [20, 60, 0, 0]
  4 | echo    | [0, 1, 0, 0]
  5 | foxtrot | [-5, 0.1, 0, 0]
  6 | golf    | [0.1, 0.1, 3, 4]
(6 rows)

psql "$SUPABASE_DB_URL" -c "SELECT extname, extversion FROM pg_extension;"

      extname       | extversion 
--------------------+------------
 plpgsql            | 1.0
 pg_stat_statements | 1.11
 uuid-ossp          | 1.1
 pgcrypto           | 1.3
 supabase_vault     | 0.3.1
(5 rows)

psql "$SUPABASE_DB_URL" -c "CREATE EXTENSION IF NOT EXISTS vector;" 2>&1

CREATE EXTENSION

psql "$SUPABASE_DB_URL" <<'SQL'
ALTER TABLE public.documents
  ADD COLUMN embedding vector(4);

UPDATE public.documents
  SET embedding = (array_to_string(
    ARRAY(SELECT jsonb_array_elements_text(embedding_json)), ',')::float8[])::vector(4);

SELECT id, title, embedding_json, embedding
FROM public.documents
ORDER BY id;
SQL

ALTER TABLE
 id |  title  |  embedding_json  | embedding 
----+---------+------------------+-----------
  1 | alpha   | [10, 0.5, 0, 0]  | 
  2 | bravo   | [0.9, 0.3, 0, 0] | 
  3 | delta   | [20, 60, 0, 0]   | 
  4 | echo    | [0, 1, 0, 0]     | 
  5 | foxtrot | [-5, 0.1, 0, 0]  | 
  6 | golf    | [0.1, 0.1, 3, 4] | 
(6 rows)

ERROR:  malformed array literal: "10,0.5,0,0"
DETAIL:  Array value must start with "{" or dimension information.

psql "$SUPABASE_DB_URL" <<'SQL'
UPDATE public.documents
  SET embedding = (
    SELECT array_agg(x::float8 ORDER BY ord)
    FROM jsonb_array_elements(embedding_json) WITH ORDINALITY AS e(x, ord)
  )::vector(4);

SELECT id, title, embedding, embedding <-> '[1,1,1,1]'::vector AS d_to_1111
FROM public.documents
ORDER BY id;
SQL

UPDATE 6
 id |  title  |   embedding   |    d_to_1111     
----+---------+---------------+------------------
  1 | alpha   | [10,0.5,0,0]  | 9.12414379544733
  2 | bravo   | [0.9,0.3,0,0] | 1.58113883008419
  3 | delta   | [20,60,0,0]   |               62
  4 | echo    | [0,1,0,0]     | 1.73205080756888
  5 | foxtrot | [-5,0.1,0,0]  |  6.2297673610891
  6 | golf    | [0.1,0.1,3,4] |  3.8236107191874
(6 rows)

psql "$SUPABASE_DB_URL" <<'SQL'
CREATE INDEX documents_embedding_hnsw
  ON public.documents
  USING hnsw (embedding vector_cosine_ops);
SQL

CREATE INDEX

psql "$SUPABASE_DB_URL" <<'SQL'
CREATE OR REPLACE FUNCTION public.match_documents(
  query_embedding vector(4),
  match_count int
)
RETURNS TABLE (id int, title text, distance float8)
LANGUAGE sql
STABLE
AS $$
  SELECT d.id, d.title, (d.embedding <=> query_embedding)::float8 AS distance
  FROM public.documents d
  ORDER BY d.embedding <=> query_embedding
  LIMIT match_count;
$$;
SQL

CREATE FUNCTION

curl -sS -X POST "$SUPABASE_URL/rest/v1/rpc/match_documents" \
  -H "apikey: $SUPABASE_ANON_KEY" \
  -H "Authorization: Bearer $SUPABASE_ANON_KEY" \
  -H "Content-Type: application/json" \
  -d '{"query_embedding": [1, 1, 1, 1], "match_count": 3}' \
  -w "\nHTTP %{http_code}\n"

[{"id":6,"title":"golf","distance":0.280287852964875}, 
 {"id":2,"title":"bravo","distance":0.367544434457624}, 
 {"id":3,"title":"delta","distance":0.367544467966324}]
HTTP 200

curl -sS -X POST "$SUPABASE_URL/rest/v1/rpc/match_documents" \
  -H "apikey: $SUPABASE_ANON_KEY" \
  -H "Authorization: Bearer $SUPABASE_ANON_KEY" \
  -H "Content-Type: application/json" \
  -d '{"query_embedding": [10, 0.5, 0, 0], "match_count": 3}' \
  -w "\nHTTP %{http_code}\n"

[{"id":1,"title":"alpha","distance":0}, 
 {"id":2,"title":"bravo","distance":0.0367087035723488}, 
 {"id":3,"title":"delta","distance":0.636791795933452}]
HTTP 200

psql "$SUPABASE_DB_URL" <<'SQL'
EXPLAIN (COSTS OFF) SELECT d.id, d.title, (d.embedding <=> '[10,0.5,0,0]'::vector)::float8 AS distance
FROM public.documents d
ORDER BY d.embedding <=> '[10,0.5,0,0]'::vector
LIMIT 3;
SQL

                         QUERY PLAN                         
------------------------------------------------------------
 Limit
   ->  Sort
         Sort Key: ((embedding <=> '[10,0.5,0,0]'::vector))
         ->  Seq Scan on documents d
(4 rows)

psql "$SUPABASE_DB_URL" <<'SQL'
\d public.documents
SELECT proname, pronargs, prorettype::regtype, proacl FROM pg_proc WHERE proname='match_documents';
SELECT grantee, privilege_type FROM information_schema.routine_privileges WHERE routine_name='match_documents';
SQL

                  Table "public.documents"
     Column     |   Type    | Collation | Nullable | Default 
----------------+-----------+-----------+----------+---------
 id             | integer   |           | not null | 
 title          | text      |           | not null | 
 embedding_json | jsonb     |           | not null | 
 embedding      | vector(4) |           |          | 
Indexes:
    "documents_pkey" PRIMARY KEY, btree (id)
    "documents_embedding_hnsw" hnsw (embedding vector_cosine_ops)

     proname     | pronargs | prorettype |                                               proacl                                               
-----------------+----------+------------+----------------------------------------------------------------------------------------------------
 match_documents |        2 | record     | {=X/postgres,postgres=X/postgres,anon=X/postgres,authenticated=X/postgres,service_role=X/postgres}
(1 row)

    grantee    | privilege_type 
---------------+----------------
 service_role  | EXECUTE
 authenticated | EXECUTE
 anon          | EXECUTE
 postgres      | EXECUTE
 PUBLIC        | EXECUTE
(5 rows)

curl -sS -X POST "$SUPABASE_URL/rest/v1/rpc/match_documents" \
  -H "apikey: $SUPABASE_ANON_KEY" \
  -H "Authorization: Bearer $SUPABASE_ANON_KEY" \
  -H "Content-Type: application/json" \
  -H "Accept: application/json" \
  -d '{"query_embedding": [0.1, 0.1, 3, 4], "match_count": 6}' \
  -w "\nHTTP %{http_code}\n"

[{"id":6,"title":"golf","distance":0}, 
 {"id":3,"title":"delta","distance":0.974711892170972}, 
 {"id":2,"title":"bravo","distance":0.974711892401252}, 
 {"id":1,"title":"alpha","distance":0.979034586516748}, 
 {"id":4,"title":"echo","distance":0.98000799508818}, 
 {"id":5,"title":"foxtrot","distance":1.01958824755548}]
HTTP 200