I love Supabase, but for a while I was also using Weaviate for the semantic search part of my app. It worked well. It also meant I had two databases to maintain, two sets of credentials, and two places where my data could get out of sync.

Eventually I got tired of that and gave pgvector a try. The setup was much simpler than I expected.

First, enable the extension:

create extension if not exists vector with schema public;

Then add an embedding column to the table that already holds the data you want to search:

alter table public.documents
  add column embedding vector(1536),
  add column needs_embedding_update boolean default true;

The 1536 needs to match the number of dimensions returned by your embedding model. The names in these examples are anonymized, but the shape is the same as what I use.

Next, decide which text columns should contribute to the embedding. In this example, I combine summary and body. A small trigger marks the row as stale whenever either one changes:

create or replace function public.mark_document_embedding_stale()
returns trigger
language plpgsql
as $$
begin
  if old.summary is distinct from new.summary
     or old.body is distinct from new.body then
    new.needs_embedding_update = true;
  end if;

  return new;
end;
$$;

create trigger mark_document_embedding_stale
before update of summary, body on public.documents
for each row
execute function public.mark_document_embedding_stale();

My embedding job then does four things: finds rows where needs_embedding_update is true, combines the selected text columns, sends that text to the embedding model, and writes the result back while setting the flag to false. New rows are picked up automatically because the flag defaults to true.

Finally, add an index for cosine similarity searches:

create index documents_embedding_hnsw_idx
on public.documents
using hnsw (embedding vector_cosine_ops);

At that point, semantic search is just another Postgres query. The cosine distance operator is <=>, so the basic shape looks like this:

select id, summary
from public.documents
where embedding is not null
order by embedding <=> :query_embedding
limit 10;

I backfilled the existing rows, compared the results with Weaviate, switched the application over, and then removed the extra database. I still had the same semantic search, but now the source data and its embeddings lived in one place.

One database, one backup strategy, and one less thing to maintain. Nice and boring.