Design Vector Database Schema and Tenancy from Day One

Tier: Foundation · Workflow area: RAG data pipelines

Teams that "just add a collection" discover at month three that they cannot filter by department, cannot delete a customer's data cleanly, and cannot tell which embedding model produced which vector. This playbook walks the selection framework and a metadata schema that keeps every later playbook — sync, tuning, ACLs, observability — possible without a re-index.

When to reach for this

• •
  Choosing the first (or next) vector store for a RAG workload and needing a defensible engine decision.
• •
  Designing the collection/namespace layout and metadata schema before the initial index build.
• •
  An existing single-tenant index must now serve multiple teams, clients, or products.
• •
  Compliance asks "how do you delete a user's data from the AI index?" and the current answer is a shrug.

The workflow

1. 1.

   Run the engine decision framework — in order, first match wins.

   • •
     Already run Postgres and expect under ~10M vectors? pgvector. One less system, real SQL, RLS for ACLs.
   • •
     Corpus lives in the warehouse and governance gravity is strong? Warehouse-native (Cortex Search, Databricks Vector Search, BigQuery vector) — see pb-39.
   • •
     Need scale beyond 10M, built-in hybrid search, or hard multi-tenancy features? Dedicated OSS (Qdrant, Weaviate, Milvus) self-hosted, or Pinecone if managed-service operations is the constraint.
   • •
     Heavy existing OpenSearch/Elastic estate? Their vector types keep search consolidated.

   Write the choice down with the two rules that fired. Expected output: a one-paragraph decision record, not a 40-page bake-off.

2. 2.

   Design the metadata schema before the first upsert. Minimum viable schema for any engine:

   Copy

   -- pgvector (local-first reference implementation)
   CREATE EXTENSION IF NOT EXISTS vector;
   CREATE TABLE rag_chunks (
     chunk_id     TEXT PRIMARY KEY,      -- deterministic: sha256(doc_id + idx + content_hash)
     doc_id       TEXT NOT NULL,
     tenant_id    TEXT NOT NULL,         -- tenancy from day one, even with one tenant
     acl_tags     TEXT[] NOT NULL DEFAULT '{}',
     source_uri   TEXT,
     content      TEXT NOT NULL,
     content_hash TEXT NOT NULL,
     model_version TEXT NOT NULL,        -- which embedding model produced this vector
     updated_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
     embedding    vector(1024)
   );
   CREATE INDEX ON rag_chunks (tenant_id, doc_id);
   

   Every field earns its place: doc_id enables delete propagation (pb-36), tenant_id and acl_tags enable isolation and ACL filtering (pb-38), content_hash enables incremental sync, model_version enables safe model migrations (pb-30). In Qdrant/Pinecone these become payload/metadata fields with the same names.

3. 3.

   Pick the tenancy pattern deliberately. Three patterns, in rising isolation and cost:

   • •
     Metadata filter (tenant_id field, filter every query): cheapest, fine for internal teams; isolation is only as strong as query discipline — enforce it in a shared retrieval function, never in each caller.
   • •
     Namespace/collection per tenant (Pinecone namespaces, Qdrant collections): hard partition, per-tenant delete is one call, noisy-neighbor risk drops; watch per-collection overhead beyond a few hundred tenants.
   • •
     Database/instance per tenant: for regulated clients only; operationally expensive.

   For pgvector, metadata filter plus row-level security gives filter-pattern economics with enforced isolation.

4. 4.

   Prove tenancy with a leakage test, not a diagram. Seed two tenants with distinctive documents, query tenant A for tenant B's content. The metric that proves this step worked: cross-tenant leakage = 0 results over a 50-query adversarial set (paraphrases of tenant B's content asked as tenant A). Any nonzero result fails the design.

   Copy

   res = search(query="B's secret pricing table", tenant_id="tenant_a", k=10)
   assert all(r["tenant_id"] == "tenant_a" for r in res), "LEAKAGE"
   

5. 5.

   Prove deletability. Delete one document and one tenant; verify vector counts drop by exactly the expected number and a follow-up query returns nothing from the deleted scope. Time it — deletion latency at realistic scale is a compliance SLA, and some engines soft-delete with delayed compaction.

6. 6.

   Capacity-sanity the choice. Vectors × dimension × 4 bytes gives raw index memory (1M × 1024-dim ≈ 4 GB before graph overhead); check it against the chosen engine's deployment size, and note quantization (pb-32) as the lever if memory is the binding constraint.

Verify before you trust it

• Decision recordexists naming the engine and the rules that selected it.
• Leakage test0/50 adversarial cross-tenant queries return foreign chunks.
• Deletability testper-document and per-tenant deletes reconcile exactly against expected counts, with measured latency.
• Round-trip checkupsert 1,000 chunks, read them back, confirm every metadata field survives with correct types — metadata silently coerced to strings is a common engine gotcha.

Pitfalls

• Choosing the engine by benchmark blog posts. Vendor QPS charts use their favorable dimensions and recall targets. Your framework inputs are operational: what you already run, corpus size, tenancy, and governance.
• Tenancy enforced in application code paths. If every caller must remember the filter, one forgotten filter is a breach. Centralize in one retrieval function or push into the engine (RLS, namespaces).
• No model_version field. The first embedding-model upgrade forces a blind full wipe instead of a versioned cutover.
• Unbounded metadata. Stuffing full document text and 30 ad-hoc fields into payloads bloats memory and slows filtering; keep payloads to what queries actually filter or display.
• Assuming deletes are instant. Several engines mark-and-compact; until compaction, "deleted" vectors can still cost memory and, in rare bugs, surface. Test deletion behavior on your actual engine version.

Tools

• pgvectordefault teaching and small-to-mid production store; SQL, RLS, and transactional metadata in one system.
• Qdrant / Weaviate / MilvusOSS dedicated engines when scale, hybrid search, or collection-level tenancy justify a separate system; Qdrant Cloud has a free tier for labs.
• Pineconemanaged option where namespaces give easy per-tenant isolation and ops is outsourced.

Client-safe variant

For consulting deliverables, run the whole design exercise against a synthetic two-tenant corpus so the leakage and deletability tests can be demonstrated to the client without touching their data. Default the engine recommendation to what the client already operates (existing Postgres → pgvector; existing warehouse → warehouse-native) because new-system approvals are the slowest path in client environments, and document the tenancy pattern and deletion SLA in the handover — those two answers are what the client's security review will ask for first. If the client mandates a managed service, confirm data-residency region and deletion guarantees in the vendor contract before indexing anything real.