pgvector

pgvector extends PostgreSQL with the `vector` type and ANN indexes (IVFFlat, HNSW) so relational rows and embeddings live together. Ideal when metadata joins, transactions, row-level security, and modest vector scale (< low tens of millions depending on hardware) matter more than dedicated vector SaaS latency.

					CREATE TABLE docs (
  id bigserial PRIMARY KEY,
  tenant_id uuid NOT NULL,
  body text,
  embedding vector(1536)
);
CREATE INDEX ON docs USING hnsw (embedding vector_cosine_ops);
				

Query pattern: filter by SQL predicates first, then order by distance operator (`<->`, `<#>`, `<=>`) with LIMIT.

On interviews: argue when pgvector beats a separate store (single DB ops, ACID, joins) vs when to outgrow it (billions of vectors, extreme QPS, specialized filtering).

Common pitfalls: no index until table is huge; IVFFlat without enough lists or analyze; storing embeddings without model version column; sequential scan on every query; ignoring vacuum bloat on heavy update tables.

The trade-off is operational simplicity and unified data model versus ANN performance ceilings and sharing Postgres resources with OLTP traffic.

Checklist:

• Show vector column + HNSW/IVFFlat index.
• Combine SQL filters with distance ORDER BY.
• Plan model version and re-embed migrations.
• Size connection pool and memory for ANN.
• Define threshold to move to dedicated vector DB.