IVFFlat vs HNSW indexes, cosine vs L2 distance, hybrid search.

pgvector — Similarity Search Inside PostgreSQL

pgvector adds a vector data type and similarity search to PostgreSQL. Store embeddings alongside metadata in the same row. Two index types: IVFFlat (inverted file, approximate, good recall with proper lists count) and HNSW (hierarchical navigable small world, faster queries, more memory). Choose distance metric at index creation: cosine for normalised embeddings (text/images), L2 for unnormalised. Hybrid search combines vector similarity with BM25 full-text search via RRF (Reciprocal Rank Fusion).

Interactive Pipeline Flow

UnderstandImplementOptimise

🧬 Embed — generate vector

Generate embeddings using text-embedding-3-small (OpenAI) or sentence-transformers. Normalise vectors before storage if using cosine similarity.

Architecture Diagrams

Combining vector and full-text search via Reciprocal Rank Fusion.

Loading diagram...

Key Design Decisions

📑HNSW for production, IVFFlat for experimentation

HNSW has faster query performance and no need to retrain (IVFFlat must be rebuilt as data grows). Use HNSW for production RAG systems.

🔀Hybrid search improves recall significantly

Vector search alone misses exact keyword matches. BM25 alone misses semantic similarity. RRF combination consistently outperforms either alone in RAG retrieval benchmarks.

🔎Pre-filter before vector search

Add WHERE tenant_id = :tid AND category = :cat before the ORDER BY <=> clause. Reduces candidate set, improves accuracy, and speeds up search on large collections.

Discussion