claude-sonnet-5 · d2_pgvector · rep 2

env | grep -i supabase | sed -E 's/(KEY|PASSWORD)=.*/\1=***REDACTED***/'

SUPABASE_DB_PASSWORD=***REDACTED***
SUPABASE_DB_URL=<redacted:SUPABASE_DB_URL>
SUPABASE_URL=https://<ref>.supabase.co
SUPABASE_SERVICE_ROLE_KEY=***REDACTED***
SUPABASE_PROJECT_REF=<ref>
SUPABASE_ANON_KEY=***REDACTED***

which psql curl jq 2>&1; psql --version 2>&1

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

psql "$SUPABASE_DB_URL" -c "\d public.documents"
echo "---"
psql "$SUPABASE_DB_URL" -c "select id, title, embedding_json from public.documents order by id;"
echo "---extensions---"
psql "$SUPABASE_DB_URL" -c "select * from pg_available_extensions where name='vector';"

                 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)

---
 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)

---extensions---
  name  | default_version | installed_version |                       comment                        
--------+-----------------+-------------------+------------------------------------------------------
 vector | 0.8.2           |                   | vector data type and ivfflat and hnsw access methods
(1 row)

psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 <<'SQL'
-- 1) Enable pgvector extension
create extension if not exists vector;

-- 2) Add embedding column of type vector(4)
alter table public.documents
  add column if not exists embedding vector(4);

-- Populate it from embedding_json
update public.documents
set embedding = (
  select array_agg(x)::vector(4)
  from jsonb_array_elements_text(embedding_json) as t(x_text)
       cross join lateral (select x_text::float8 as x) as e
)
where embedding is null;

select id, title, embedding_json, embedding from public.documents order by id;
SQL

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

psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 <<'SQL'
-- 3) HNSW index using cosine distance
create index if not exists documents_embedding_hnsw_cosine_idx
  on public.documents
  using hnsw (embedding vector_cosine_ops);

-- 4) RPC function for semantic search
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;
$$;

-- Allow it to be called via REST RPC by anon/authenticated roles
grant execute on function public.match_documents(vector(4), int) to anon, authenticated, service_role;

\df+ public.match_documents
SQL

CREATE INDEX
CREATE FUNCTION
GRANT
                                                                                                                            List of functions
 Schema |      Name       |                     Result data type                     |             Argument data types             | Type | Volatility | Parallel |  Owner   | Security | Leakproof? |    Access privileges     | Language | Internal name | Description 
--------+-----------------+----------------------------------------------------------+---------------------------------------------+------+------------+----------+----------+----------+------------+--------------------------+----------+---------------+-------------
 public | match_documents | TABLE(id integer, title text, distance double precision) | query_embedding vector, match_count integer | func | stable     | unsafe   | postgres | invoker  | no         | =X/postgres             +| sql      |               | 
        |                 |                                                          |                                             |      |            |          |          |          |            | postgres=X/postgres     +|          |               | 
        |                 |                                                          |                                             |      |            |          |          |          |            | anon=X/postgres         +|          |               | 
        |                 |                                                          |                                             |      |            |          |          |          |            | authenticated=X/postgres+|          |               | 
        |                 |                                                          |                                             |      |            |          |          |          |            | service_role=X/postgres  |          |               | 
(1 row)

psql "$SUPABASE_DB_URL" -c "NOTIFY pgrst, 'reload schema';"
sleep 3
echo "notified"

NOTIFY
notified

echo "== Query near alpha/bravo direction: [1, 0.3, 0, 0] =="
curl -s -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, 0.3, 0, 0], "match_count": 3}' | jq .

echo
echo "== Query near golf direction: [0.1, 0.1, 3, 4] =="
curl -s -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": [0, 0, 3, 4], "match_count": 3}' | jq .

echo
echo "== Query near delta direction: [20, 60, 0, 0] =="
curl -s -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": [21, 59, 0, 0], "match_count": 6}' | jq .

== Query near alpha/bravo direction: [1, 0.3, 0, 0] ==
[
  {
    "id": 2,
    "title": "bravo",
    "distance": 0.000458813290824645
  },
  {
    "id": 1,
    "title": "alpha",
    "distance": 0.0290193401171952
  },
  {
    "id": 3,
    "title": "delta",
    "distance": 0.42450660263693
  }
]

== Query near golf direction: [0.1, 0.1, 3, 4] ==
[
  {
    "id": 6,
    "title": "golf",
    "distance": 0.000399769304185948
  },
  {
    "id": 1,
    "title": "alpha",
    "distance": 1
  },
  {
    "id": 2,
    "title": "bravo",
    "distance": 1
  }
]

== Query near delta direction: [20, 60, 0, 0] ==
[
  {
    "id": 3,
    "title": "delta",
    "distance": 0.000203998370135627
  },
  {
    "id": 4,
    "title": "echo",
    "distance": 0.0578973682822852
  },
  {
    "id": 2,
    "title": "bravo",
    "distance": 0.383964097327088
  },
  {
    "id": 1,
    "title": "alpha",
    "distance": 0.618047345677281
  },
  {
    "id": 6,
    "title": "golf",
    "distance": 0.974461667579548
  },
  {
    "id": 5,
    "title": "foxtrot",
    "distance": 1.31641932903244
  }
]

psql "$SUPABASE_DB_URL" <<'SQL'
select extname, extversion from pg_extension where extname='vector';
select indexname, indexdef from pg_indexes where tablename='documents';
explain select id from public.documents order by embedding <=> '[1,0.3,0,0]'::vector(4) limit 3;
SQL

 extname | extversion 
---------+------------
 vector  | 0.8.2
(1 row)

              indexname              |                                                   indexdef                                                    
-------------------------------------+---------------------------------------------------------------------------------------------------------------
 documents_pkey                      | CREATE UNIQUE INDEX documents_pkey ON public.documents USING btree (id)
 documents_embedding_hnsw_cosine_idx | CREATE INDEX documents_embedding_hnsw_cosine_idx ON public.documents USING hnsw (embedding vector_cosine_ops)
(2 rows)

                              QUERY PLAN                              
----------------------------------------------------------------------
 Limit  (cost=1.15..1.16 rows=3 width=12)
   ->  Sort  (cost=1.15..1.17 rows=6 width=12)
         Sort Key: ((embedding <=> '[1,0.3,0,0]'::vector(4)))
         ->  Seq Scan on documents  (cost=0.00..1.07 rows=6 width=12)
(4 rows)

psql "$SUPABASE_DB_URL" <<'SQL'
set enable_seqscan=off;
explain select id from public.documents order by embedding <=> '[1,0.3,0,0]'::vector(4) limit 3;
reset enable_seqscan;
SQL

SET
                                                 QUERY PLAN                                                 
------------------------------------------------------------------------------------------------------------
 Limit  (cost=2.25..2.83 rows=3 width=12)
   ->  Index Scan using documents_embedding_hnsw_cosine_idx on documents  (cost=2.25..3.42 rows=6 width=12)
         Order By: (embedding <=> '[1,0.3,0,0]'::vector(4))
(3 rows)

RESET