Postgres and pgvector

The pragmatic default

Many AI products already run on Postgres. pgvector adds a vector type, distance operators, and ANN indexes so you can keep documents, ACLs, and embeddings in one transactional store — excellent until scale forces a split.

You still need the concepts from Vector databases: same embedding model for query and docs, stable chunk ids, recall@k evals. pgvector changes the ops contract, not the math. If you cannot explain ANN, filtered search, and system of record vs derived index, you are not ready to put embeddings in production Postgres either.

flowchart TB
  App[App / RAG service] --> PG[(Postgres)]
  PG --> Rel[users, docs, acls]
  PG --> Vec[chunks.embedding vector]
  Vec --> IVF[ivfflat / hnsw index]
  Rel --> Join[SQL joins + filters]
  IVF --> Join
  Join --> Pack[Top-k for LLM]

One-sentence definition

pgvector is an extension that stores embeddings as a first-class Postgres type and supports exact and ANN similarity search alongside normal SQL filters, joins, and transactions.

What you get that a “numpy in RAM” demo does not

Analogy: a dedicated vector DB is a specialty tool for “nearest neighbors at huge QPS.” pgvector is “put the specialty index inside the database you already trust for money and auth.”

Why teams love it

When pgvector is the wrong first move

• Greenfield with no Postgres and a pure ANN SaaS already approved
• Billion-scale vectors on day one with multi-region ANN SLAs
• Team has zero Postgres ops capacity but strong managed-vector budget
• Embedding write QPS already saturates your OLTP primary in load tests

Otherwise, try pgvector before inventing a second datastore. See Choosing vector stores.

Interview cue: “We put vectors in Postgres because our ACL and chunk text already live there — not because HNSW in Postgres beats every specialized engine at every scale.”

Mental model: Postgres is still Postgres

pgvector does not turn Postgres into a different product. You still get:

• MVCC, WAL, vacuum, TOAST, planner choices
• Index bloat from updates/deletes
• Connection limits and lock contention
• The same need for EXPLAIN (ANALYZE, BUFFERS)

What changes is that a large HNSW graph can dominate RAM and I/O the way a giant GIN index can — except the query pattern (filtered ORDER BY embedding <=> $1 LIMIT k) is easier to get subtly wrong.

flowchart LR
  subgraph StillPG[Still Postgres]
    WAL[WAL / PITR]
    Vac[Autovacuum]
    Plan[Planner + buffers]
  end
  subgraph New[pgvector adds]
    Type[vector type]
    Ops[distance operators]
    ANN[HNSW / IVFFlat]
  end
  StillPG --> Query[Filtered ANN query]
  New --> Query

Schema sketch

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE docs (
  id          uuid PRIMARY KEY,
  tenant_id   uuid NOT NULL,
  title       text NOT NULL,
  source_uri  text,
  doc_type    text NOT NULL DEFAULT 'generic',
  deleted_at  timestamptz,
  updated_at  timestamptz NOT NULL
);

CREATE TABLE chunks (
  id           uuid PRIMARY KEY,
  doc_id       uuid NOT NULL REFERENCES docs(id) ON DELETE CASCADE,
  tenant_id    uuid NOT NULL,
  chunk_index  int NOT NULL,
  content      text NOT NULL,
  content_hash text NOT NULL,
  embedding    vector(1536) NOT NULL,
  embedding_model text NOT NULL,
  updated_at   timestamptz NOT NULL,
  acl_roles    text[] NOT NULL,
  UNIQUE (doc_id, chunk_index, embedding_model)
);

CREATE INDEX chunks_hnsw ON chunks
  USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

CREATE INDEX chunks_tenant_doc ON chunks (tenant_id, doc_id);
CREATE INDEX chunks_acl ON chunks USING gin (acl_roles);
CREATE INDEX chunks_tenant_updated ON chunks (tenant_id, updated_at DESC);

Ship rule: put embedding_model (or collection version) in the row or table name so you never mix dimensions silently.

Why denormalize tenant_id onto chunks

You could join docs for every ANN query. In practice, denormalizing tenant_id (and often ACL) onto chunks keeps the hot path simple, makes partial indexes easier, and reduces “forgot the join filter” bugs. Keep docs as the place for titles and source URIs; keep chunks as the retrieval unit.

Optional: partial indexes for hot subsets

-- Example: only index “live” policy chunks for a product surface
CREATE INDEX chunks_hnsw_policies ON chunks
  USING hnsw (embedding vector_cosine_ops)
  WHERE doc_type = 'policy' AND deleted_at IS NULL;

Partial ANN indexes help when most queries share a predicate — measure before proliferating. Wrong partial predicate → silent misses that look like “the embedder got worse.”

Extension install and version pinning

• Install via your platform’s package (postgresql-15-pgvector, Docker image with the extension, etc.).
• Pin extension version in migrations / runbooks the same way you pin app dependencies.
• CREATE EXTENSION vector is a privileged DDL step — plan who can run it in staging vs prod.
• After upgrades, re-check operator classes and GUC names (hnsw.ef_search, ivfflat.probes) against the release notes.

Distance operators

Match the operator and opclass to how you train/normalize embeddings. For cosine, many teams L2-normalize at write and query time so cosine and inner-product rankings behave predictably.

-- Store normalized vectors if you use cosine / inner product consistently
-- (normalize in app before INSERT / at query bind time)
-- Pseudocode: v = v / ||v||

Ship rule: the index opclass, the ORDER BY operator, and the embedding model’s intended metric must agree. Mixing vector_l2_ops with <=> is a classic silent quality bug.

Half-precision and storage levers (version-aware)

Newer pgvector versions add storage options (e.g. halfvec / binary-ish paths depending on release). Treat them as quality vs RAM knobs:

1. Measure task recall@k on your eval set before and after
2. Document the type in the collection/table version name
3. Do not silently ALTER types under a live RAG path

Always verify against the docs for your installed version — do not cargo-cult blog posts from a different major.

Hybrid queries that matter

-- $1 = query embedding, $2 = tenant, $3 = roles text[]
SELECT
  c.id,
  c.content,
  d.title,
  d.source_uri,
  1 - (c.embedding <=> $1) AS score
FROM chunks c
JOIN docs d ON d.id = c.doc_id
WHERE c.tenant_id = $2
  AND c.acl_roles && $3          -- overlap with user roles
  AND c.updated_at > now() - interval '180 days'
  AND d.deleted_at IS NULL
ORDER BY c.embedding <=> $1
LIMIT 40;

Metadata filters + vector order in one place is pgvector’s superpower. Dedicated vector DBs need filter pushdown too — verify before you migrate.

For keyword + vector hybrid inside Postgres, combine tsvector / ts_rank with vector scores (or RRF in the app). See Hybrid search and rerankers.

-- Sketch: keyword candidate set then vector re-order (cascade)
WITH kw AS (
  SELECT id FROM chunks
  WHERE tenant_id = $2
    AND content_tsv @@ plainto_tsquery('english', $4)
  LIMIT 200
)
SELECT c.id, c.content, 1 - (c.embedding <=> $1) AS score
FROM chunks c
JOIN kw ON kw.id = c.id
ORDER BY c.embedding <=> $1
LIMIT 40;

Filter selectivity — the product-critical footgun

Load-test with realistic ACL selectivity, not only “tenant with millions of open chunks.”

Index choices

-- IVFFlat example
CREATE INDEX chunks_ivf ON chunks
  USING ivfflat (embedding vector_cosine_ops)
  WITH (lists = 100);

-- Session / role knobs (names vary by pgvector version — check docs)
SET hnsw.ef_search = 40;
SET ivfflat.probes = 10;

Tune ef_search / probes / lists for recall vs latency. Always measure recall@k against exact search on a held-out sample:

-- Sketch: compare ANN top-k ids vs exact top-k ids on sample queries
-- recall = |ANN ∩ Exact| / k

HNSW knobs in plain English

Start with documented defaults, then ablate on your eval queries under your filters. Vendor/blog defaults are not your product SLO.

IVFFlat knobs in plain English

IVFFlat is cheaper to build than HNSW at some scales; it is also easier to mis-tune after a bulk load if centroids were trained on a tiny early sample.

Build-time tips

• Create HNSW after a bulk load when practical (faster than updating the graph row-by-row during a huge backfill — check your version’s guidance).
• Prefer CREATE INDEX CONCURRENTLY in production windows when your ops policy allows — still budget time and disk.
• lists for IVFFlat: start with a heuristic, then ablate with recall@k.
• Monitor index size vs shared_buffers / RAM; HNSW wants to be hot.
• Do not create the ANN index on an empty table and forget to rebuild after the first million-row backfill.

flowchart TD
  Bulk[Bulk load chunks] --> Exact[Optional: exact recall baseline]
  Exact --> Build[CREATE INDEX hnsw / ivfflat]
  Build --> Tune[Tune ef_search / probes]
  Tune --> Gate[recall@k gate]
  Gate -->|pass| Serve[Serve RAG]
  Gate -->|fail| Retune[Retune or reindex]

Ingest patterns

# Pseudocode — skip re-embed when content_hash unchanged
row = fetch_chunk(doc_id, idx)
new_hash = sha256(text)
if row and row.content_hash == new_hash and row.embedding_model == MODEL:
    return  # idempotent no-op
emb = embed(text)
upsert_chunk(..., embedding=emb, content_hash=new_hash, embedding_model=MODEL)

• Batch inserts; avoid per-row round-trips.
• Use COPY or multi-row INSERT for backfills.
• Rebuild ANN indexes after huge bulk loads if your version recommends it.
• VACUUM / bloat: deleted vectors and updates still need Postgres hygiene.
• Soft-delete with deleted_at + filter if GDPR needs tombstones before hard delete.
• Cap embed-worker concurrency; unbounded gather → max_connections death.

sequenceDiagram
  participant Worker as Ingest worker
  participant Emb as Embedding API
  participant PG as Postgres
  participant API as RAG API
  Worker->>Worker: parse + chunk + hash
  alt hash unchanged
    Worker-->>Worker: skip
  else changed
    Worker->>Emb: batch embed
    Emb-->>Worker: vectors
    Worker->>PG: upsert chunks + embeddings
  end
  API->>PG: filtered ORDER BY embedding <=> q
  PG-->>API: top-k + join docs
  API->>API: optional rerank + pack prompt

Idempotent ids

Prefer stable chunk primary keys such as uuid derived from doc_id + chunk_index + embedding_model (or content-addressed ids). Re-ingest must upsert, not duplicate. Unstable ids → duplicate neighbors forever and broken citations.

Row Level Security (optional but powerful)

If you already use RLS for multi-tenant tables, you can align chunk access with the same policies — still put explicit tenant_id predicates in app SQL for defense in depth and clearer plans.

-- Shape only — wire to your auth.uid() / session GUC pattern
ALTER TABLE chunks ENABLE ROW LEVEL SECURITY;
-- CREATE POLICY chunks_tenant_isolation ON chunks
--   USING (tenant_id = current_setting('app.tenant_id')::uuid);

Ship rule: RLS is not a substitute for forgetting tenant_id in ANN queries during code review. Reviewers should see the predicate in the helper SQL.

Performance and ops reality

Read replica pattern

flowchart LR
  Ingest[Ingest] --> Primary[(Primary OLTP + writes)]
  API[RAG reads] --> Replica[(Replica with pgvector)]
  Primary --> Replica

Use a replica for heavy ANN if lag is acceptable for your freshness SLO. Embedding writes still hit primary (or a dedicated ingest path). Document the max acceptable replica lag for “new doc searchable” UX.

Separating workloads without leaving Postgres

Before graduating to Pinecone/Qdrant:

1. Cap RAG concurrency (semaphore at the API)
2. Add Redis exact/semantic cache in front (Redis for AI caching)
3. Move ANN reads to a hot replica
4. Partition or partial-index hot doc types
5. Only then split derived ANN out

When to leave Postgres (or split)

Leave the primary as the only ANN store when:

• Billions of vectors or multi-region ANN SLAs
• Embedding write QPS saturates OLTP
• You need specialized cold/hot vector tiers
• p95 ANN latency fights checkout/auth queries
• Compliance requires a harder blast radius than one Postgres cluster

Common pattern:

1. Postgres = system of record (docs, ACL, canonical chunk text)
2. Specialized ANN (Qdrant/Pinecone) as derived index
3. Dual-write or CDC with idempotent chunk ids

Until then, pgvector is often the highest-ROI choice.

flowchart TD
  Stress{ANN hurting OLTP?}
  Stress -->|No| Stay[Stay on pgvector]
  Stress -->|Yes| Split[Postgres SoT + dedicated ANN]
  Split --> Dual[Dual-write / CDC]

Ship rule: write the graduation trigger in the design doc before the incident — e.g. “split when p95 ANN > 80ms and checkout p95 rises >20% during RAG load tests.”

Architecture that survives production

flowchart TB
  subgraph SoT[System of record]
    PG[(Postgres: docs, ACL, chunk text, embeddings)]
    S3[(Object store: raw files)]
  end
  subgraph OptionalDerived[Optional later]
    VDB[(Dedicated vector DB)]
  end
  subgraph Online
    API[RAG API] --> Cache[Redis exact/semantic]
    Cache --> Emb[Embed query]
    Emb --> PG
    PG --> Pack[Join + pack + citations]
    Pack --> LLM[LLM]
  end
  S3 --> PG
  PG -.->|graduate| VDB

When you graduate ANN, keep Postgres as SoT and treat the external vector DB as rebuildable.

How to evaluate retrieval on pgvector (not vibes)

Hold a frozen set of queries with labeled relevant chunk ids.

Ship rule: never ship a new embedder, chunker, or index knob without a before/after on the same eval set. Generation quality alone hides retrieval rot.

Interface contract (staff bar)

Three measurable metrics

1. recall@10 vs exact on sample queries at current ef_search / probes
2. p95 of filtered ANN query under load
3. Primary CPU / buffer hit ratio during peak RAG traffic

Two degrade modes

1. ANN index dropped/invalid → exact search on a capped candidate set or BM25-only with user-visible quality note
2. Postgres failover → serve stale Redis FAQ cache; block writes until primary healthy

Threat note

Missing tenant_id in WHERE turns ANN into a cross-tenant exfiltration tool via the LLM prompt. Catch with SQL review checklists, integration tests, and optional RLS. Prompt injection via retrieved docs is a packing/policy problem — cite spans and do not treat retrieved text as instructions.

Failure modes

• Missing tenant_id in WHERE → cross-tenant leak
• IVFFlat with default probes → silent recall drop
• Mixing cosine ops with unnormalized embeddings
• ANN index on empty/small table then never rebuilding after bulk load
• Storing only vectors without content → cannot cite or rebuild prompts
• Giant HNSW on undersized RAM → latency cliffs / IO storm
• Embedding workers opening unlimited connections → slot exhaustion
• Dimension / opclass mismatch after a “quick” migration
• Soft-deleted docs still retrieved → policy ghosts in the prompt
• Evaluating only final answers → blind to retrieval rot
• Holding DB connections open during slow embed HTTP calls
• Planner skipping ANN index — verify with EXPLAIN

Debugging playbook (first hour)

1. EXPLAIN (ANALYZE, BUFFERS) the ANN query — is the HNSW/IVF index used?
2. Raise ef_search / probes in a session; did recall jump on eval?
3. Compare exact vs ANN id overlap for the failing query
4. Check embedding_model and vector length constraints
5. Look for bloat / dead tuples on chunks
6. Run the failing query with filters relaxed only in a secure staging clone — is it filter or geometry?
7. Inspect chunk text for parse/chrome garbage from the ingest pipeline
8. Confirm app p95 vs DB time — is embed API the real bottleneck?

Production readiness checklist

• Extension version pinned in migrations
• HNSW or IVFFlat created with documented knobs
• Tenant + ACL predicates mandatory in query helpers
• recall@k job vs exact baseline (weekly or CI against ephemeral Postgres)
• Backup restore tested including large chunks
• Connection pool sized for ingest + API
• Graduation criteria written (when to split ANN out)
• Soft-delete / GDPR delete verification job
• EXPLAIN sampled in staging under realistic filters
• Alerts: ANN p95, empty-retrieval rate, dead tuples, connection saturation

Interview prompts

1. IVFFlat vs HNSW in Postgres — when each?
2. How do you measure ANN recall without a dedicated vector DB?
3. Why store content in Postgres even if another ANN serves queries later?
4. What breaks when ANN and checkout share one primary?
5. How do you change embedding dimension without downtime?
6. Why is post-filtering ANN results in the app dangerous under strict ACLs?
7. Walk through EXPLAIN findings that show the ANN index is not used.

Capacity planning sketch

Assume 2M chunks, 1536-dim float32:

• Raw vectors ≈ (2e6 \times 1536 \times 4 \approx 12.3) GB
• HNSW overhead often adds a large fraction more (graph links) — treat 2× raw as a planning buffer until measured
• Payload text may dominate if you store full chunks inline — consider TOAST behavior and whether cold text lives only for joins
• Indexes + WAL + bloat need headroom beyond “raw math”

Size RAM so the hot index is not thrashing disks during p95 queries. Also budget embedding API cost for backfill and ongoing CDC — a multi-million-chunk re-embed is a calendar project.

Sizing worksheet (fill before buying hardware)

Migration: change embedding dimension

-- Shape: new table or new column, never ALTER vector(1536) → vector(3072) in place casually
CREATE TABLE chunks_v4 (LIKE chunks INCLUDING ALL);
-- embed into chunks_v4.embedding vector(3072)
-- dual-read in app; drop old after gate

Keep embedding_model populated. Application query helpers should take an explicit collection/table version.

Dual-write cutover (safe upgrade)

flowchart LR
  V3[chunks serving] --> Shadow[Shadow compare]
  V4[chunks_v4 backfill] --> Shadow
  Shadow -->|pass gates| Flip[Flip read traffic]
  Flip --> Keep[Keep v3 for rollback]

1. Create chunks_v4 with new vector(N) + new HNSW
2. Backfill async; keep serving chunks
3. Shadow-query both; compare recall@k / nDCG on eval set
4. Flip traffic; keep v3 read-only until rollback window ends
5. Drop old only after the window — and after backup

Combining with Postgres full-text

Maintain content_tsv:

ALTER TABLE chunks ADD COLUMN content_tsv tsvector
  GENERATED ALWAYS AS (to_tsvector('english', content)) STORED;
CREATE INDEX chunks_tsv ON chunks USING gin (content_tsv);

Use for hybrid cascades or RRF in the app. Analyzers matter — test SKUs and error codes. pgvector does not replace a serious BM25 engine for keyword-heavy corpora; it coexists.

Connection and pool hygiene

Never run unbounded asyncio gather of ANN queries without a semaphore — you will exhaust max_connections. Embed outside the DB transaction whenever possible: open connection → write vectors → close; do not sit in BEGIN while waiting on OpenAI.

Exact baseline query (for recall@k)

-- Disable index scan in a session when measuring exact neighbors (shape; verify with EXPLAIN)
SET enable_indexscan = off;
SET enable_bitmapscan = off;
SELECT id FROM chunks
WHERE tenant_id = $2
ORDER BY embedding <=> $1
LIMIT 10;
RESET enable_indexscan;
RESET enable_bitmapscan;

Compare id sets to ANN with indexes enabled. Automate in a weekly job — do not rely on one-off notebooks. Cap the exact baseline to a sampled tenant or random subset when (n) is huge; document the sampling bias.

Vacuum and delete reality

Updates to embedding rewrite rows. High-churn corpora need:

• Sensible fillfactor experiments only with measurement
• Autovacuum scale factors tuned for large tables
• Periodic REINDEX INDEX CONCURRENTLY when appropriate for your version/ops policy
• Monitoring n_dead_tup and last autovacuum time

GDPR hard-deletes should be verified with a search for the doc_id after the job — both in docs and chunks, and in any derived external ANN if you split later.

Multi-tenancy patterns on pgvector

Start shared + mandatory filters for MVPs; graduate when compliance or noisy-neighbor requires harder walls. Decision detail: Choosing vector stores.

Worked walkthrough: support policy RAG on pgvector

1. Ingest — parse policy PDFs → chunk → hash → embed → upsert into chunks with tenant_id, acl_roles, embedding_model
2. Index — HNSW on embedding vector_cosine_ops after bulk load
3. Query — embed user question → SQL with tenant + ACL → ORDER BY <=> LIMIT 40
4. Pack — rerank optional → pack 5–10 chunks with citations into the LLM
5. Eval — 50 labeled queries: task recall@10 + ANN vs exact overlap + zero cross-tenant hits
6. Ops — alert on p95, empty rate, dead tuples; document graduation trigger

If step 5 fails, do not “fix” generation temperature first — fix retrieval.

Anti-patterns (pgvector edition)

Interview whiteboard: design a pgvector layer

Be ready to draw and narrate:

1. Tables: docs, chunks (+ optional content_tsv)
2. Indexes: HNSW + tenant/ACL helpers
3. Ingest path with content_hash short-circuit
4. Online path: embed → filtered ANN → pack → LLM
5. Eval: exact vs ANN + task recall + tenant isolation
6. Failure: degrade to BM25/cache; graduation to dedicated ANN

If you skip filters or SoT, the whiteboard fails staff bar even if HNSW knobs are perfect.

Glossary

Application helper pattern

def search_chunks(conn, qvec, tenant_id: str, roles: list[str], k: int = 40):
    # tenant_id from session — never from client body alone
    sql = """
    SELECT c.id, c.content, d.title, d.source_uri,
           1 - (c.embedding <=> %s) AS score
    FROM chunks c
    JOIN docs d ON d.id = c.doc_id
    WHERE c.tenant_id = %s
      AND c.acl_roles && %s::text[]
      AND d.deleted_at IS NULL
    ORDER BY c.embedding <=> %s
    LIMIT %s
    """
    return conn.execute(sql, (qvec, tenant_id, roles, qvec, k)).fetchall()

Centralize this helper so reviewers only audit one place for missing filters. Add integration tests that insert tenant A/B fixtures and assert isolation on every PR that touches retrieval.

Monitoring queries worth saving

-- Index sizes
SELECT relname, pg_size_pretty(pg_relation_size(oid))
FROM pg_class
WHERE relname LIKE 'chunks%';

-- Bloat / dead tuples (approx)
SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'chunks';

-- Hot queries (enable pg_stat_statements)
SELECT substring(query, 1, 80), calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
WHERE query ILIKE '%embedding%'
ORDER BY total_exec_time DESC
LIMIT 20;

Pair with app-level p95 ANN latency. DB-only metrics miss embed time. Trace one request: embed ms + SQL ms + rerank ms + LLM ms.

Observability for the pgvector data plane

End-to-end smoke test script (shape)

1. Insert tenant A and B chunks with known text.
2. Query as A — never see B.
3. Exact top-10 vs HNSW top-10 overlap on 10 queries.
4. Delete doc — confirm chunks gone / filtered.
5. Change one chunk hash — confirm re-embed path updates vector.
6. EXPLAIN confirms index use.
7. Soft-delete a doc — confirm it disappears from RAG hits.
8. Flip ef_search up/down — confirm latency/recall move as expected on eval.

Automate in CI against ephemeral Postgres when feasible.

FAQ (pgvector)

Does ORDER BY embedding <=> $1 LIMIT k always use HNSW?
Check EXPLAIN. Missing index, wrong opclass, or planner choices can surprise you.

Can I use RLS instead of tenant_id in SQL?
Use both. RLS helps; explicit predicates keep plans and reviews clearer.

Will pgvector replace Elastic for keyword search?
No — use tsvector or an external search engine for serious BM25. pgvector shines at vectors + SQL filters together.

Is HNSW always better than IVFFlat?
Often for interactive recall/latency, not always for build time or RAM. Measure on your (n), filters, and hardware.

Can I put a separate HNSW per tenant?
Partial indexes or partitions can help hot tenants; sprawl has a cost. Measure before proliferating.

What if replica lag is 30s?
New docs may be invisible to RAG on the replica. Either read-your-writes on primary for that tenant, accept lag in UX copy, or shorten the path to primary for freshness-critical queries.

Do I need a dedicated vector DB eventually?
Maybe. Start with graduation triggers, not vibes. Many products stay on pgvector far longer than Twitter threads suggest.

Deep dive: keeping OLTP healthy

Symptoms ANN is hurting the primary:

• Checkout/auth p95 rises when RAG traffic spikes
• chunks index larger than RAM; disks busy
• Autovacuum cannot keep up with embed worker updates
• Connection saturation from parallel RAG fan-out
• pg_stat_activity shows piles of ORDER BY embedding <=> …

Responses in order of cost:

1. Cap RAG concurrency; add Redis cache
2. Raise ef_search carefully — sometimes lower load with slightly lower recall is required under incident
3. Move ANN reads to a replica
4. Split derived ANN to Qdrant/Pinecone; keep Postgres as SoT

Write the graduation trigger in the design doc before the incident.

Deep dive: filtered search under sparse ACLs

When only 0.1% of chunks match a user’s roles, a naive “ANN then filter” mental model fails. In SQL you want the engine to consider predicates with the distance order. Practical tactics:

• Keep tenant_id + ACL predicates in the same query as ORDER BY embedding <=> …
• Retrieve wider (LIMIT 80) then rerank/pack narrower
• Raise ef_search under sparse ACL load tests
• Consider partial indexes for common role/doc_type surfaces
• Add an isolation test that uses a rare role — not only the admin role that sees everything

Deep dive: embedding model upgrades without downtime

1. New table/version with new dim + model id
2. Dual-write new docs; backfill old
3. Shadow traffic + eval gates
4. Flip reads; keep rollback window
5. Drop old after backup + quiet period

Never ALTER the vector dimension in place on a live hot table as your only plan.

Consistency models you will actually hit

Pick one and document it in the API (“docs searchable within N seconds”).

Common interview traps

• Claiming pgvector “doesn’t do ANN” (it does — HNSW/IVFFlat)
• Ignoring filters when praising cosine similarity
• Treating Redis as a substitute for durable chunk storage
• Sizing RAM from raw float32 only (forgetting HNSW overhead)
• Saying “we’ll just raise ef_search” without a recall@k graph

Putting what / why / how together

If you can only recite operators, you know syntax. If you can defend filters, recall@k, and OLTP isolation, you know the product.

Micro-project

1. CREATE EXTENSION vector in local Postgres.
2. Create chunks with HNSW + tenant filter.
3. Insert 100 fake chunks across two tenants; run filtered similarity query.
4. Compare exact vs HNSW top-10 overlap (recall@10).
5. Deliberately omit tenant_id once in a throwaway script — observe the leak — then fix the helper and add a test.
6. EXPLAIN (ANALYZE, BUFFERS) and confirm the ANN index is used.

Related

Vector databases · Choosing stores · Guided RAG. Redis if you need a hot semantic/exact cache in front: Redis for AI caching. Chunking quality still dominates: Chunking and metadata. Hybrid keyword+vector: Hybrid search and rerankers.