vector-db

---
name: vector-db
description: "Use when operating a vector store as a data layer — choosing or migrating between Pinecone, Qdrant, Weaviate and pgvector; designing a collection or index (distance metric, dimensions, HNSW parameters, named vectors); filtering on metadata; hybrid dense-plus-sparse search; and quantization to cut RAM and cost. Covers garbage results, silently ignored filters, low recall, slow queries, and filtered queries returning fewer than k rows. NOT producing, chunking or judging embeddings (that is `embeddings-search`)."
tags: [vector-database, pinecone, qdrant, weaviate, pgvector, hnsw, hybrid-search, metadata-filter, quantization]
recommends: [embeddings-search, rag, postgresdb, redis, supabase]
origin: risco
---

# 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.

| Engine | Best when | Hybrid built-in | Ops cost | Scale sweet spot |
|---|---|---|---|---|
| **pgvector** | Data already in Postgres; one less system to run | No — DIY (`vector` + `tsvector`, combine yourself) | You already run Postgres | ≤ a few M vectors |
| **Qdrant** | You want best filtered-search latency and self-host control | Yes — `query` API, `prefetch` + RRF/DBSF, server-side IDF | Self-host or cloud | 10M+ |
| **Weaviate** | You want hybrid + modules out of the box | Yes — `alpha` + `fusionType`, BlockMax WAND BM25 | Self-host or cloud | 10M+ |
| **Pinecone** | You refuse to operate anything | Yes — sparse-dense, integrated inference | Zero (serverless) | Any (pay per use) |

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:

   ```text
   <-> 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.

```text
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:

  ```sql
  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](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](references/engines.md).

## Quantization & cost

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

| Method | Compression | When safe |
|---|---|---|
| Scalar (int8) | ~4x | Almost always; tiny recall loss. Good default RAM cut. |
| Product (PQ) | 8–64x | Large corpora where RAM dominates; needs tuning + recall check. |
| Binary | ~32x (~40x faster via SIMD popcount) | **High-dim only** (≥1024). On 384-dim it shreds recall — measure or don't. |
| pgvector `halfvec` | ~2x | Near-free: 16-bit float, near-identical recall, and required for >2000 dims. |

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

| Anti-pattern | Why it bites | Do instead |
|---|---|---|
| Cosine-trained model indexed with L2 (`<->`) | Silently wrong ranking, no error | Match metric to model — cosine → `<=>` / `vector_cosine_ops` |
| Post-filtering top-k results | Returns < k rows, sometimes 0, on selective filters | Filter inside the search; index the filter field |
| IVFFlat + selective filter, no iterative scan | Drops rows; deprecated path in pgvector 0.8 | HNSW + `hnsw.iterative_scan='relaxed_order'` |
| Never measuring recall | "Search is bad" with no number to move | Recall@k vs an exact baseline before shipping |
| Binary quantization on 384-dim | Recall collapses, then blamed on the engine | Binary only ≥1024 dims, after a recall test; else scalar/halfvec |
| One-by-one upserts | 10–100x slower, index thrash | Batch hundreds per request |
| `ef_construction < 2*m` | Permanently weak graph; recall needs a full rebuild | Keep `ef_construction >= 2*m` (≥32 at default `m=16`) |
| Store only raw text, re-embed to "update" | Drift, cost, no point-update path | Re-upsert the vector by id |
| Unindexed filter field | Full scan, latency spikes | Payload index (Qdrant) / B-tree (pgvector) / sane metadata cardinality (Pinecone) |

## References & siblings

- [references/engines.md](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](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>`.

• SKILL.md 11 KB
• evals/README.md 1 KB
• evals/cases.yaml 3 KB
• references/engines.md 5 KB
• references/tuning.md 3 KB
• scripts/verify.sh 5 KB