Skip to content
AI Engineer Guide.

concept · concepts · intermediate · concept 11 of 57

Vector databases

0 of 57 complete

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

Write, query, filter, top-kInteractive

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.

  1. 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.
  2. 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.
  3. Index. For HNSW, the store builds a multi-layer proximity graph: each vector keeps links to a bounded number of neighbours, with M controlling graph degree and ef_construction controlling build thoroughness. For IVF-family indexes, vectors are clustered and only the nearest few cells are probed at query time.
  4. Query. The search walks the graph or probes the cells, exploring ef_search or nprobe candidates. Higher values mean better recall and more latency; this is the main runtime knob.
  5. 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 kk results.
  6. 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.
  7. 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.

pre-filterInsertEmbedHNSW / IVFQueryTop-kMetadata filter
Step 1/4 · Embed and insert documents
Figure. Approximate indexes trade exactness for speed; filters decide what is even eligible.
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

OptionGood forCostFails when
Postgres + pgvectorExisting relational data, joins, filters, one system to operateIndex build time and memory at large scaleYou need billions of vectors or very high QPS
Dedicated vector databaseScale, sharding, managed ANN tuningAnother service, another backup and auth storyYour data already lives in Postgres and fits there
Search engine with vectorsHybrid lexical plus dense in one queryDense-only recall can lag specialised indexesYou need fine-grained ANN tuning per collection
In-process library (FAISS and similar)Batch experiments, offline evaluation, embedded searchYou own persistence, updates and servingThe corpus changes continuously or needs HA
Brute-force scanSmall corpora, exact ground truth for evalsLinear cost per queryQPS or corpus size grows
Trade-off mapInteractive

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

  1. You need billions of vectors or very high QPS
  2. Your data already lives in Postgres and fits there
  3. You need fine-grained ANN tuning per collection
  4. The corpus changes continuously or needs HA
  5. 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