Vector databases

01Embeddings: meaning as coordinates

An embedding model turns a piece of content into a vector, for example 1,024 numbers. Think of a huge map where similar meanings are close together: “dog” is near “puppy”, far from “invoice”. The model is trained so that distance on this map reflects similarity. You embed your documents once and store the vectors; at query time you embed the question with the same model and look for the nearest neighbours. Similarity is measured with cosine similarity (angle between vectors), dot product or Euclidean (L2) distance. Most text models produce normalised vectors, so cosine and dot product rank the same way.

• Dimension: how many numbers each vector has (for example 384, 768, 1024, 1536, 3072).
• Always use the same model for documents and queries; vectors from different models are not comparable.
• Changing the model means re-embedding everything, so record the model name and version with each vector.

02Why exact search does not scale, and how ANN helps

Exact search compares the query with every stored vector. For 10,000 vectors that is fine; for 50 million with 1,024 dimensions it is far too slow. Approximate nearest neighbour (ANN) indexes trade a little accuracy for huge speed. HNSW (Hierarchical Navigable Small World) builds a layered graph: upper layers are sparse highways, lower layers are dense local streets. A search starts at the top, greedily moves toward the query, and descends. IVF (inverted file) clusters vectors and only searches the nearest clusters. Recall measures quality: the share of true nearest neighbours the index returns. Good settings reach 95–99% recall in a few milliseconds.

• HNSW: best recall/speed trade-off, more memory, slower to build.
• IVF: less memory and faster builds, needs training on data and tuning of probes.
• Quantization: storing vectors in fewer bits (int8, binary) to cut memory, with rescoring for accuracy.

03Starting with pgvector in PostgreSQL

If you already run PostgreSQL, pgvector is often the simplest start: vectors live next to your rows, you get transactions, joins, backups and access control for free, and filters are plain SQL. The extension adds the vector type (and halfvec for 16-bit storage), distance operators (<-> for L2, <=> for cosine distance, <#> for negative inner product) and HNSW and IVFFlat indexes. Use the operator class that matches your distance; an index built with vector_cosine_ops only helps queries that order by <=>. Raise hnsw.ef_search for better recall at some latency cost.

1CREATE EXTENSION IF NOT EXISTS vector;
2 
3CREATE TABLE doc_chunks (
4  id         bigserial PRIMARY KEY,
5  doc_id     bigint NOT NULL,
6  tenant_id  int    NOT NULL,
7  content    text   NOT NULL,
8  embedding  vector(1024) NOT NULL
9);
10 
11CREATE INDEX ON doc_chunks USING hnsw (embedding vector_cosine_ops)
12  WITH (m = 16, ef_construction = 64);
13 
14SET hnsw.ef_search = 100;
15SELECT id, content, 1 - (embedding <=> $1) AS similarity
16FROM doc_chunks WHERE tenant_id = 7
17ORDER BY embedding <=> $1 LIMIT 10;

04Dedicated vector databases

Dedicated engines such as Qdrant, Weaviate, Milvus and Pinecone are built around vector search. They offer fast filtered search, quantization, sharding across nodes, multi-tenancy features, sparse vectors for hybrid search and managed cloud options. You store points made of an ID, one or more vectors and a payload (metadata) that you filter on. The trade-off is another system to run, secure and keep in sync with your primary database. A practical rule: below a few million vectors with moderate traffic, pgvector or your existing search engine is usually enough; with tens of millions of vectors, heavy filtering or strict latency targets, a dedicated engine earns its place.

1# Qdrant: create a collection and search with a filter (HTTP API)
2curl -X PUT http://localhost:6333/collections/docs -H 'Content-Type: application/json' \
3  -d '{ "vectors": { "size": 1024, "distance": "Cosine" } }'
4 
5curl -X POST http://localhost:6333/collections/docs/points/query \
6  -H 'Content-Type: application/json' -d '{
7    "query": [0.01, 0.12, 0.03],
8    "filter": { "must": [ { "key": "tenant_id", "match": { "value": 7 } } ] },
9    "limit": 10, "with_payload": true
10  }'

05Hybrid search and reranking

Vectors are great at meaning but weak at exact tokens: product codes, names, error messages. Keyword search (BM25) is the opposite. Hybrid search runs both and merges the lists, most often with reciprocal rank fusion (RRF), where each document scores 1 / (k + rank) in each list and the sums decide the final order. RRF needs no score normalisation, which is why it is popular. After merging, a cross-encoder reranker can re-score the top 50–100 candidates by reading query and document together; it is slower but much more accurate, so it runs only on a short list.

1-- Hybrid in PostgreSQL: full-text + vector, merged with RRF (k = 60)
2WITH kw AS (
3  SELECT id, row_number() OVER (ORDER BY ts_rank(tsv, q) DESC) AS r
4  FROM doc_chunks, websearch_to_tsquery('english', $2) q
5  WHERE tsv @@ q ORDER BY r LIMIT 50
6), vec AS (
7  SELECT id, row_number() OVER (ORDER BY embedding <=> $1) AS r
8  FROM doc_chunks ORDER BY embedding <=> $1 LIMIT 50
9)
10SELECT id, sum(1.0 / (60 + r)) AS score
11FROM (SELECT * FROM kw UNION ALL SELECT * FROM vec) s
12GROUP BY id ORDER BY score DESC LIMIT 10;

06Tuning HNSW: m, ef_construction, ef_search

m is the number of links per node. Higher m improves recall on high-dimensional data but increases memory and build time; 16 is a common default, 32–64 for hard datasets. ef_construction is the size of the candidate list while building; higher means a better graph and slower inserts (64–200 is typical). ef_search is the candidate list at query time and the main runtime knob: higher means better recall and more latency, and it must be at least as large as the LIMIT. Tune empirically: take 1,000 sample queries, compute exact neighbours with a sequential scan, then sweep ef_search and plot recall at 10 against p95 latency. Building HNSW is memory hungry; in PostgreSQL raise maintenance_work_mem and max_parallel_maintenance_workers for the build.

1-- Build faster with more memory and workers (PostgreSQL)
2SET maintenance_work_mem = '4GB';
3SET max_parallel_maintenance_workers = 7;
4CREATE INDEX CONCURRENTLY idx_chunks_emb
5  ON doc_chunks USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 128);
6 
7-- Filtered queries: keep scanning until enough rows match (pgvector 0.8+)
8SET hnsw.iterative_scan = relaxed_order;

07Memory, quantization and rescoring

A float32 vector of 1,024 dimensions takes 4 KB. Ten million of them is about 40 GB before index overhead, and HNSW wants the hot part in RAM. Quantization shrinks this. Half precision (halfvec in pgvector) halves size with almost no recall loss. Scalar quantization to int8 cuts it by four. Binary quantization stores one bit per dimension, 32 times smaller, and works surprisingly well on many modern embedding models when combined with rescoring: search the compact index for, say, the top 200 candidates, then re-rank them with the full-precision vectors. Engines like Qdrant, Milvus and Elasticsearch implement this pattern natively; in pgvector you can index a binary expression and rescore in SQL.

1-- pgvector: binary-quantized index with full-precision rescoring
2CREATE INDEX ON doc_chunks USING hnsw
3  ((binary_quantize(embedding)::bit(1024)) bit_hamming_ops);
4 
5SELECT id FROM (
6  SELECT id, embedding FROM doc_chunks
7  ORDER BY binary_quantize(embedding)::bit(1024) <~> binary_quantize($1)
8  LIMIT 200
9) candidates
10ORDER BY embedding <=> $1 LIMIT 10;

08Keeping vectors fresh: ingestion and re-embedding

Vectors are derived data. Treat embedding as a pipeline: when a document changes, an event (outbox row or queue job) triggers chunking and embedding, and the result is upserted by a stable ID such as doc_id plus chunk number, deleting stale chunks of the same document. Make jobs idempotent and rate-limited, because embedding APIs have quotas. Model upgrades need a migration plan: create a new column or collection for the new model, backfill in the background, evaluate side by side, switch reads behind a flag, then drop the old vectors. HNSW indexes degrade a little with heavy updates and deletes; schedule periodic reindexing (REINDEX CONCURRENTLY in PostgreSQL) or rely on the engine's optimiser.

09Security and privacy of embeddings

Embeddings are not anonymous. Research has shown that text can be partly reconstructed from its embedding, so treat vectors with the same sensitivity as the source content. Enforce access control at query time: tenant and permission filters inside the search, row-level security in PostgreSQL, or separate collections for strict isolation. When sending content to an external embedding API, check data processing terms and avoid sending secrets or unnecessary personal data. Deleting a document must delete its vectors too, which matters for GDPR erasure. Finally, retrieved content can contain prompt injection when used in RAG, so treat retrieved text as untrusted data in the prompt.