PostgreSQL + pgvector

PostgreSQL + pgvector is the boring default for vector storage — a Postgres extension adding a vector type and ANN indexes (HNSW, IVFFlat), so embeddings live beside your data with filters that compose as SQL (L180) — one database for data and vectors, with a migration path to specialists (L184–185) when the L186 decision rule says scale demands it.

The one-sentence interview answer: "pgvector is the default (L183). It's a Postgres extension: a vector column, HNSW and IVFFlat indexes (L182), and distance operators. My data already lives in Postgres (L115) — so vectors sit beside it, metadata filters are WHERE clauses (L180), tenant isolation is a filter on the tenant column (L320), and there's one database to back up and operate. It handles millions of rows with HNSW (L182). When the corpus, the filter load, or the ops needs outgrow it — that's when the L186 rule points at a specialist (L184–185). Boring, but it's the right first answer."

Think of pgvector as putting the library's card catalog in the same building as the books. Your data (books) already live in Postgres; pgvector adds the catalog (vector indexes) in the same building. One building — one janitor (DBA), one backup, one alarm system (security). The specialist vector stores (L184–185) are separate buildings for the catalog — sometimes worth it (L186), but the default is: your catalog belongs where your books are.

   ONE DATABASE (L183)               THE SPECIALIST PATH (L184-186)
   ┌──────────────────────┐          ┌──────────────────────────┐
   │ Postgres (L115)      │          │ Postgres                 │
   │  users, orders,      │          │  (data, business logic)  │
   │  docs, chunks        │          └───────────┬──────────────┘
   │  + vector column     │                      │ sync
   │  + HNSW index (L182) │          ┌───────────▼──────────────┐
   │  one database,       │          │ specialist vector DB     │
   │  one ops story       │          │ (Pinecone/Qdrant, L184-5)│
   └──────────────────────┘          └──────────────────────────┘

The mental model is one building: pgvector keeps vectors with the data, one database to run — and the specialist path is a documented upgrade, not the starting point.

   the ingestion pipeline (L176)
        │
        ▼
   ┌──────────────────────────────────────────────────────────┐
   │ 1 · SCHEMA (L183)                                        │
   │     chunks table: id, text, source, date, tenant (L180)  │
   │     + embedding vector(1536) — the pgvector column       │
   └──────────────────┬───────────────────────────────────────┘
                      ▼
   ┌──────────────────────────────────────────────────────────┐
   │ 2 · INDEX (L183)                                         │
   │     HNSW on the embedding column (L182)                  │
   │     or IVFFlat for very large / memory-lean cases        │
   └──────────────────┬───────────────────────────────────────┘
                      ▼
   ┌──────────────────────────────────────────────────────────┐
   │ 3 · QUERY (L189, L180)                                   │
   │     WHERE tenant = $1                                  │
   │     ORDER BY embedding <=> $2  LIMIT 5                   │
   │     — filters compose as SQL (L320)                      │
   └──────────────────────────────────────────────────────────┘
                      ▼
   top-k rows → context (L191) → citations (L192)

The flow is the database-native path: schema with a vector column → ANN index → SQL query with filters — the L175 index as a table.

• The extension. CREATE EXTENSION vector adds the vector(n) type, distance operators (<-> L2, <=> cosine), and ANN index methods — HNSW and IVFFlat (L182). Same Postgres, same backups, same ops.
• The schema. A chunks table: the text, its metadata columns (source, date, tenant, section — L180), and an embedding vector(1536) column (dimensionality from L181). Metadata filters are WHERE clauses — the same SQL your team already writes.
• The index. CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops) — the ANN index (L182). HNSW is the default for quality; IVFFlat (ivfflat) for very large, memory-lean cases. The dials (L182) are query-time: hnsw.ef_search, ivfflat.probes.
• The query. SELECT … WHERE tenant = $1 ORDER BY embedding <=> $2 LIMIT 5 — filter (L180), similarity, top-k (L189) in one statement. The filter is applied by construction (L320).

• The default RAG stack. Postgres already holds users, docs, and metadata (L115); pgvector adds the vector column — one database for the whole app (L183).
• Multi-tenant SaaS (L320). Tenant isolation is a WHERE tenant = $1 — a row-level concern, auditable, and enforceable like any other data access (L180).
• Startups and MVPs. The fastest path to a working RAG system — no new infrastructure, no sync pipeline (L186).
• Transactional + semantic. The same database answers "recent orders for this user" and "chunks similar to this query" — no ETL boundary (L183).
• Hybrid search (L187). Postgres full-text search (tsvector) + pgvector in one query — the hybrid (L187) without a second store.

The through-line: pgvector is the "start here" answer — the vector store that composes with the database you already run, with a documented upgrade path (L186).

Say it in four moves:

1. The default. "pgvector — a Postgres extension: a vector type, HNSW/IVFFlat indexes (L182), distance operators. Vectors beside my data (L115)."
2. The compose. "Metadata filters are WHERE clauses (L180); tenant isolation is a filter on the tenant column (L320); hybrid search (L187) composes with full-text in one query."
3. The scale. "HNSW handles millions of rows (L182); the dials (ef_search) are query-time and measured (L195)."
4. The path. "When the corpus, filter load, or ops outgrow it, the L186 rule points at a specialist (L184–185). pgvector is the default, not the destination."

• Vectors beside data beats a separate store by default (L183). One database: one backup, one security story (L172), one query language. The senior default is "use what you already run" — the specialist is the exception with triggers (L186).
• Filters compose as SQL (L180). The metadata schema (L180) and the tenant filter (L320) are just columns — row-level security (L238) and indexes apply like any other data. This is pgvector's quiet superpower: your existing database discipline transfers.
• HNSW is the quality default; IVFFlat is the scale lever (L182). pgvector supports both — the L182 trade applied inside Postgres, with dials set for the budget (L151) and measured (L195).
• The migration path is the design (L186). The senior answer includes when to leave pgvector — corpus scale, filter load, managed-ops needs — and the sync pattern (L222) that makes the move reversible.
• Hybrid search is native (L187). tsvector + pgvector in one query — the L187 hybrid without a second system. Another reason the default composes so well.

• A specialist "because it's for vectors". New infrastructure, a sync pipeline, and an ops story where pgvector would do (L186) — the default skipped.
• No index. A vector column without HNSW/IVFFlat (L183) — every query is a sequential scan (L182).
• Filters as an afterthought. Tenant filtering by loading everything and filtering in code (L320) — the WHERE clause exists for a reason (L180).
• Wrong dimensionality. vector(1536) when the model outputs 256 (L181) — schema/embedding mismatch, fixed by re-ingestion (L341).
• No migration plan. No documented trigger for the specialist path (L186) — scaling becomes a crisis instead of a decision.
• IVFFlat for a small corpus. The index needs enough rows to cluster well (L182) — HNSW is the default for a reason.

• Start with pgvector (L183) — the default until the L186 rule says otherwise.
• Make metadata real columns (L180) — source, date, tenant — and index the filter columns.
• Use HNSW by default (L182); IVFFlat for very large, memory-lean cases.
• Set the dials per query (L182) — ef_search for the latency budget (L151), measured (L195).
• Enforce tenant filters in the query (L320) — WHERE tenant = $1, by construction.
• Document the migration trigger (L186) — corpus size, filter load, ops needs.

Q: Where do you store embeddings?

A: pgvector in Postgres, by default (L183). It's an extension — a vector type, HNSW and IVFFlat indexes (L182), distance operators. My data already lives in Postgres (L115), so vectors sit beside it: metadata filters are WHERE clauses (L180), tenant isolation is a column filter (L320), and there's one database to operate. When the L186 rule says scale demands a specialist (L184–185), there's a documented path.

Q: How does pgvector handle scale?

A: HNSW indexes handle millions of rows (L182) — the ANN trade (L189) inside Postgres, with ef_search as the query-time dial. For very large, memory-lean cases, IVFFlat clusters. The real answer is the L186 rule: pgvector is the default, and the triggers for leaving it are corpus scale, filter load, and managed-ops needs.

Q: How do filters work?

A: Metadata is columns (L180) — so filters are WHERE clauses: WHERE tenant = $1 AND source = $2. The tenant filter (L320) is enforced by the query, indexable like any column, and the vector search composes with it in one statement. That's pgvector's advantage — your existing database discipline transfers.

Q: When do you move to a specialist vector DB?

A: When the L186 rule triggers (L186): the corpus outgrows Postgres's ANN performance, the filter load needs a specialist's structure, or managed vector ops beat self-managing. Pinecone (L184) for managed, Qdrant (L185) for self-hosted. The move is a sync pipeline (L222) — documented, reversible, and driven by measured triggers, not fashion.

• How does HNSW in pgvector compare to a specialist's (L182)?
• How does hybrid search compose in one query (L187)?
• What are the L186 triggers for leaving pgvector?
• How do you enforce tenant isolation at the query layer (L320)?
• How does the migration path work (L222)?

The senior read: pgvector is the default column; the specialists are the scale columns — L186 is the decision rule between them.

-- SCHEMA (L183): the chunks table with a vector column (L180, L181).
CREATE EXTENSION vector;

CREATE TABLE chunks (
  id        bigserial PRIMARY KEY,
  text      text NOT NULL,
  source    text NOT NULL,              -- L180 — citation backbone (L192)
  date      date NOT NULL,              -- L180 — freshness (L140)
  tenant    text NOT NULL,              -- L180 — isolation (L320)
  section   text,                       -- L180 — from the parser (L177)
  embedding vector(1536)                -- dimensionality from L181
);

-- INDEX (L182): HNSW by default — quality-first ANN.
CREATE INDEX chunks_hnsw ON chunks USING hnsw (embedding vector_cosine_ops);

-- QUERY (L189, L180, L320): filter + similarity + top-k, one statement.
SELECT text, source, section,
       embedding <=> $1 AS distance
FROM chunks
WHERE tenant = $2                        -- L320 — isolation by construction
  AND date >= $3                         -- L180 — freshness filter (L140)
ORDER BY embedding <=> $1                -- cosine similarity (L182)
LIMIT 5;                                 -- top-k (L189)

-- THE DIAL (L182, L195): recall vs speed, per query.
SET hnsw.ef_search = 128;                -- higher = better recall, slower

What the reader must SEE — the whole lesson in SQL:

  CREATE EXTENSION vector     → the vector type (L183)
  metadata as real columns    → filters as WHERE (L180, L320)
  HNSW index + ef_search dial → ANN, tunable (L182, L195)
  one query: filter + search  → top-k in one statement (L189)

  Vectors beside data, filters as SQL, ANN under the hood.

• HNSW is the latency answer (L151). The index keeps ANN queries sub-100ms at millions of rows (L182) — within the TTFT budget (L145).
• The filter columns need indexes (L180). WHERE tenant = $1 without an index on tenant is a scan — index the metadata columns the queries filter on (L183).
• Memory vs disk (L182). HNSW is memory-hungry; IVFFlat is the disk-lean alternative — the L182 trade inside Postgres (L186).
• Maintenance is your Postgres's (L183). One database — one backup, one VACUUM story (L119) — versus the sync pipeline a specialist needs (L222).

• pgvector = the boring default: vector type, HNSW/IVFFlat, distance ops in Postgres (L183).
• Metadata = columns; filters = WHERE clauses (L180, L320).
• HNSW default, IVFFlat for scale (L182); dial = ef_search (L195).
• One database — data, vectors, backups, ops (L115).
• The path: L186 triggers → Pinecone (L184) / Qdrant (L185).
• Hybrid (L187) composes in one query with full-text.

PGVECTOR = the boring default for vector storage

THE EXTENSION (L183)
  CREATE EXTENSION vector
  vector(n) type · <-> / <=> operators · HNSW + IVFFlat indexes

THE SCHEMA (L180)
  metadata as real columns: source, date, tenant, section
  filters are WHERE clauses — your existing SQL

THE QUERY (L189, L320)
  WHERE tenant = $1        isolation by construction
  ORDER BY embedding <=> $2  cosine similarity
  LIMIT 5                  top-k
  SET hnsw.ef_search       the recall/speed dial (L182, L195)

THE SCALE (L182)
  HNSW — millions of rows, quality-first
  IVFFlat — very large, memory-lean

THE PATH (L186)
  stay → data stays with Postgres (L115)
  leave → triggers: corpus size, filter load, managed ops
       → Pinecone (L184) / Qdrant (L185) — a sync pipeline (L222)

INTERVIEW, 4 MOVES
  1 default "vectors beside my data (L115)"
  2 compose "filters are WHERE clauses (L180)"
  3 scale   "HNSW + the dial, measured (L182, L195)"
  4 path    "the L186 triggers to a specialist"

You now hold the first answer to "where do embeddings live": pgvector — vectors beside your data, filters as WHERE clauses, ANN under the hood, and a documented path to scale. Boring is a feature: one database, your existing SQL, and the L186 rule to tell you when boring stops being right.

Next: the managed specialist — Pinecone (L184), when scale and zero ops point away from Postgres.