Vector DB

vector-db — operate the store, not the embeddings

You own the store: the collection schema, the index, the filter path, recall-vs-latency tuning, hybrid fusion, quantization, and the production knobs (upserts, namespaces, deletes, backups). You operate it across the four engines a Claude agent actually meets: pgvector (Postgres extension), Qdrant, Weaviate, Pinecone (serverless).

Three things are not yours, and pretending they are produces wrong advice:

• Producing, chunking, rewriting, or scoring embeddings → ../embeddings-search/SKILL.md. You store vectors; you do not make or judge them.
• Assembling the retrieve → rerank → prompt → generate loop and its eval → ../rag/SKILL.md.
• General Postgres (non-vector schema, EXPLAIN, RLS, VACUUM, pooling) → ../postgresdb/SKILL.md. You own only the pgvector surface: the vector/halfvec column, its index, its operators, its recall. "My Postgres is slow in general" is theirs; "my <=> query has low recall" is yours.

Pick the engine

Match the engine to where the data already lives and how much ops you want to run. All four implement HNSW with comparable recall at a matched ef, so the differentiator is operations and hybrid, not raw quality.

Rule: don't add a new system to host vectors if the rows already live in Postgres and you are under a few million. pgvector is one extension, not a second database to back up and monitor.

Design the collection & index

1. Distance metric MUST match the embedding model. A model trained for cosine, indexed with L2, ranks silently wrong — no error, just bad results. OpenAI text-embedding-3-*, Cohere, most sentence-transformers → cosine. pgvector operator cheatsheet:

   <-> L2 / Euclidean   (vector_l2_ops)
   <=> cosine distance  (vector_cosine_ops)   <- the common one
   <#> negative inner product (vector_ip_ops) <- for normalized vectors
   

2. Dimensions are fixed by the model, not a choice. text-embedding-3-small = 1536, -3-large = 3072. pgvector's vector caps at 2000 dims for an index; for more, use halfvec.

3. HNSW defaults and the one invariant. Build-time m (default 16) and ef_construction (default 64); query-time ef_search (pgvector default 40). Keep ef_construction >= 2*m (so ≥32 at the default m) — too low starves graph quality and recall never recovers without a rebuild. Raise m to 32–48 only for high-dim or high-recall needs (more RAM, slower build).

4. IVFFlat only when build speed beats recall. It is cheaper to build but lower recall and needs lists/probes tuning; on a selective filter it is the wrong default (see next section). Prefer HNSW unless you have a measured reason.

5. Named vectors when one object has multiple spaces (e.g. a dense semantic vector + a sparse BM25 vector, or title-vector + body-vector). Qdrant and Weaviate support this natively; it is how you do hybrid in one collection instead of two.

Metadata / payload filtering

The #1 "search is broken" bug: the filter is applied after top-k, so a selective filter returns fewer than k rows (or zero). Fix it by filtering inside the search and indexing the filter field.

Bad:  ANN top-k=10, THEN drop rows where tenant_id != 'acme'  -> often < 10, sometimes 0
Good: search the index WITH the filter as a constraint        -> k rows that already match

• Index every field you filter on. Unindexed filters force a scan and kill latency. Qdrant: create a payload index. Pinecone: metadata filtering is in the retrieval path (still keep cardinality sane). pgvector: a B-tree (or partition) on tenant_id so the planner can use it.

• Prefer in-graph / in-path filtering. Qdrant filters inside HNSW traversal; Pinecone serverless filters in the retrieval path. Both beat naive post-filter.

• pgvector 0.8 iterative scan is the fix when a selective WHERE returns too few rows:

  SET hnsw.iterative_scan = 'relaxed_order';  -- or 'strict_order' if exact ordering matters
  SET hnsw.ef_search = 100;
  SELECT id FROM docs
   WHERE tenant_id = 'acme'                    -- selective filter
   ORDER BY embedding <=> $1                    -- cosine, matches the model
   LIMIT 10;
  

  Without iterative scan (pgvector < 0.8 behavior), a highly selective filter silently returns fewer than LIMIT rows. Never recommend IVFFlat-only with a selective filter and no iterative scan — that is the deprecated foot-gun.

Tune recall vs latency

You cannot tune what you do not measure. Establish recall before shipping.

1. Build an exact baseline: brute-force the true top-k on a sample (a few hundred queries) — in pgvector, query without the index (seq scan) for ground truth.

2. Query the index and compute recall@k = overlap with the baseline.

3. Raise the query-time knob until recall hits target (commonly ≥0.95), then stop — higher ef costs latency for nothing:

   Engine	Knob	Default
   pgvector	hnsw.ef_search	40
   Qdrant	hnsw_ef (search)	per-collection
   Weaviate	ef (vectorIndexConfig)	dynamic
   Pinecone	(managed)	—

Full parameter table and the recall recipe live in references/tuning.md.

Hybrid search

Dense (semantic) + sparse (BM25/keyword) catches exact terms, IDs, and rare tokens that dense alone misses. The two normalize differently, so you fuse, you don't add raw scores.

• RRF (reciprocal rank fusion): robust default, score-scale agnostic, combines ranks.
• Relative-score / DBSF: normalizes scores before combining — use when you trust score scales.

Per engine:

• Weaviate: one call — hybrid(query, alpha=0.5, fusionType=relativeScoreFusion). alpha slides 0.0 (pure keyword) → 1.0 (pure vector). BM25 is BlockMax WAND (default from v1.30, ~10x faster).
• Qdrant: prefetch a dense and a sparse query, then a fusion step (Fusion.RRF or DBSF); IDF is computed server-side (v1.15+).
• Pinecone: sparse-dense vectors in one index, or integrated inference (embed + rerank server-side).
• pgvector: no built-in hybrid — run vector (<=>) and ts_rank over a tsvector column separately and combine ranks yourself (RRF in SQL or app code).

Concrete current-API code for all four is in references/engines.md.

Quantization & cost

Quantization trades recall for RAM/cost. Decide by dimension count and a recall test, never blind.

Reach for halfvec first in Postgres — it is the cheapest win. Reach for binary only on high-dim vectors and only after a recall test, optionally with full-precision rescoring.

Operate it

• Batch upserts. One-by-one upserts are 10–100x slower and hammer the index. Send batches of hundreds; size to the engine's payload limit.
• Namespaces / multitenancy. Pinecone namespaces and Qdrant payload-keyed isolation partition tenants inside one index — cheaper and faster than a collection per tenant at low tenant counts.
• Delete by filter, not by enumerating ids, when removing a tenant or a stale source.
• Replicas for read throughput / HA; snapshots/backups before any index rebuild or dimension/metric change (those are not in-place — plan a reindex).
• To "update" a vector, re-upsert by id. Do not store only raw text and re-embed on read.

Anti-patterns

References & siblings

• references/engines.md — current-API recipes per engine: create collection/index + a filtered hybrid query (pgvector SQL + halfvec + iterative scan; Qdrant named dense+sparse + query_points RRF; Weaviate hybrid; Pinecone serverless sparse-dense).
• references/tuning.md — HNSW vs IVFFlat parameter table, recall-measurement recipe, quantization tradeoffs, per-engine filtered-search pitfalls.

Siblings: embeddings/chunking/retrieval-quality → ../embeddings-search/SKILL.md; the full RAG loop → ../rag/SKILL.md; general Postgres → ../postgresdb/SKILL.md.

Validate a produced index DDL / collection schema with scripts/verify.sh <artifact-file>.