Vector Search medium
Product records live in PostgreSQL. The application writes to Postgres and then to the vector index in the same request handler. Occasionally the second write fails, and search returns deleted or stale products. What is the most robust design?
- A Make the vector database the system of record and have Postgres sync from it, so writes only ever land in one place.
- B Wrap both writes in a distributed two-phase commit so the Postgres row and the vector always commit atomically together.
- C Treat the index as derived: stream changes via an outbox or CDC and apply them with an idempotent, retrying indexer.
- D Retry the vector write in the request handler up to three times and log failures, which covers transient errors in practice.
Answer: C. Treat the index as derived: stream changes via an outbox or CDC and apply them with an idempotent, retrying indexer.
A vector index is a **derived index**, not the source of truth. An **outbox or CDC pipeline** guarantees that every committed change eventually reaches the index, at the cost of a short staleness window. Two-phase commit is tempting, but most vector stores do not participate in XA transactions, and 2PC adds latency and blocking failure modes. 2PC only fits when both stores support it and strict atomicity is truly required.
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?