Vector Databases & pgvector: Scaling Semantic Search in Postgres
By: Md Tarek · 17 Oct 2026
Executive Summary & Key Takeaways
- A vector database stores embeddings — numbers, not words — and answers "what is closest to this query vector?" with approximate nearest-neighbor (ANN) search.
- pgvector keeps everything in Postgres. No separate service, no data-sync pipeline; your vectors live next to the source rows and you can join, filter, and transact over them with ordinary SQL.
- HNSW is the index you want. It trades a little recall for orders-of-magnitude faster search than exact scan; IVFFlat is the older alternative that needs a representative training set.
- Distance metric matters as much as the index. Use cosine for normalized text embeddings, L2 when magnitude matters, and inner product when your model was trained for it.
- Hybrid search beats vector-only. Combine vector similarity with BM25/tsvector and fuse the results with Reciprocal Rank Fusion for exact-token recall that pure embeddings lose.
1. Embeddings and Distance Metrics
An embedding model turns text into a fixed-length vector (e.g. 1536 floats for OpenAI's older text-embedding-3-small). Similarity is a distance between vectors. The metric must match how your model was trained or you get subtly wrong results that are hard to debug.
2. pgvector Setup and HNSW
create extension if not exists vector; create table notes ( id bigserial primary key, body text, embedding vector(1536) ); create index on notes using hnsw (embedding vector_cosine_ops); select id, body from notes order by embedding <=> $1::vector limit 10;
The <=> operator is cosine distance. HNSW builds a multi-layer proximity graph that allows greedy navigation to the nearest neighbors in roughly O(log n) steps.
3. HNSW vs IVFFlat
| Property | HNSW | IVFFlat | |---|---|---| | Build time | slower, no training | needs a training pass | | Query speed | fast | fast, within a probe budget | | Recall | high | lower unless probes raised | | Insert behavior | incremental | may degrade, needs rebuild |
For a corpus that keeps growing, HNSW is the safer default; IVFFlat is worth considering for very large, stable datasets.
4. Hybrid Retrieval
Vector search fails on exact identifiers (error codes, product SKUs, names). Combine it with Postgres full-text search and merge the two ranked lists with RRF (score = sum of 1/(k + rank)). This recovers the lexical precision embeddings lose while keeping semantic recall.
FAQ
Do I need a dedicated vector DB like Pinecone or Weaviate? Not for most workloads. If you already run Postgres and your corpus is under a few million vectors, pgvector removes an entire service and its sync pipeline. Dedicated stores win when you need managed scale-out or specialized features like namespaced multi-tenancy.
