pgvector + HNSW

Vector search inside PostgreSQL. "Good enough" without a separate vector DB.

1. Install

docker run -d -e POSTGRES_PASSWORD=x -p 5432:5432 pgvector/pgvector:pg16

Or existing PG:

CREATE EXTENSION IF NOT EXISTS vector;

2. Schema

CREATE TABLE document_chunks (
  id BIGSERIAL PRIMARY KEY,
  document_id BIGINT NOT NULL,
  chunk_index INT NOT NULL,
  content TEXT NOT NULL,
  embedding vector(768),
  created_at TIMESTAMPTZ DEFAULT now()
);

3. HNSW vs IVFFlat

Index	Build	Query	Accuracy	For
HNSW	slow	fast	very high	read-heavy RAG
IVFFlat	fast	medium	tunes	bulk + periodic rebuild

4. Create HNSW

CREATE INDEX ON document_chunks
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

Build after the full load.

5. Query

SELECT id, content, 1 - (embedding <=> $1::vector) AS similarity
FROM document_chunks
ORDER BY embedding <=> $1::vector LIMIT 10;

Must sort ASC by distance for HNSW to engage.

6. Runtime tuning

SET hnsw.ef_search = 100;

7. Hybrid (trigram + vector)

SELECT *, similarity(content, $1) AS text_score,
       1 - (embedding <=> $2::vector) AS vec_score
FROM document_chunks
ORDER BY (similarity(content, $1) * 0.3 + (1 - (embedding <=> $2::vector)) * 0.7) DESC
LIMIT 10;

8. Backup

pg_dump stores vectors as text; rebuild indexes on restore (pg_restore --jobs 4).

9. Gotchas

Missing CREATE EXTENSION vector
ORDER BY similarity DESC → no HNSW
Dim mismatch after model swap
Seq Scan in EXPLAIN → query shape wrong

10. When a dedicated DB?

Scale	Pick
~10M	pgvector
10–100M	pgvector with tuning
100M+	Qdrant · Milvus · Vespa

Closing

Start with pgvector; migrate only after measured bottlenecks.

04-redis-five-roles