Vector Database Selection

Vector database selection is the decision rule that picks the store: start with pgvector (L183) because your data already lives in Postgres (L115), and move to a specialist — Pinecone (L184) for managed scale or Qdrant (L185) for self-hosted filtering — only when measured triggers (corpus scale, filter load, ops availability, cost shape, L150) say so, with the retrieval layer behind an interface (L155) so the choice stays reversible.

The one-sentence interview answer: "The rule is Postgres first, specialist on triggers (L186). Default to pgvector (L183) — my data already lives in Postgres (L115), metadata filters are WHERE clauses (L180), one database to operate. I move when measured triggers fire: the corpus outgrows Postgres's ANN (L182), the filter load needs a specialist's structure, managed ops beat self-hosting (Pinecone, L184), or self-hosted control beats managed cost (Qdrant, L185). The retrieval layer sits behind an interface (L155) so the choice is a deployment decision, reversible — and the golden set (L195) re-measures after the move."

Think of the decision as choosing a kitchen for a restaurant you're opening. You already own a building with a kitchen (Postgres, L115) — it handles most cooking (pgvector, L183). You only build a second kitchen (specialist store, L184–185) when measured demand says the first can't keep up: the menu outgrew the ovens (corpus scale), the orders need special stations (filter load), or you want to hire a chef (managed ops, L184) instead of cooking yourself (L185). And you never weld the stoves in place — the kitchen can be reconfigured (the interface, L155).

   THE RULE (L186)
   ┌──────────────────────────────────────────────────────────┐
   │ 1 · DATA ALREADY IN POSTGRES? (L115, L183)               │
   │     yes → pgvector — the default, one database           │
   └──────────────────┬───────────────────────────────────────┘
                      ▼ measured triggers (L195, L332)
   ┌──────────────────────────────────────────────────────────┐
   │ 2 · SCALE?         corpus outgrew pgvector's ANN (L182)  │
   │ 3 · FILTER LOAD?   filtering needs a specialist (L180)   │
   │ 4 · OPS?           managed beats self-host (L184)        │
   │    or control beats managed cost (L150, L185)            │
   └──────────────────┬───────────────────────────────────────┘
                      ▼
   Pinecone (L184) · Qdrant (L185) — behind the interface (L155)

The mental model is the kitchen decision: use what you own until measured demand says otherwise — and keep every choice reconfigurable.

   choosing the vector store (L186)
        │
        ▼
   ┌──────────────────────────────────────────────────────────┐
   │ 1 · DATA LOCATION (L115)                                 │
   │     where does the data live? → Postgres? → pgvector    │
   └──────────────────┬───────────────────────────────────────┘
                      ▼
   ┌──────────────────────────────────────────────────────────┐
   │ 2 · MEASURED TRIGGERS (L195, L332)                       │
   │     corpus size vs ANN headroom (L182)                   │
   │     filter complexity vs WHERE clauses (L180)            │
   │     ops budget vs self-hosting cost (L150, L274)         │
   └──────────────────┬───────────────────────────────────────┘
                      ▼
   ┌──────────────────────────────────────────────────────────┐
   │ 3 · THE SPECIALIST CHOICE (L184-185)                     │
   │     managed scale + zero ops → Pinecone (L184)           │
   │     self-hosted control + filters → Qdrant (L185)        │
   └──────────────────┬───────────────────────────────────────┘
                      ▼
   ┌──────────────────────────────────────────────────────────┐
   │ 4 · THE MIGRATION (L222)                                 │
   │     sync pipeline → re-measure on the golden set (L195)  │
   │     the interface (L155) keeps it reversible             │
   └──────────────────────────────────────────────────────────┘

The flow is the process: data first, triggers measured, specialist chosen, migration reversible — and every step re-measured (L195).

• The default: pgvector (L183). The data lives in Postgres (L115); vectors join it. Filters are WHERE clauses (L180); one database to back up and secure (L172). It handles millions of rows with HNSW (L182) — the default until a trigger fires.
• Trigger 1: corpus scale (L182). Postgres's ANN performance degrades at the tens-of-millions scale (L182) — the trigger is measured headroom, not a magic number. When the corpus genuinely outgrows it, the specialist's purpose-built indexes (L184–185) win.
• Trigger 2: filter load (L180, L189). Filtering-heavy retrieval — complex metadata composition, pre-ANN filters (L189) — is a specialist's native shape (Qdrant's payloads, L185; Pinecone's filter API, L184). When WHERE clauses and pgvector's filtering feel like a fight, the trigger fires.
• Trigger 3: ops and cost shape (L150, L274). Managed ops (Pinecone, L184) win when the ops budget is zero; self-hosted control (Qdrant, L185) wins when the managed bill (L150) and the ops team's capacity (L274) point that way. The cost model (L150) — per-token vs your infra — is the deciding axis.
• The interface (L155). The retrieval layer behind searchIndex(): the store is a deployment choice, and the migration is a pipeline change (L222), not a rewrite (L341).

• The startup default. Postgres already holds users and docs (L115) → pgvector (L183). The fastest correct RAG, one database.
• The growth trigger. The corpus hits tens of millions of chunks, ANN latency creeps (L182, L151) → measured (L195) → migrate to a specialist (L186).
• The multi-tenant platform (L320). Filtering-heavy by design — tenant filters (L320) on every query → Qdrant's payloads (L185) or Pinecone's namespaces (L184).
• The zero-ops team. No DBA time, serverless posture (L283) → Pinecone (L184) — the managed bill (L150) is the trade.
• The cost-conscious self-hosters. Control and cost shape (L150) over managed (L184) → Qdrant (L185), with the ops story (L274) staffed.

The through-line: every store is right for someone — the L186 rule is how you know which one is right for you, measured against your data, your filters, your ops, and your cost (L150).

Say it in four moves:

1. The rule. "Postgres first (L183) — my data lives there (L115). Specialists on measured triggers (L186)."
2. The triggers. "Corpus scale (L182), filter load (L180), ops availability, and the cost shape (L150) — each measured, not guessed (L195)."
3. The choice. "Managed scale + zero ops → Pinecone (L184). Self-hosted control + filters → Qdrant (L185)."
4. The insurance. "Behind an interface (L155) — the store is a deployment choice, reversible, and the golden set (L195) re-measures after the move."

• The rule, not the store, is the deliverable (L186). The senior answer names the process — data, triggers, choice, reversibility — not a favorite. "We use X" is a fact; "here's how we'd choose X" is an architecture.
• The triggers are measured numbers (L195, L332). Corpus size, ANN latency (L151), filter performance, and cost (L150) are metrics (L332) — the migration decision is a dashboard review, not a vibe.
• The interface is the migration insurance (L155, L222). Behind searchIndex(), a store change is a pipeline change (L222) — the L341 discipline applied to the vector layer.
• The choice composes with the embedding decision (L181). Dimensionality (L182) and the store's memory model are made together (L186) — the L181 and L186 decisions are one decision.
• The rule re-runs (L341). Scale grows, ops change, bills change — the L186 rule is re-applied on a schedule (L341), like any architecture review. The store is never "set".

• A favorite store (L186). "We use Pinecone" without the rule — the choice that can't be defended or revisited.
• pgvector forever (L183). The default past the triggers — ANN latency creeps (L182), filters fight, and the rule was never applied.
• Specialist at the start (L184). New infrastructure and a sync pipeline (L222) before the corpus justifies it (L150).
• Migrating without measuring (L195). Moving on a hunch — the golden set should confirm the specialist actually wins.
• No interface (L155). Store calls throughout the app — the migration that becomes a rewrite (L341).
• Ignoring the cost shape (L150). Per-token billing on a steady, huge corpus — the L186 cost axis unexamined.

• Start with pgvector (L183) — the default until a measured trigger fires (L186).
• Name the triggers in advance (L186) — corpus size, filter load, ops, cost — and monitor them (L332).
• Keep retrieval behind an interface (L155) — the store is a deployment choice.
• Migrate with a sync pipeline (L222) — and re-measure on the golden set (L195).
• Decide dimensionality with the store (L181, L182) — one decision, not two.
• Re-run the rule on a schedule (L341) — the store is never "set".

Q: Which vector database do you use?

A: It's a decision rule, not a favorite (L186). Default: pgvector (L183) — my data lives in Postgres (L115), filters are WHERE clauses (L180), one database to operate. I move on measured triggers: corpus scale outgrew the ANN headroom (L182), the filter load needs a specialist's structure (L180), or the ops/cost shape (L150) flips — Pinecone (L184) for managed scale, Qdrant (L185) for self-hosted control. Behind an interface (L155), reversible.

Q: Why pgvector by default?

A: Because the data already lives in Postgres (L115). Vectors join it — one database, one backup, one security story (L172). Metadata filters are WHERE clauses (L180), tenant isolation is a column filter (L320), and HNSW (L182) handles millions of rows. It's the cheapest correct default (L150) until a measured trigger says otherwise.

Q: What triggers a move to a specialist?

A: Four measured triggers (L186): corpus scale — Postgres's ANN (L182) latency degrades past its headroom; filter load — filtering-heavy retrieval (L180) fights the relational shape; ops — managed beats self-hosting (L184) or control beats managed cost (L150); and the cost shape — per-token vs your infra. Each is a metric (L332), reviewed on the dashboard (L195), not a vibe.

Q: How do you migrate without pain?

A: The interface first (L155). Retrieval behind searchIndex() means a store change is a pipeline change (L222) — a sync pipeline (L222) moves the chunks, the golden set (L195) re-measures quality, and the old store stays live until the numbers confirm the new one (L341). The migration is reversible because the app never knew which store it was talking to.

• What are your four triggers, and how do you measure them (L195)?
• How does the embedding dimensionality interact with the choice (L181)?
• How does the cost shape decide between managed and self-hosted (L150)?
• What does the interface look like (L155)?
• How often do you re-run the rule (L341)?

The senior read: the rule reads the axes for your context — data, scale, filters, ops, cost — and picks the column.

// The L186 decision rule, as a review checklist (L186, L195, L332).
const storeDecision = {
  // TRIGGER 1 — DATA LOCATION (L115): where does the data live?
  dataInPostgres: true,                       // → pgvector is the default (L183)

  // TRIGGER 2 — SCALE (L182): measured, not guessed (L332).
  corpusSize: 4_200_000,                      // chunks
  annLatencyMs: 38,                           // p95, from metrics (L332, L151)
  //   trigger: latency > budget (L151) or corpus past ANN headroom (L182)

  // TRIGGER 3 — FILTER LOAD (L180): how heavy is the retrieval?
  filterComplexity: 'tenant + date + source', // WHERE clauses fine? → stay
  //   trigger: complex pre-ANN filters (L189) fight the relational shape

  // TRIGGER 4 — OPS + COST (L150, L274): who runs it, at what shape?
  opsBudget: 'one part-time DBA',             // → self-hosting is risky (L274)
  costShape: 'per-token vs infra',            // measured per quarter (L332)

  // THE VERDICT (L186) — computed from the metrics, not from taste.
  verdict() {
    if (this.dataInPostgres && this.annLatencyMs < 100 && this.corpusSize < 1e7) {
      return 'pgvector (L183) — the default still holds';
    }
    if (this.filterComplexity.includes('+') || this.corpusSize >= 1e7) {
      return this.opsBudget === 'zero' ? 'Pinecone (L184)' : 'Qdrant (L185)';
    }
    return 'pgvector (L183)';
  },
};

// THE INTERFACE (L155) — the app never knows which store it is.
// searchIndex() is implemented by pgvector, Qdrant, or Pinecone (L186).

What the reader must SEE — the rule as data:

  corpusSize + annLatencyMs  → measured triggers (L332, L195)
  filterComplexity + opsBudget → the specialist axes (L180, L274)
  verdict()                  → the rule, not a favorite (L186)
  searchIndex()              → the reversible interface (L155)

  The store is a decision. The decision is measurable.

• The rule is a latency review (L151, L332). ANN latency (L182) is the trigger metric — p95 vs the TTFT budget (L145), measured continuously (L332).
• The cost shape is a quarterly review (L150, L332). Per-token vs infra spend — the L186 cost axis re-measured as the corpus and traffic grow.
• The migration is a pipeline project (L222). Sync, backfill, and cutover — the L222 discipline, with the golden set (L195) as the gate.
• The interface adds a call (L151). One indirection — negligible against the store's own latency (L182) and the cache (L171).

• The rule: pgvector first (L183), specialists on measured triggers (L186).
• The triggers: corpus scale (L182), filter load (L180), ops, cost shape (L150).
• The choice: Pinecone (L184) for managed, Qdrant (L185) for self-hosted.
• The insurance: the interface (L155) — the store is reversible.
• The migration: sync pipeline (L222) + golden set re-measure (L195).
• The rule re-runs: on a schedule (L341) — the store is never "set".

VECTOR DB SELECTION = a rule, not a favorite

THE RULE (L186)
  1 data in Postgres (L115)? → pgvector (L183) — the default
  2 measured triggers (L195, L332):
     corpus scale  past ANN headroom (L182)
     filter load   past the relational shape (L180)
     ops           managed beats self-host (L184)
     cost shape    per-token vs infra (L150)
  3 the specialist choice:
     managed scale + zero ops → Pinecone (L184)
     self-hosted + filters    → Qdrant (L185)
  4 the migration:
     sync pipeline (L222) → golden set (L195) → reversible (L155)

THE INSURANCE
  searchIndex() behind an interface (L155)
  the store is a deployment choice, re-run on a schedule (L341)

INTERVIEW, 4 MOVES
  1 rule    "Postgres first, specialists on triggers"
  2 triggers "scale, filters, ops, cost — measured (L332)"
  3 choice  "Pinecone for managed, Qdrant for self-hosted"
  4 insurance "the interface keeps it reversible (L155)"

You now hold the decision process: Postgres first, specialists on measured triggers, the interface as insurance, and the golden set as the gate. "Which vector database?" is answered by a rule — and a rule is an architecture; a favorite is just a preference.

Next: the retrieval quality upgrade — hybrid search (L187), where keyword precision meets semantic recall.