Why it matters
Once a corpus passes a few tens of thousands of chunks, comparing the query against every vector on each request stops being viable. A vector database exists to make that comparison sublinear, and to carry the surrounding work that a bare index does not: storing the text and metadata, applying permission filters, handling upserts and deletes, replicating for availability, and returning results with scores you can rank.
The engineering problem is that all of those features interact. An index that is fast without filters can collapse to near-zero recall once a restrictive filter is applied. An index that is accurate on a static corpus can degrade after a month of updates. Choosing a database is mostly choosing which of these failure modes you can operate.
Mental model
A vector database is a library with two catalogues: one sorted by meaning, one sorted by attributes. The meaning catalogue is approximate — a librarian who knows the shelves well enough to point you at the right aisle without walking every row. The attribute catalogue is exact and used to exclude books you are not allowed to see.
The analogy breaks at the shelf. In a real library the books stay put, whereas a vector index is rebuilt continuously: inserts, updates and deletes leave gaps, background merges reshape the graph, and yesterday's recall is not today's. Treat the index as a living structure with maintenance cost, not a static file.
How it works
The interactive figure loads with JavaScript. The text version below describes the same frame.
Figure. Ten synthetic chunks in one hand-placed 2-D layout. Writes are already embedded; the query is embedded on request; a tenant filter restricts eligible chunks; an approximate index returns five.
What to notice. With the filter off, the nearest chunk overall is legacy refund policy, which belongs to another tenant; filtering removes it and three other chunks, so the eligible answer set changes. At the last step the approximate index returns four of the five exact neighbours — mid-cycle proration is missing — and adds trial conversion, the sixth-ranked eligible chunk. The example's recall is four of five; that is the result of this configuration, not a property of the index. This filter still keeps six chunks: a more selective filter can starve the walk and return fewer than k.
Text version
Ten synthetic chunks plotted in two dimensions. The default final frame shows the query, refund window for annual plan, the tenant filter active, four excluded chunks dimmed, and the five eligible exact neighbours refund window, annual billing, cancel anytime, mid-cycle proration and card on file joined to the query in teal. The rank panel marks the approximate result: four of the five are returned, mid-cycle proration is marked missed, and trial conversion is marked as the extra result returned in its place. Four step controls walk through insert, query, filter and top-k.
- Define the schema. Vector dimension, distance metric, the payload fields you will filter on, and the tenant key. These are hard to change later; the metric in particular is baked into the index.
- Write. Each record is a vector plus metadata plus usually the chunk text. Writes go to a durable log or segment first, and are searchable quickly but not instantly — most systems expose a refresh interval.
- Index. For HNSW, the store builds a multi-layer proximity graph: each vector keeps links to a bounded number of neighbours, with
Mcontrolling graph degree andef_constructioncontrolling build thoroughness. For IVF-family indexes, vectors are clustered and only the nearest few cells are probed at query time. - Query. The search walks the graph or probes the cells, exploring
ef_searchornprobecandidates. Higher values mean better recall and more latency; this is the main runtime knob. - Filter. Metadata constraints are applied either before the search (restricting the graph traversal) or after (dropping results). Pre-filtering is correct but can starve the search when the filter is narrow; post-filtering is fast but can return fewer than results.
- Merge and rank. Results from shards or segments are merged by score; in a hybrid setup, lexical and dense lists are fused by rank rather than raw score.
- Maintain. Compaction rewrites segments, deletes are finalised, and index statistics drift. This is where recall quietly changes over time.
- Core query
- k nearest vectors, with filters
- Typical indexes
- HNSW, IVF, product quantisation
- Main knobs
- M, ef_construction, ef_search
- The real cost
- Memory, plus rebuilds after churn
- Small-corpus shortcut
- Postgres with pgvector
A minimal schema
CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE chunks (
id bigserial PRIMARY KEY,
document_id text NOT NULL,
tenant_id text NOT NULL,
content text NOT NULL,
embedding vector(768) NOT NULL,
updated_at timestamptz NOT NULL DEFAULT now()
);
-- HNSW index for cosine distance; operators: <=> cosine, <-> L2, <#> inner product
CREATE INDEX chunks_embedding_idx ON chunks
USING hnsw (embedding vector_cosine_ops);
-- Query: nearest chunks for one tenant, filtered at the database, not the client
SELECT id, content, 1 - (embedding <=> $1) AS similarity
FROM chunks
WHERE tenant_id = $2
ORDER BY embedding <=> $1
LIMIT 20;This is enough for a large fraction of products. A dedicated vector database earns its place when you need distributed sharding, very high write rates, or index types a relational engine does not offer.
Text version
Documents are embedded and inserted into an approximate index; queries are embedded, metadata filters restrict candidates, and top-k results return.
Trade-offs
| Option | Good for | Cost | Fails when |
|---|---|---|---|
| Postgres + pgvector | Existing relational data, joins, filters, one system to operate | Index build time and memory at large scale | You need billions of vectors or very high QPS |
| Dedicated vector database | Scale, sharding, managed ANN tuning | Another service, another backup and auth story | Your data already lives in Postgres and fits there |
| Search engine with vectors | Hybrid lexical plus dense in one query | Dense-only recall can lag specialised indexes | You need fine-grained ANN tuning per collection |
| In-process library (FAISS and similar) | Batch experiments, offline evaluation, embedded search | You own persistence, updates and serving | The corpus changes continuously or needs HA |
| Brute-force scan | Small corpora, exact ground truth for evals | Linear cost per query | QPS or corpus size grows |
Good for
Existing relational data, joins, filters, one system to operate
Cost
Index build time and memory at large scale
Fails when
You need billions of vectors or very high QPS
- You need billions of vectors or very high QPS
- Your data already lives in Postgres and fits there
- You need fine-grained ANN tuning per collection
- The corpus changes continuously or needs HA
- QPS or corpus size grows
Figure. Each choice with what it buys, what it costs, and the case where it loses.
What to notice. The fourth column is the decision: if the losing case describes your situation, the choice is wrong.
Text version
Four trade-offs: Postgres + pgvector is good for Existing relational data, joins, filters, one system to operate, costs Index build time and memory at large scale, and fails when You need billions of vectors or very high QPS. Dedicated vector database is good for Scale, sharding, managed ANN tuning, costs Another service, another backup and auth story, and fails when Your data already lives in Postgres and fits there. Search engine with vectors is good for Hybrid lexical plus dense in one query, costs Dense-only recall can lag specialised indexes, and fails when You need fine-grained ANN tuning per collection. In-process library (FAISS and similar) is good for Batch experiments, offline evaluation, embedded search, costs You own persistence, updates and serving, and fails when The corpus changes continuously or needs HA. Brute-force scan is good for Small corpora, exact ground truth for evals, costs Linear cost per query, and fails when QPS or corpus size grows
Failure modes
- Filtered search starves. A tenant filter selects 0.1% of the corpus, the graph walk cannot reach enough matching nodes, and the query returns three results instead of twenty. Debug: run the same query without the filter and compare the result count and IDs; if unfiltered works, you need partitioned indexes or a filter-aware index.
- Churn degrades recall. After weeks of updates and deletes, recall drifts down without any code change. Debug: keep a frozen labelled query set, measure recall@10 weekly, and compare against a rebuilt index from the same data.
- Refresh lag. A document is ingested and immediately queried, and the answer cites the old version because the write was not yet searchable. Debug: timestamp the write and the first query that should have seen it; expose the refresh interval to callers instead of assuming read-after-write.
- Cross-tenant leakage. Filters live in application code and one code path forgets them. Debug: grep every query constructor for the tenant predicate, then add a test that asserts an empty result for a foreign tenant.
In production
Memory is the binding constraint for graph indexes, since the vectors and the graph both want to be resident. Size for vectors plus overhead, not just the raw float array, and decide early whether quantisation is acceptable — it changes recall and must be evaluated, not assumed.
Operationally, the things that page you are index build time after a bulk import, replication lag between primary and replicas, and backup/restore that does not include the index (rebuilding from vectors is fine if you planned for it). Monitor recall on a labelled set, p95 and p99 query latency at the chosen ef_search, result-count shortfalls against k, and index size growth per week.
Common misconceptions
People think: A vector database understands your data.
Actually: It stores vectors and finds neighbours. All semantics come from the embedding model; swapping the model invalidates the index.
People think: Approximate search is close enough to exact.
Actually: ANN recall is a parameter, not a property. With an aggressive `ef_search` or a narrow filter it can miss most true neighbours while still returning confident-looking results.
Interview angle
A common system-design prompt is "50 million chunks, per-user permissions, sub-200ms p95". The signal interviewers look for is that you separate storage, index, and filter strategy, and that you know filtered ANN is the hard part rather than the distance function. Naming a concrete fallback — pre-filter with partitioned indexes, post-filter with an oversample factor — and the metric you would watch is stronger than naming a product. See RAG interview questions and ANN indexes.
Sources
- Malkov & Yashunin, Efficient and robust approximate nearest neighbor search using Hierarchical Navigable Small World graphs — the HNSW construction and search parameters referenced above.
- Johnson, Douze & Jégou, Billion-scale similarity search with GPUs — the FAISS design and IVF/PQ trade-offs.
- pgvector, README and operator reference — index types, distance operators and filtering behaviour in Postgres.
- Qdrant, Filtrable HNSW — why filtering during graph traversal differs from filtering results afterwards.