Choosing vector stores — Pinecone, Weaviate, Chroma, pgvector

Same job, different contracts

All serious options do vector insert + ANN query + metadata. You choose on ops, hybrid search, cost, team skills, and exit plan — not marketing “AI native” labels.

Read the flagship Vector databases — what, why, how first so HNSW / IVF / filters are not mysterious vendor jargon. This page is the buying and architecture companion: which box do you run, and why?

flowchart TD
  Need[Need semantic retrieval?] --> Scale{Scale / team / SoT}
  Scale -->|Laptop / class lab| Chroma[Chroma / Qdrant local]
  Scale -->|Already on Postgres| PG[pgvector]
  Scale -->|Managed SaaS, minimal ops| Pine[Pinecone / hosted Weaviate / Qdrant Cloud]
  Scale -->|Schema + hybrid first-class| Weav[Weaviate / Elasticsearch / OpenSearch]
  Scale -->|Self-host filters + Docker| Qdr[Qdrant / Weaviate self-host]

One-sentence decision rule

Pick the store that minimizes ops risk + data-model friction for your team’s next 12 months, while keeping an export/rebuild path so the vendor is not your source of truth.

What “choosing” actually decides

If two stores both clear your must-haves, pick the one your team can debug at 2am.

Architecture: what every store shares

Before brand comparison, fix the shared architecture. Every production RAG path looks like this — only the middle box changes.

flowchart LR
  subgraph WritePath[Write path]
    Docs[Docs / tickets / PDFs] --> Chunk[Chunker]
    Chunk --> EmbW[Embed worker]
    EmbW --> Upsert[Upsert vectors + payload]
  end
  subgraph Store[Vector store]
    Idx[(ANN index)]
    Meta[(Payload / metadata)]
    Upsert --> Idx
    Upsert --> Meta
  end
  subgraph ReadPath[Read path]
    Q[User query] --> Auth[Auth + tenant bind]
    Auth --> EmbQ[Embed query]
    EmbQ --> ANN[Filtered ANN]
    Idx --> ANN
    Meta --> ANN
    ANN --> Pack[Top-k + hydrate]
    Pack --> LLM[LLM]
  end

Shared contracts you must keep portable

1. Stable ids — chunk_id / point_id that survive re-embeds
2. Payload schema — tenant_id, ACL fields, source_uri, doc_id, timestamps
3. Model stamp — embedding_model_id + dimension (never mix silently)
4. Delete semantics — hard delete vs tombstone; GDPR “forget this user” runbook
5. Eval set — frozen queries with expected doc ids, independent of vendor

flowchart TB
  SoT[(System of record<br/>Postgres / object store)] -->|text + metadata| Rebuild[Rebuild job]
  Rebuild -->|same ids + model| AnyStore[(Any vector store)]
  App[App] -->|reads SoT for citations| SoT
  App -->|ANN only| AnyStore

Ship rule: the vector store holds a derived index. Canonical chunk text lives in your SoT. If the vendor disappears Monday, you rebuild by Friday.

How it works: the decision loop (not the brand pitch)

Teams that regret their choice usually skipped this loop:

flowchart TD
  A[Inventory SoT + skills] --> B[List must-haves]
  B --> C[Shortlist 2 stores]
  C --> D[Spike: ingest 10k + filtered query]
  D --> E[Measure p95 + recall@10]
  E --> F{Must-haves pass?}
  F -->|No| C
  F -->|Yes| G[Cost model 12 months]
  G --> H[Write exit/rebuild script]
  H --> I[Design review + ship]

Must-haves vs nice-to-haves (write these down)

A store that wins on nice-to-haves and fails a must-have is a no.

Comparison (engineering view)

Decision checklist (use in design docs)

1. Where does source-of-truth relational data live? If Postgres — try pgvector before a second datastore.
2. Do you need hybrid BM25+vector in one engine? Prefer Weaviate / Elastic / OpenSearch; or fuse in app with RRF (hybrid article).
3. SLA and staffing? Managed Pinecone / Weaviate Cloud / Qdrant Cloud vs self-host.
4. Tenant isolation? Separate indexes/collections vs shared + mandatory metadata filters + tests.
5. Filter selectivity? Sparse tenants + strict ACL → verify filter-aware ANN, not post-filter only.
6. Write path? Burst re-embeds, CDC from docs DB, GDPR deletes — who owns the runbook?
7. Exit plan? Store raw chunks + embedding_model_id + stable ids so you can rebuild elsewhere.
8. Budget shape? Always-on managed QPS vs spiky student/demo traffic.
9. Latency geography? Multi-region users → where does ANN live relative to embed + LLM?
10. Compliance? Data residency, encryption at rest, audit logs — vendor docs vs your Postgres already approved.

flowchart LR
  subgraph App
    Ingest[Ingest worker]
    Query[RAG query path]
  end
  subgraph Choice
    PG[(Postgres+pgvector)]
    Dedicated[(Pinecone/Qdrant/Weaviate)]
  end
  Ingest --> PG
  Ingest --> Dedicated
  Query --> PG
  Query --> Dedicated
  PG -.->|joins ACLs docs| Rel[(users/docs)]

Architecture deep dive by store

Chroma — architecture

Chroma is a developer-first embedding database: collections of embeddings + documents + metadata, with a Python/JS client that feels like a notebook library more than a clustered database.

flowchart TB
  Client[Chroma client / SDK] --> API[Local server or embedded]
  API --> Coll[(Collections)]
  Coll --> Emb[Embedding vectors]
  Coll --> Doc[Document text]
  Coll --> MD[Metadata dicts]
  API --> ANN[ANN backend]

How it works in practice

1. Create a collection (optionally with a default embedding function — prefer BYO embeddings in products).
2. upsert ids, documents, metadatas, embeddings.
3. query with a query embedding + where metadata filters.
4. Get distances + documents back for packing into an LLM prompt.

Tradeoffs

Tools

• chromadb Python package, Docker image for local server
• LangChain / LlamaIndex Chroma integrations (wrap behind your own protocol)
• Simple persistence directory for demos — know where files live before you treat it as durable

pgvector — architecture

pgvector is a Postgres extension: vector type + distance operators + IVFFlat / HNSW index access methods. The “vector store” is your OLTP database.

flowchart TB
  App[App] --> PG[(Postgres)]
  PG --> Tables[docs / chunks / acls]
  PG --> Ext[pgvector extension]
  Ext --> Col[embedding vector N]
  Ext --> Idx[HNSW / IVFFlat]
  Tables --> SQL[SQL WHERE + JOIN]
  Idx --> SQL
  SQL --> Hits[Ordered rows]

How it works in practice

1. CREATE EXTENSION vector; add embedding vector(dim) columns.
2. Build USING hnsw or ivfflat indexes with the right ops class (vector_cosine_ops, etc.).
3. Query with ORDER BY embedding <=> $1 LIMIT k plus normal WHERE tenant_id = ….
4. Joins hydrate titles/URLs/ACLs without a second network hop.

Tradeoffs

Full page: Postgres and pgvector.

When pgvector wins interviews: “We already had Postgres, ACLs in SQL, and <5M chunks — adding a second ANN SaaS was premature.”

Tools

• pgvector extension; managed on RDS/Aurora/Cloud SQL where supported
• psql, EXPLAIN (ANALYZE, BUFFERS) for query plans
• Migration tools (Alembic, Flyway) to version schema + index params
• Connection poolers (PgBouncer) — ANN queries need pool sizing thought

Pinecone — architecture

Pinecone is a managed vector database SaaS: you create indexes (and often namespaces), upsert vectors with metadata, and query over HTTPS. You buy ops time, not a Docker Compose file.

flowchart LR
  App[Your app] -->|HTTPS API| Pine[Pinecone control + data plane]
  Pine --> Idx[(Distributed ANN index)]
  Pine --> Meta[Metadata store]
  App -->|SoT still yours| SoT[(Postgres / S3)]

How it works in practice

1. Create an index with dimension + metric matching your embedding model.
2. Upsert (id, values, metadata) in batches; use namespaces for tenancy/versioning if that fits.
3. Query with vector + metadata filter; receive ids, scores, metadata.
4. Hydrate full text from your SoT if you did not store large blobs in metadata.

Tradeoffs

Tools

• Official Python/Node SDKs; REST API
• Console for index health and usage
• Your own export_payloads.py — do not rely on console clicks for disaster recovery
• Terraform / IaC providers where available for index lifecycle

Weaviate — architecture

Weaviate is a vector-native search engine with a schema (classes/properties), optional modules (vectorizers, Q&A), and strong hybrid (BM25 + vector) story. Cloud or self-host.

flowchart TB
  Client[GraphQL / REST client] --> WV[Weaviate node s]
  WV --> Schema[Class schema]
  WV --> Inv[Inverted index BM25]
  WV --> Vec[(Vector index)]
  Schema --> Objects[Objects + properties]
  Objects --> Inv
  Objects --> Vec
  Client --> Hybrid[hybrid search]
  Inv --> Hybrid
  Vec --> Hybrid

How it works in practice

1. Define classes and properties (types, tokenization, vectorization config).
2. Prefer bring-your-own embeddings in production so model choice is yours.
3. Query with nearVector / hybrid / filters; GraphQL is common, REST also exists.
4. Modules are optional power — do not couple your product to a module you cannot replace.

Tradeoffs

Tools

• Weaviate Cloud / open-source Docker images
• GraphQL playground; client libraries (Python, JS, Go)
• Backup modules / snapshots for self-host
• Schema migration discipline (treat like DB migrations)

Qdrant — architecture

Qdrant is a vector similarity search engine optimized for payload-filtered HNSW, with REST and gRPC, excellent Docker DX, and a clear points/collections model.

flowchart TB
  Client[REST / gRPC] --> QD[Qdrant]
  QD --> Coll[(Collections)]
  Coll --> Points[Points: id + vector + payload]
  Coll --> HNSW[HNSW segments]
  Coll --> PayIdx[Payload indexes]
  Client --> Search[Search + filter]
  HNSW --> Search
  PayIdx --> Search

How it works in practice

1. Create a collection with vector size + distance.
2. Upsert points with JSON payloads; index fields you filter on.
3. Search with vector + filter conditions; Qdrant aims for filter-aware ANN.
4. Snapshots / backups for self-host; or use Qdrant Cloud.

Tradeoffs

Tools

• Official Docker image; Qdrant Cloud
• Python qdrant-client, gRPC for throughput
• Web UI for collections in local/dev
• Snapshot APIs for backup drills

Elastic / OpenSearch — architecture

These are search platforms that grew dense-vector / kNN support. If the company already runs them for logs or product search, adding vectors can beat introducing Pinecone.

flowchart TB
  App --> ES[(Elastic / OpenSearch)]
  ES --> Inv[Inverted index BM25]
  ES --> kNN[kNN / HNSW fields]
  ES --> Analyzers[Analyzers / synonyms]
  App --> Hybrid[BM25 + kNN fusion]
  Inv --> Hybrid
  kNN --> Hybrid

How it works in practice

1. Map a dense_vector (or equivalent) field with dims + similarity.
2. Index documents with both text fields and vectors.
3. Query with knn + bool filters; fuse with BM25 when IDs/SKUs matter.
4. Respect JVM heap, shard counts, and circuit breakers — this is still a search cluster.

Tradeoffs

Tools

• Elasticsearch / OpenSearch clusters; Kibana / Dashboards
• Language clients; Search templates
• Snapshot/restore to S3-compatible storage
• Ranking eval notebooks against your frozen set

FAISS / local libraries — architecture

FAISS (and similar) are libraries, not product databases. They shine for offline eval, batch nearest neighbor, and research.

flowchart LR
  Vectors[Numpy / memmap vectors] --> FAISS[FAISS index]
  FAISS --> IDs[Neighbor ids]
  IDs --> Join[Join to metadata in your DB]

How it works: build an index in-process or on disk; search returns integer ids; you own persistence, filters, HA, and multi-tenant safety.

Tradeoffs: maximum control and speed for batch; almost never the online multi-tenant store alone.

Tools: faiss-cpu / faiss-gpu, Annoy, ScaNN — pair with Postgres for metadata.

Deep enough on each option (product lens)

Chroma

• Use: notebooks, RAG-style labs, internal prototypes, teaching the mental model.
• Avoid as sole prod store until you have a clear HA/backup story — or treat it as a client over a stronger backend if that is where the project is headed.
• Great for teaching collection / upsert / query quickly.
• Portfolio tip: fine for demos; write the abstract VectorStore protocol so you can swap later without rewriting ingest.

pgvector

• Keep vectors next to docs, acls, tenants.
• One backup story, one auth story, SQL you already know.
• Graduate when ANN QPS, index RAM, or multi-region ANN dominates Postgres primary health.
• Full page: Postgres and pgvector.

Pinecone

• Buy ops time: indexes, replicas, basic scaling.
• Model your metadata filters and namespaces/tenancy early.
• Watch unit economics at high QPS + large metadata; export chunks regularly so you are not trapped.
• Good default when the team is AI-product-heavy and infra-light.
• Ask vendors (and yourself): filter-aware search behavior under sparse ACL selectivity.

Weaviate

• Schema-first classes/properties; strong hybrid story.
• Modules for vectorizers (or BYO embeddings — usually better to control the model yourself).
• Self-host only if you want the ops; otherwise cloud.
• Fits teams that think in “search schema” rather than “SQL tables.”

Qdrant

• Excellent payload filtering + HNSW; pleasant Docker/Compose path for portfolios.
• Clear REST/gRPC; easy to wrap behind a VectorStore protocol in Python.
• Still: backups, upgrades, and capacity planning are yours if self-hosted.
• Strong choice when filters are the product (multi-tenant SaaS RAG).

Elastic / OpenSearch

• If the company already runs them for logs/search, adding dense vectors can beat introducing Pinecone.
• BM25 expertise transfers; dense vector scoring and kNN settings still need their own evals.
• JVM heap, shard strategy, and cluster ops are real — do not treat kNN as a checkbox.

FAISS / local libraries

• Excellent for batch similarity, offline eval, and research.
• Pair with a real DB for online serving unless you invent sharding, filters, and HA yourself (usually a bad idea for a product team).

Tenancy patterns

Ship rule: isolation bugs are security bugs. Add an automated test that tenant A’s query never returns tenant B payloads.

flowchart TD
  Q[Query + auth context] --> Bind[Bind tenant_id / roles in server]
  Bind --> F[Mandatory filter in store client]
  F --> ANN[ANN]
  ANN --> Assert[Assert all hits match tenant]
  Assert --> Pack[Pack for LLM]

Never trust the client to pass tenant_id alone — bind it from the session on the server.

Tenancy decision tree

flowchart TD
  T{Tenant count + size skew?}
  T -->|Few large enterprise| Sep[Index / collection per tenant]
  T -->|Many small similar| Shared[Shared + mandatory filter + CI]
  T -->|Whale + long tail| Hybrid[Isolate whales; share long tail]
  Shared --> Test[Isolation tests in CI]
  Sep --> Cost[Watch empty-index cost]
  Hybrid --> Complexity[Two code paths — document them]

Cost and lock-in without drama

• Persist canonical text + metadata in your own object store / Postgres — the vector DB holds a derived index.
• Version collections: docs_v3_modelX_dim.
• Script export_payloads.py / rebuild_index.py on day one of production.
• Track $/1k queries and $/1M vectors / month in the same dashboard as LLM spend.

class VectorStore(Protocol):
    def upsert(self, points: list[Point]) -> None: ...
    def search(self, vector: list[float], k: int, filters: dict) -> list[Hit]: ...
    def delete(self, ids: list[str]) -> None: ...

# Swap Chroma → Qdrant → Pinecone behind the protocol; keep ingest identical

Cost shape questions for design review

Rough TCO mental model

flowchart LR
  subgraph Managed
    M1[Index hours]
    M2[Storage]
    M3[Ops eng ≈ low]
  end
  subgraph SelfHost
    S1[VMs / k8s]
    S2[On-call eng]
    S3[Backup drills]
  end
  subgraph Shared
    R[Re-embed compute]
    E[Embed API $]
  end

Managed is not “more expensive” in the abstract — it is expensive when idle capacity dominates or when storage of fat metadata balloons. Self-host is expensive when on-call dominates.

Migration playbook (store A → store B)

1. Keep SoT in Postgres / object store (already)
2. Stand up B; backfill from SoT with same ids + model
3. Shadow traffic: compare hit ids / scores on eval queries
4. Flip read path behind a flag
5. Keep A read-only for rollback window
6. Decommission A after metrics + incident silence

Ship rule: never “migrate by changing the SDK in place” without a dual-read period.

sequenceDiagram
  participant SoT
  participant A as Store A
  participant B as Store B
  participant App
  SoT->>B: Backfill same ids
  App->>A: Production reads
  App->>B: Shadow reads compare
  Note over App: Flip flag
  App->>B: Production reads
  App->>A: Rollback window only

Dual-write vs rebuild

Prefer rebuild when you can afford a maintenance window; prefer outbox when deletes/updates must stay consistent continuously.

Worked example: 10-tenant SaaS RAG MVP

Constraints: 10 tenants, ~200k chunks total, team knows Postgres, need ACL filters, one engineer ops budget.

Reasonable pick: pgvector on existing Postgres — filters + joins, one backup. Add Redis exact cache in front. Revisit Qdrant/Pinecone when ANN p95 or index size stresses the primary.

Alternative: Qdrant Docker in staging with Postgres as SoT for docs/ACL; dual-write embeddings — if you expect to outgrow pgvector in months.

Five-bullet rationale (paste into design doc):

1. SoT already Postgres → avoid second consistency story
2. ACL joins are SQL we already audit
3. 200k vectors is well inside HNSW comfort on a sized box
4. Protocol wrapper keeps exit to Qdrant/Pinecone open
5. Re-eval when p95 ANN or vacuum/bloat threatens OLTP SLOs

Worked counterexample: 50M chunks, 5 regions

pgvector-on-primary loses. Prefer managed ANN or self-hosted Qdrant/Weaviate near embedders, Postgres remains SoT, CDC/dual-write for embeddings, regional read replicas or regional collections for latency.

flowchart TB
  subgraph Regions
    R1[Region A ANN]
    R2[Region B ANN]
    R3[Region C ANN]
  end
  SoT[(Global SoT Postgres / object store)] --> Workers[Embed workers]
  Workers --> R1
  Workers --> R2
  Workers --> R3
  UsersA[Users A] --> R1
  UsersB[Users B] --> R2

Worked example: university lab / portfolio

Constraints: one developer, demo traffic, need something on a resume that is not vapor.

Pick: Qdrant Compose + Postgres SoT + Redis cache, or pgvector if the whole app is already SQL. Chroma is fine for the first notebook week — graduate before the public demo.

Worked example: company already on Elastic

Constraints: SRE team lives in Elastic; BM25 product search exists; leadership skeptical of “another SaaS.”

Pick: add dense vectors / knn in Elastic or OpenSearch; keep Postgres for transactional ACL hydrate if needed. Prove recall with the same eval harness you would use for Pinecone.

Anti-patterns

• Picking Pinecone for a 5k-vector homework project
• Putting pgvector on an undersized Postgres already on fire
• Switching embedding models without a reindex budget
• Choosing purely on “highest GitHub stars”
• No export path for payloads / chunk text
• Hybrid “later” when your corpus is full of SKUs and ticket IDs
• One shared collection with optional tenant_id filter “for now”
• Letting the frontend pass raw filter JSON to the store
• Storing only vectors in the vendor (no SoT text) — citations die on key rotation
• Benchmarking without filters (prod always has filters)
• Comparing vendors on unfiltered recall only

Interface contract (staff bar)

Three measurable metrics

1. p95 search latency under realistic filters
2. recall@10 on frozen eval set
3. Cross-tenant isolation test = 0 leaks

Two degrade modes

1. Dedicated store down → serve BM25 from Elastic/Postgres tsvector or cached FAQ
2. Embed API down → refuse semantic path; exact cache only

flowchart TD
  Fail{Failure?}
  Fail -->|ANN store down| BM25[Keyword / FAQ fallback]
  Fail -->|Embed API down| Cache[Exact cache only]
  Fail -->|Both| Soft[Soft error + support path]
  BM25 --> User[User still gets something]
  Cache --> User

Threat note

Attacker or buggy client omits tenant filter → another tenant’s chunks enter the LLM prompt. Catch at server-bound filters + assert-on-hits + eval gate, not at “please remember to filter” comments.

Production readiness checklist

• Abstract VectorStore protocol in app code
• SoT for text outside the vector vendor
• Tenancy tests in CI
• Backup/restore or vendor RPO/RTO understood
• Cost model spreadsheet attached to design doc
• Reindex / model-upgrade runbook
• Hybrid plan if corpus is ID-heavy
• Filter selectivity tested on sparse tenants
• Delete / GDPR path rehearsed once
• Shadow-read plan if you might migrate in 12 months

Interview prompts

1. When would you pick pgvector over Pinecone?
2. How do you avoid vendor lock-in for embeddings?
3. Shared index vs per-tenant collections — tradeoffs?
4. What breaks first when ANN and OLTP share one Postgres primary?
5. How do you validate filter-aware search under 0.1% selectivity?
6. Walk through a store A → store B migration without downtime.
7. What belongs in the vector payload vs the relational SoT?

Strong answer shapes

Feature matrix (deeper)

Scoring rubric for a design review

Score 1–5 each; pick the highest total that clears “must haves”:

1. Fits existing SoT (Postgres?)
2. Hybrid needs covered
3. Ops staffing match
4. Filter/ACL confidence
5. Cost at 12-month projected QPS
6. Exit/rebuild pain
7. Latency geography
8. Compliance / residency fit

Must-haves that fail → eliminate the option even if the total is high.

Example scored shortlist

Numbers are illustrative — fill with your constraints. The value is forcing the debate onto a grid.

Local → staging → prod path

flowchart LR
  Local[Chroma or Qdrant Compose] --> Staging[Same API as prod]
  Staging --> Prod[Managed or HA self-host]

Ship rule: do not prototype on an API shape you will throw away. Prefer Qdrant/pgvector local if prod will be those; use Chroma only when the lesson is the mental model.

Environment parity checklist

Dim mismatch between local and prod is a classic silent footgun.

Vendor questions to ask (or self-ask)

• Exact behavior of metadata filters under 0.1% selectivity?
• Backup / PITR / point-in-time restore of an index?
• Dimension change story?
• Sparse / hybrid vectors roadmap?
• Data residency regions?
• Export API for all payloads + vectors?
• Max metadata size per vector and billing impact?
• Hot/cold tiering or only full-RAM indexes?
• SLO credits and incident communication?
• How deletes propagate to ANN segments (visibility lag)?

Tools toolkit (what you actually install)

flowchart TB
  Dev[Dev laptop] --> Compose[Compose vector store]
  CI[CI] --> Tests[Isolation + recall tests]
  Staging[Staging] --> SameAPI[Prod-shaped API]
  Prod[Prod] --> Metrics[p95 / errors / cost]
  SoT[(SoT)] --> Rebuild[rebuild_index job]
  Rebuild --> Prod

Minimal VectorStore spike (shape only)

# Illustrative — wire real SDKs behind this in your repo
from typing import Protocol

class Hit(dict):
    pass

class VectorStore(Protocol):
    def upsert(self, points: list[dict]) -> None: ...
    def search(self, vector: list[float], k: int, filters: dict) -> list[Hit]: ...
    def delete(self, ids: list[str]) -> None: ...

def assert_tenant(hits: list[Hit], tenant_id: str) -> None:
    bad = [h for h in hits if h.get("tenant_id") != tenant_id]
    if bad:
        raise RuntimeError("cross-tenant leak")

The protocol is the product; the SDK is an adapter.

Portfolio / homework guidance

Interviewers care more that you can explain filters, ids, evals, exit than that you used the trendiest managed service.

What to put on a resume bullet

Weak: “Used Pinecone for RAG.”
Strong: “Designed multi-tenant retrieval with server-bound filters, frozen recall@10 evals, and a rebuild-from-Postgres exit path across Qdrant and pgvector.”

Failure story bank (mention these)

• Chose Pinecone, stored only vectors, lost chunk text when rotating keys — could not cite
• Shared index, forgot filter in one code path — cross-tenant leak in staging
• IVFFlat default probes after bulk load — recall collapsed silently
• Migrated embed model in place — mixed dimensions; queries returned noise
• Benchmarked without filters — prod p95 5× worse under ACL predicates
• Prototyped on Chroma API, rewrote everything for prod Pinecone in a crunch
• Per-tenant indexes for 2,000 free-tier tenants — ops and cost exploded
• Deleted from SoT but forgot vector delete — stale chunks kept answering

Glossary

Decision tree (printable)

Already Postgres + <~5M vectors + team knows SQL?
  YES → pgvector (revisit when OLTP SLOs hurt)
  NO ↓
Need managed ops and budget OK?
  YES → Pinecone / Qdrant Cloud / Weaviate Cloud
  NO ↓
Need strong payload filters self-hosted?
  YES → Qdrant
  NO ↓
Already Elastic/OpenSearch?
  YES → kNN + BM25 there
  NO → Qdrant or Weaviate self-host; wrap VectorStore protocol

Teaching/lab only → Chroma OK; still keep an exit protocol.

flowchart TD
  Start[Start] --> PG{Postgres SoT + SQL team + modest scale?}
  PG -->|Yes| PGv[pgvector]
  PG -->|No| Man{Need managed ops + budget?}
  Man -->|Yes| Cloud[Pinecone / Qdrant Cloud / Weaviate Cloud]
  Man -->|No| Filt{Payload filters self-host?}
  Filt -->|Yes| Qdrant
  Filt -->|No| El{Already Elastic/OS?}
  El -->|Yes| kNN[kNN + BM25 there]
  El -->|No| Self[Qdrant or Weaviate self-host]

TCO sketch fields

Pick with a 12-month spreadsheet, not a blog post title.

Sample spreadsheet rows

1. Vector count now / at 12 months
2. Avg metadata bytes × vectors × replicas
3. Query QPS p50 / p95 / peak
4. Re-embed jobs per year × embed $
5. Eng hours for on-call / upgrades
6. Cost of a 4-hour outage (business)

If row 6 dwarfs row managed fees, managed often wins.

Reference architectures (copy into Notion)

A — Postgres-centric MVP

Postgres (docs, ACL, pgvector) → Redis exact cache → LLM. Hybrid via tsvector + RRF in app when IDs matter.

flowchart LR
  User --> API[API]
  API --> Redis[(Redis)]
  Redis -->|miss| PG[(Postgres + pgvector)]
  PG --> API
  API --> LLM

B — Split ANN

Postgres SoT → async embed worker → Qdrant/Pinecone → Redis → LLM. CDC or outbox for deletes.

flowchart LR
  Docs[(Postgres SoT)] --> Outbox[Outbox / CDC]
  Outbox --> Worker[Embed worker]
  Worker --> ANN[(Qdrant / Pinecone)]
  API[API] --> ANN
  API --> Docs
  API --> LLM

C — Search-platform-centric

OpenSearch/Elastic for BM25+kNN → Postgres for ACL joins on hydrate → LLM.

flowchart LR
  API --> ES[(Elastic / OpenSearch)]
  ES -->|ids| API
  API --> PG[(Postgres ACL hydrate)]
  API --> LLM

Draw your traffic arrows and failure domains before debating brands.

D — Lab / teaching

Chroma embedded or Qdrant single container; SQLite/Postgres optional; emphasize protocol + evals over HA.

FAQ (choosing stores)

Is managed always more expensive?
Not if eng time is scarce. It is if traffic is spiky and you pay for idle capacity — model the curve.

Can I use Chroma in production?
Only with eyes open on HA, backup, and tenancy. Many teams prototype in Chroma and productionize on Qdrant/pgvector/Pinecone behind the same protocol.

When is Elastic the obvious answer?
When the company already runs it well and needs BM25+vector with existing analyzers and ops.

Should I use the vendor’s built-in embedding module?
Usually no for products — BYO model keeps quality, cost, and exit under your control. Modules are fine for demos.

How many vectors until pgvector is “wrong”?
No universal number. Watch OLTP p95, autovacuum, index RAM, and ANN QPS. Many teams are fine into low millions of chunks on a sized primary; others split earlier because of ops politics, not math.

Do I need hybrid on day one?
If users search SKUs, error codes, emails, or ticket IDs — yes, or you will look broken. See hybrid + rerank.

Namespace per tenant or metadata filter?
Namespaces help isolation and drop-tenant; filters scale to many small tenants. Combine with tests either way.

Deep dive: lock-in is about data shape, not SDKs

SDKs are easy to wrap. Painful lock-in looks like:

• Chunk text only inside the vendor
• Proprietary sparse vector formats you cannot export
• Tenancy only via a vendor namespace feature with no portable equivalent
• Filters expressed in a DSL that does not map to SQL
• Dimension or metric choices that force a full rewrite of eval baselines

Countermeasures: SoT ownership, portable metadata schema, protocol wrapper, periodic restore drill into a second engine in staging.

flowchart TB
  Bad[Bad lock-in] --> T1[Text only in vendor]
  Bad --> T2[Proprietary sparse only]
  Bad --> T3[Tenant only via vendor namespace]
  Good[Good posture] --> S1[SoT owns text]
  Good --> S2[Portable payload schema]
  Good --> S3[Protocol + rebuild job]
  Good --> S4[Quarterly restore drill]

Deep dive: filter selectivity and why demos lie

Unfiltered ANN is the happy path vendors demo. Production queries look like:

tenant = X AND role IN (…) AND doc_type = policy AND updated_at > …

If the filter matches 0.1% of vectors, engines that post-filter candidates can return < k hits or scan huge candidate sets. Engines with filter-aware indexing behave better — but you must measure on your skew.

Deep dive: write path and deletes

Choosing a store is also choosing a write machine.

sequenceDiagram
  participant User
  participant API
  participant SoT
  participant Queue
  participant Emb as Embed worker
  participant VS as Vector store
  User->>API: Upload doc
  API->>SoT: Insert doc + chunks text
  API->>Queue: Enqueue embed
  Queue->>Emb: Job
  Emb->>VS: Upsert vectors
  User->>API: Delete doc
  API->>SoT: Soft/hard delete
  API->>Queue: Enqueue delete vectors
  Queue->>VS: Delete by ids

Ship rules

• Upserts are idempotent on stable ids
• Deletes are scheduled and retried — never “fire and forget” only in the request thread without a durable queue for prod
• Re-embeds create a new collection or stamp embedding_model_id — do not mix dims

Deep dive: latency budget

ANN is one hop in a larger budget:

Optimizing only ANN while ignoring embed + hydrate is common theater. Cache exact query hashes in Redis when repeats are common.

How-it-works: side-by-side query paths

flowchart TB
  subgraph pgvectorPath[pgvector]
    Q1[SQL] --> H1[HNSW / IVF]
    Q1 --> J1[JOIN docs ACL]
  end
  subgraph qdrantPath[Qdrant]
    Q2[gRPC search + filter] --> H2[HNSW + payload idx]
    H2 --> H3[Hydrate text from Postgres]
  end
  subgraph pinePath[Pinecone]
    Q3[HTTPS query + metadata filter] --> H4[Managed ANN]
    H4 --> H5[Hydrate from SoT]
  end

Same user question; different failure domains. pgvector fails with the DB. Pinecone fails as a third-party dependency. Qdrant self-host fails as your cluster. Design degrade modes for the one you pick.

Tradeoffs summary poster

Micro-project

Pick a store for a 10-tenant SaaS RAG MVP. Write five bullets of rationale and one paragraph on the exit plan (how you would rebuild on another store in a weekend).

Stretch goals

1. Draw architecture A or B with failure domains labeled.
2. Implement a fake VectorStore with an in-memory backend and a Qdrant adapter behind the same protocol.
3. Write a CI test that fails if any hit’s tenant_id mismatches the session.
4. Fill the scoring rubric for your side project and paste it into the README.

Related

Flagship: Vector databases. Guided RAG; Key Tech Chroma. Next: Postgres and pgvector, chunking, and hybrid + rerank.