A vector database stores embeddings, the lists of numbers that represent what a text means, and finds the ones closest to a query quickly. You give it the embedding of a question, and it returns the stored passages whose meaning is nearest. It is the “retrieve” part of most RAG systems.
It can be a dedicated product such as Qdrant, Milvus, Weaviate, Chroma or Pinecone, or an extension to a database you already run, such as pgvector for PostgreSQL.
What it looks like
With pgvector, it is ordinary SQL. A table with a vector column:
CREATE EXTENSION vector;
CREATE TABLE passages (
id bigserial PRIMARY KEY,
content text NOT NULL,
embedding vector(768) NOT NULL
);768 is the length embeddinggemma returns. It must match your embedding model exactly.
Find the five passages closest in meaning to a question, where $1 is the question’s embedding:
SELECT content
FROM passages
ORDER BY embedding <=> $1
LIMIT 5;<=> is cosine distance: smaller means closer in meaning. pgvector also has <-> for straight-line (L2) distance and <#> for the negative inner product.
How does a vector database work?
Finding the closest vectors exactly means comparing the question against every stored vector. That is fine for thousands, slow for many millions. So vector databases build an approximate nearest neighbor (ANN) index, which checks only a small, well-chosen part of the data and is almost always right.
The most common index is HNSW, a graph that links each vector to its neighbors, so a search can hop toward the closest ones instead of scanning everything. In pgvector:
CREATE INDEX ON passages USING hnsw (embedding vector_cosine_ops);The trade is speed for recall: an approximate search can occasionally miss a vector an exact scan would have found. Every engine lets you tune how hard it looks.
Beyond the search itself, a vector database handles the parts a plain list of vectors doesn’t: storing the text and metadata with each vector, filtering (“only documents from this team”), updates and deletes, and persistence.
Do I need a vector database?
Often not.
- A few thousand passages? Keep the vectors in memory and compare them all, like the 50-line example in What is RAG?. It is fast enough and has nothing to operate.
- Already running PostgreSQL? Add pgvector. Your vectors live next to the data they describe, in the same backups, with the same access rules and transactions.
- Very large collections, heavy query load, or many tenants? That is where a dedicated vector database earns its keep.
The question is less “which vector database” than “how much search infrastructure do I want to run”.
Which one should I use?
| Option | What it is | Pick it if |
|---|---|---|
| pgvector | An extension for PostgreSQL | You already run Postgres, or want vectors next to your other data |
| Chroma | An open-source embedding database | You want something simple for prototypes and small apps |
| Qdrant, Milvus, Weaviate | Open-source vector databases you can host or rent | You need scale, filtering and dedicated tuning |
| Pinecone | A managed vector database service | You want no servers to run |
| SQLite or Postgres full-text search plus a cosine loop | No vector database; rank keyword candidates by vector in your own code | One workspace or one tenant, where the collection fits in memory for the rerank |
Check each project’s current docs for licensing, hosting options and limits before you commit. They move fast.
What we ran, from one SQLite file to a cluster per tenant
Two ends of the same job, both from our own work:
- One workspace: no vector database at all. Our local code index kept chunks and vectors in SQLite, used FTS5 for the keyword pass, and ranked the top 200 keyword hits by cosine in a Go loop. 3,191 lines including tests, no server, no index type to pick. For one person’s documents or one repository, that is enough, and the vector database would have been the biggest moving part in the system.
- Many tenants: a vector-search cluster each. For Bob, the hosted document search, we wrote a Kubernetes operator that provisioned one Vald cluster per tenant from a single custom resource, reconciled like everything else in the cluster. The lesson that paid for itself: validation at the API boundary made the fields that shape an index, dimension first, immutable. An in-place edit of those would have quietly corrupted a live index; the operator refused the edit before any reconcile loop could run it.
The gap between the two is where pgvector and the hosted services live. Start at the SQLite end; move when a tenant boundary or a scale number forces you.
The traps
The dimension trap: the vector length and the distance measure are fixed when you create the index, and they belong to one embedding model. Switch models and every vector must be computed again, usually into a new index. Changing them in place corrupts search silently: queries still return results, just wrong ones.
We ran per-tenant vector search on Kubernetes, one cluster per tenant and embedding length. The settings that could silently ruin a live index (vector length, distance measure, object type) were made impossible to change at the API level, so nobody could edit them by accident. Switching a model meant building a second cluster and moving over.
- Filtering after search. If you search first and filter by permission afterwards, the top results may all be filtered out, or worse, the filter gets forgotten. Filter as part of the query, and never let a user’s question reach documents they can’t read.
- Deletes and updates. Changing a document means replacing all of its passages’ vectors. Leftovers keep showing up in results long after the source is gone.
- Memory. Graph indexes like HNSW are fast because they live in memory. Large collections need planning for RAM, not just disk.
- Store the model name. Keep which embedding model produced each vector. Months later, it is the only way to know what needs re-embedding.
Why does my filtered search return too few results?
A common surprise. You ask for the 10 closest passages from one team’s documents:
SELECT content
FROM passages
WHERE team_id = 42
ORDER BY embedding <=> $1
LIMIT 10;and get back 3. With an approximate index, the database finds a fixed number of candidates first and filters afterwards. pgvector’s docs give the numbers: HNSW looks at 40 candidates by default (hnsw.ef_search), so if the filter matches 10% of rows, you get about 4 results on average.
Since pgvector 0.8.0, iterative index scans fix this by scanning further until enough rows match:
SET hnsw.iterative_scan = strict_order;relaxed_order is faster and finds more, but results can come back slightly out of order. If you filter on a few fixed values, such as one index per tenant, a partial index on each value is another option.
Related guides
- What are embeddings?: what the database stores
- What is RAG?: what the database is usually for
- What is MCP?: give an agent a search tool instead of a fixed index