Step 3
pgvector + HNSW
25 min
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 ScaninEXPLAIN→ 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.
Next
- 04-redis-five-roles