Case Study: Choosing and Running a Store for a SaaS Knowledge Base

Decision and design

Situation: a SaaS product has 300 customer tenants, 6 million chunks in total (768 dimensions), a team already running PostgreSQL, and a requirement that no tenant ever sees another's data. Decision: start with PostgreSQL + pgvector because it keeps vectors next to existing data, supports row-level security and transactions, and the team already operates it; revisit a dedicated engine only if measured needs (scale, latency, features) exceed it. Sizing: 6 million × 768 × 4 bytes is about 18 GB of vectors, plus an HNSW index and payload, so plan roughly 50 to 60 GB with headroom on a machine with enough RAM, and consider halfvec to halve it. Schema: chunks(id, tenant_id, doc_id, content_hash, text, embedding, model, created_at) with a deterministic ID. Indexes: HNSW on embedding with the matching operator class, a B-tree on tenant_id; iterative scans for per-tenant filtered queries, or a partial index for a few very large tenants. Search: tenant filter always applied, hybrid full-text + vector with RRF, rerank, score threshold. Operations: batch idempotent upserts, nightly incremental refresh by content hash, replicas for reads, point-in-time recovery, a model-upgrade runbook with a parallel table. Quality and safety: a golden set with recall@k gating changes, automated tenant-isolation tests, p95 latency and recall dashboards.

A measured, secure, rebuildable store

Start simple and exact, measure recall and latency, then add the index, filters and scale you actually need.

The design on one page

Each line maps to a section of this course.

Engine      PostgreSQL + pgvector (vectors beside data, RLS, transactions, familiar ops)   (Sec 1, 2)
Sizing      6M x 768 x 4B ~ 18 GB vectors (+ index + payload) -> ~50-60 GB; halfvec if needed (Sec 6)
Indexes     HNSW (matching opclass) + B-tree on tenant_id; iterative scan / partial index    (Sec 3, 4)
Search      tenant filter ALWAYS, full-text + vector with RRF, rerank, score threshold        (Sec 4)
Ingest      batched idempotent upserts by deterministic id; nightly refresh by content hash  (Sec 1, 6)
Resilience  read replicas, PITR backups, model-upgrade runbook with a parallel table         (Sec 6, 7)
Quality     golden set recall@k gates changes; automated tenant-isolation tests; dashboards   (Sec 3, 7)

Prove it with a pilot

Load one real tenant's data, run the golden set and a load test, and only then commit to the design.

Quick check: Why does the case study start with PostgreSQL + pgvector?

• Vector search is impossible elsewhere
• PostgreSQL is always the fastest option
• Dedicated engines cannot filter
• It keeps vectors with existing data, supports row-level security, and the team already runs it