pgvector for Embeddings, RAG, and Semantic Search

# PGDG ships pgvector as a versioned package:
sudo apt install -y postgresql-17-pgvector

# Enable per-database:
sudo -u postgres psql -d appdb -c "CREATE EXTENSION vector;"

# Verify:
sudo -u postgres psql -d appdb -c "\dx vector"

Are inserts continuous (RAG with growing corpus)?
  YES → HNSW. Updates are cheap; recall is high.
  NO  → Static dataset, build once?
        YES + < 1M rows → either works; HNSW is simpler.
        YES + > 1M rows → IVFFlat builds faster and uses less RAM.

Need highest recall at low query latency? → HNSW.
Need smallest index footprint? → IVFFlat.

CREATE TABLE documents (
  id          bigserial PRIMARY KEY,
  tenant_id   uuid NOT NULL,
  body        text NOT NULL,
  embedding   vector(768) NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);

-- Build-time params:
--   m = 16 default (graph connectivity; raise to 32 for hard datasets)
--   ef_construction = 64 default (build effort; 200 gives much better recall)
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 200);

-- Query-time recall vs latency knob:
SET hnsw.ef_search = 100;   -- raise for higher recall, lower for speed

CREATE INDEX ON documents
  USING hnsw ((embedding::halfvec(768)) halfvec_cosine_ops)
  WITH (m = 16, ef_construction = 200);

-- Query against the index by casting the probe vector:
SELECT id, body
FROM documents
ORDER BY embedding::halfvec(768) <=> $1::halfvec(768)
LIMIT 10;

pip install sentence-transformers psycopg[binary]

from sentence_transformers import SentenceTransformer
import psycopg

model = SentenceTransformer("BAAI/bge-base-en-v1.5")
conn = psycopg.connect("dbname=appdb user=appuser host=127.0.0.1 port=6432")

docs = [("doc-1", "Patroni provides leader election for PostgreSQL HA"),
        ("doc-2", "pgBackRest performs incremental block-level backups")]

with conn.cursor() as cur:
    for tid, body in docs:
        emb = model.encode(body, normalize_embeddings=True).tolist()
        cur.execute(
          "INSERT INTO documents (tenant_id, body, embedding) VALUES (%s,%s,%s)",
          (tid, body, emb))
    conn.commit()

# Ollama on a separate RamNode instance:
curl http://10.0.0.20:11434/api/embeddings -d '{
  "model": "nomic-embed-text",
  "prompt": "Patroni provides leader election for PostgreSQL HA"
}'

WITH semantic AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY embedding <=> $2) AS rank
  FROM documents WHERE tenant_id = $1
  ORDER BY embedding <=> $2 LIMIT 50
),
lexical AS (
  SELECT id, ROW_NUMBER() OVER (
    ORDER BY ts_rank(to_tsvector('english', body), plainto_tsquery($3)) DESC
  ) AS rank
  FROM documents
  WHERE tenant_id = $1
    AND to_tsvector('english', body) @@ plainto_tsquery($3)
  LIMIT 50
)
SELECT d.id, d.body,
       COALESCE(1.0/(60+s.rank), 0) + COALESCE(1.0/(60+l.rank), 0) AS rrf_score
FROM documents d
LEFT JOIN semantic s USING (id)
LEFT JOIN lexical  l USING (id)
WHERE s.id IS NOT NULL OR l.id IS NOT NULL
ORDER BY rrf_score DESC LIMIT 10;

CREATE INDEX docs_tenant_a_hnsw ON documents
  USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 200)
  WHERE tenant_id = '11111111-1111-1111-1111-111111111111';