vector-db-retrieval
verified549abefd-5aa6-4d7a-a3f0-85c20700bcea
Pick and operate a vector database for RAG — indexing, chunking, hybrid search, and eval of retrieval quality.
Metadata
Skill file
# Choosing and Operating a Vector Database
Use when your app must search over embeddings — RAG retrieval, semantic search,
duplicate detection — and you need to pick a backend and tune it so results are
actually relevant.
## Decide the backend first
- **pgvector** (Postgres): best when you already run Postgres. One system for
relational + vector. Good recall at moderate scale; read index tradeoffs below.
- **sqlite-vec**: zero-ops, file-based, great for single-node/tooling use.
- **Dedicated engines** (Qdrant, Weaviate, Milvus, Pinecone): distributed,
filtering-rich, high QPS. Choose when you outgrow SQL-scale or need
multi-tenancy at scale.
The Skill Vault itself uses a pluggable vector backend (default sqlite-vec) so
the registry works single-node without external infra.
## Index math that governs recall
| parameter | effect |
|-----------|--------|
| `lists` (HNSW `M`) | higher = better recall, more memory/time |
| `ef_search` | higher = better recall per query, slower |
| dimension | 384 (bge-small) vs 1536 (OpenAI) vs 3072 — cost/speed |
| distance metric | cosine vs L2 vs inner-product (normalize first for cosine) |
Rule of thumb: recall isn't free — index size and latency grow with recall.
Measure with a labeled eval set (below), not vibes.
## Chunking is the top lever
- Chunk *semantically* (by heading/paragraph), not by fixed char count alone.
- Keep overlap small (5–10%) so sentence boundaries aren't split mid-thought.
- Chunk size should match what you'll *retrieve and stuff into a prompt* — if
the LLM window is small, smaller chunks; if you cite whole sections, bigger.
## Hybrid search beats pure vector
Pure embedding search misses exact tokens. Combine:
- keyword/BM25 recall + vector recall
- reciprocal rank fusion (RRF) to merge two ranked lists
- optional metadata filters (author, date, tenant)
## Pitfalls
- **Cosine vs raw dot-product**: don't push un-normalized vectors into cosine.
- **Reindexing drift**: keep the DB row and the vector index in sync (Skill Vault
handles this via `reindex_all()`).
- **Cold indexes**: brand-new small stores return poor results until seeded.
- Blindly trusting top-k: always return *score* so callers can threshold.
## Verify
- Build a 50–200 question eval set with known-relevant docs; measure recall@k.
- Compare hybrid vs pure-vector recall on it before shipping.
- `vacuum`/`ANALYZE` after bulk loads; re-run evals after any indexing change.