What is a vector database?

What is a vector database and how does it work?

A vector database stores rows of {id, vector, metadata} and answers one question fast: given a query vector, which stored vectors point in the most similar direction. You produce the query vector with an embedding model, send it to the database, and get back the top-k rows by cosine similarity or dot product, usually alongside a metadata filter such as tenant, document type or date. Every retrieval-augmented pipeline that runs on more than a demo's worth of data sits on top of this call.

The reason a dedicated store exists is that the comparison is not free. Cosine similarity between two 1536-dimensional vectors is 1536 multiplies and an add, and doing that against every vector in a corpus of ten million is minutes per request, not milliseconds. An index like HNSW (Hierarchical Navigable Small World, Malkov and Yashunin 2016) or IVF (inverted file, from FAISS) reduces the scan to a logarithmic walk over a graph, or to a lookup into a small number of clusters, at the cost of returning an approximate answer rather than the exact top-k.

The API surface is similar across products. pgvector adds a vector column type and a <=> distance operator to Postgres; Pinecone, Weaviate, Qdrant and Milvus expose a REST or gRPC endpoint that takes a vector and returns matches. Under the hood they are all doing approximate nearest-neighbour search over an on-disk or in-memory index, plus a metadata filter, plus some bookkeeping for updates.

Approximate vs exact search: what is the trade?

Exact search, sometimes called flat or brute-force, compares the query vector to every stored vector and returns the true top-k. It needs no index, is easy to reason about, and is fine up to a few hundred thousand vectors on modern hardware. Past that, latency grows linearly with corpus size and the request path is not the place for it. FAISS ships an IndexFlat that does exactly this, so you have a baseline to score approximate indexes against.

Approximate indexes trade a small amount of recall for a large speedup. HNSW builds a multi-layer graph where each node points to its nearest neighbours; a query walks the graph from the top layer down, greedily following the closest neighbour, and typically visits a few hundred nodes to find the top-k out of millions. IVF partitions the vectors into clusters, and a query only searches the few clusters closest to it. Both come with knobs (ef_search for HNSW, nprobe for IVF) that trade recall against latency at query time.

Recall is the number that matters and the one most demos hide. If your index returns 95 percent recall at 20 ms versus 100 percent recall at 400 ms, the 5 percent you miss are real documents your RAG pipeline will never see. Measure recall on your own data against a flat baseline before you settle on index parameters, and re-measure after any change to the embedding model, the chunker, or the corpus size.

Do you need a dedicated database, or is Postgres enough?

For a lot of workloads Postgres with pgvector is the right answer. You already have the operational story (backups, replicas, migrations, transactions), your metadata lives in the same row as the vector, and joins are free. pgvector supports HNSW and IVFFlat indexes, cosine, L2 and inner-product distance, and enough throughput for corpora into the tens of millions of vectors on a well-sized instance. If your corpus is under that and your team already runs Postgres, adding a new database service to hold vectors is a cost with no matching benefit.

Dedicated vector databases (Pinecone, Weaviate, Qdrant, Milvus) start to earn their keep at larger scale or when the access pattern diverges from a general-purpose SQL store: hundreds of millions of vectors, sub-100 ms p99 at high QPS, hybrid dense-plus-sparse search, or per-tenant namespaces with their own indexes. They also handle sharding and replication of the index itself, which is work you would otherwise write yourself on top of Postgres.

The honest question is throughput and operational surface, not features. Start with pgvector, benchmark against a flat baseline on your data, and move only when a specific number (p99 latency, index build time, recall at your target QPS) forces the move.

Where vector databases fail

The first failure is treating similarity as relevance. Two chunks with high cosine similarity to a query can both be off-topic if the query is ambiguous or if the corpus is homogeneous. Hybrid search that combines the dense vector score with a keyword score such as BM25 rescues a lot of these cases, which is why Weaviate, Qdrant and pgvector all ship a hybrid mode.

The second is index drift after writes. HNSW graphs degrade as you insert and delete, because the graph was built for the shape of the data at index time, and deletions leave tombstoned nodes the search still walks through. Recall on a stale index can fall by several points before anyone notices, so schedule rebuilds against your write rate rather than reacting to symptoms.

The third is coupling to an embedding model you cannot swap. Every vector in the store was produced by one model at one version. If you upgrade the embedding model, the old vectors are no longer comparable to new query vectors, and you have to re-embed the whole corpus before you can serve traffic on the new model. Budget for that cost from the start, and keep the embedding model, the chunker version and the index parameters in a config file rather than scattered across the code.

-- Enable the extension and create a table with a 1536-dim vector column.
CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE passages (
    id        bigserial PRIMARY KEY,
    doc_id    text        NOT NULL,
    chunk     text        NOT NULL,
    embedding vector(1536) NOT NULL
);

-- Build an HNSW index for cosine distance. m and ef_construction trade
-- build time and memory for query recall. Rebuild after large write bursts.
CREATE INDEX passages_embedding_hnsw
    ON passages
    USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

-- Insert a row: the vector comes from your embedding provider.
INSERT INTO passages (doc_id, chunk, embedding)
VALUES ('rfc-9110',
        'The GET method requests transfer of a target resource.',
        '[0.0123, -0.0451, ... 1534 more floats ...]');

-- Query: the 5 nearest passages to a query vector, filtered by doc.
-- <=> is cosine distance; ORDER BY uses the HNSW index.
SET hnsw.ef_search = 40;   -- raise for more recall, lower for faster queries

SELECT id, doc_id, chunk, 1 - (embedding <=> $1) AS cosine_similarity
FROM   passages
WHERE  doc_id LIKE 'rfc-%'
ORDER  BY embedding <=> $1
LIMIT  5;