pgvector in practice: semantic search without a separate vector database

The HostBase team

A separate vector database is one more service to pay for, synchronize and secure. For most apps pgvector in Postgres is fast enough — and the vectors sit next to the data they describe, under the same RLS policies.

Schema

create extension if not exists vector;

create table articles (
  id bigint generated always as identity primary key,
  title text not null,
  body text not null,
  embedding vector(1024)
);

create index articles_embedding_idx
  on articles using hnsw (embedding vector_cosine_ops)
  with (m = 16, ef_construction = 64);

Search

create or replace function search_articles(q vector(1024), k int default 5)
returns table (id bigint, title text, score float)
language sql stable as $$
  select id, title, 1 - (embedding <=> q) as score
  from articles
  order by embedding <=> q
  limit k;
$$;

const { data } = await supabase.rpc("search_articles", {
  q: await embed("how do I return an item?"),
  k: 5,
});

Hybrid search

The best results come from combining full-text and vector search. Postgres has both — you only need to merge the rankings, for example with Reciprocal Rank Fusion.

alter table articles add column fts tsvector
  generated always as (to_tsvector('simple', title || ' ' || body)) stored;
create index on articles using gin (fts);

Performance tips

Build the HNSW index after loading the data — it is much faster.
Increase maintenance_work_mem while the index is built.
The hnsw.ef_search parameter (default 40) trades accuracy for speed.
Smaller dimensions (512–1024) often give similar quality at half the memory.