Vector Databases: Turso, pgvector & RAG · lesson 3 of 4

Vector Search on Supabase (pgvector)

Supabase's vector story is plain pgvector — the Postgres extension — plus Supabase's client conventions for calling it. If you know pgvector, you know Supabase vectors; the Supabase-specific part is how you get queries past PostgREST.

Setup

create extension vector;

create table documents (
  id bigint primary key generated always as identity,
  content text,
  embedding vector(384)   -- dimension fixed at DDL time
);

vector(384) enforces dimensionality: inserting or comparing a different-length embedding is a hard error (see rag-fundamentals gotchas).

Distance operators

pgvector exposes distances as operators (contrast the named functions in turso-vector-search):

Operator	Distance	Note
`<->`	L2 (Euclidean)
`<=>`	cosine distance	most common for text embeddings
`<#>`	negative inner product	fastest when vectors are normalized — for unit vectors inner product ranks identically to cosine, minus the normalization arithmetic

Similarity conversion you'll write constantly:

1 - (embedding <=> query_embedding)  -- cosine similarity in [-1, 1]

Indexing

Two ANN index types; HNSW is recommended over IVFFlat — better recall/speed trade-off and no dependency on a training step over existing rows:

create index on documents using hnsw (embedding vector_cosine_ops);

Match the opclass to your query operator (vector_cosine_ops for <=>, vector_l2_ops for <->, vector_ip_ops for <#>), or the index is silently unused.

The PostgREST problem → RPC pattern

Supabase's auto-generated REST API (PostgREST) cannot express vector operators — there's no ?embedding=<=>... filter syntax. The standard workaround: wrap the query in a SQL function and call it via RPC.

create or replace function match_documents(
  query_embedding vector(384),
  match_threshold float,
  match_count int
)
returns table (id bigint, content text, similarity float)
language sql stable as $$
  select d.id, d.content,
         1 - (d.embedding <=> query_embedding) as similarity
  from documents d
  where 1 - (d.embedding <=> query_embedding) > match_threshold
  order by d.embedding <=> query_embedding
  limit match_count;
$$;

const { data } = await supabase.rpc('match_documents', {
  query_embedding: embedding,   // number[] from your embedding model
  match_threshold: 0.7,
  match_count: 5,
});

Note the order by embedding <=> query_embedding — ordering by the raw operator expression (not the aliased similarity) is what lets the HNSW index serve the scan. RLS applies to functions like any query, so the RLS policies on documents still govern what rows a user can match.

Hybrid search

Combine a keyword arm (Postgres full-text search: tsvector column ranked with ts_rank) with the vector arm, fused by Reciprocal Rank Fusion exactly as in rag-fundamentals:

-- per arm, compute rank; then fuse:
coalesce(1.0 / (60 + fts.rank), 0) + coalesce(1.0 / (60 + vec.rank), 0)

Full-outer-join the two ranked CTEs on document id so a document scoring in only one arm still surfaces.

Key terms

pgvector — the Postgres extension providing the vector type, distance operators, and ANN indexes; Supabase's native vector store.
vector(n) — column type with fixed dimensionality n, enforced at insert and comparison time.
<-> / <=> / <#> — pgvector's L2, cosine-distance, and negative-inner-product operators respectively.
Negative inner product (<#>) — the fastest distance when embeddings are normalized to unit length, ranking identically to cosine in that case.
HNSW — graph-based ANN index, recommended over IVFFlat on Supabase for its recall/speed trade-off.
IVFFlat — cluster-based ANN index requiring training on existing rows; the older alternative to HNSW.
PostgREST limitation — Supabase's REST layer cannot express vector operators, so vector queries must be wrapped in SQL functions.
match_documents RPC — the conventional SQL function (query_embedding, match_threshold, match_count) called via supabase.rpc() to run similarity search.
ts_rank / tsvector — Postgres full-text search primitives forming the keyword arm of hybrid search.
RRF fusion (1/(60+rank)) — the reciprocal-rank formula used to merge keyword and vector arms into one ranking.