Vector Search hard
Documents and their ACLs live in PostgreSQL. In the same request, the application writes to Postgres and then to the vector index. After a deploy bug, some index writes failed silently, and documents deleted for a privacy request kept appearing in RAG answers for days. Which design prevents this class of failure?
- A Make the vector database the system of record for documents and ACLs, eliminating the second write entirely.
- B Wrap the Postgres write and the vector upsert in a two-phase commit so both stores commit or roll back together.
- C Drive index updates from Postgres through CDC or an outbox with idempotent deletes, and reconcile periodically.
- D Give every vector a short TTL so stale or deleted entries expire and get re-indexed from the source if still live.
Answer: C. Drive index updates from Postgres through CDC or an outbox with idempotent deletes, and reconcile periodically.
The vector index is a **derived view** of the source. Updates should therefore flow from the source's change log and be retried idempotently until applied, with periodic **reconciliation** to catch drift. Checking deletion and ACLs against Postgres at query time closes the remaining lag window, so you accept seconds of index lag in exchange for guaranteed convergence. Making the vector store the system of record (A) gives up transactions, constraints, and mature backup tooling. It fits only small, append-mostly corpora with no relational data.
One system design question a day, with spaced repetition so the ones you miss come back.
Get the appRelated questions
- You are building an IVF index on 20 million vectors. A teammate asks why the build needs a 'training' step before any vectors can be added. What is the reason?
- An HNSW index serves a marketplace where about 30% of listings are updated or removed each week. Each update is applied as a delete plus an insert, and deletes mark tombstones. Over a month, recall@10 drifts from 97% to 89% and p99 latency rises at the same ef_search. What is happening, and what should you do?
- Users delete documents in your primary database, but the deleted documents keep appearing in vector search results for hours. The vector index is rebuilt nightly. What is the best fix?
- Your embedding model outputs unnormalized vectors whose magnitudes vary widely. A teammate wants to switch the index from cosine similarity to dot product because it is cheaper to compute. What is the consequence?
- You are setting an SLO for a new ANN-backed recommendation search over 50 million vectors. A product manager asks for 100% recall@10 with single-digit millisecond latency. What is a realistic response?
- A B2B SaaS app serves 500 customers, and every query searches only one customer's documents. One large customer runs heavy batch queries that slow down search for everyone. Which sharding approach fits best?