1. Beyond Keyword Search (BM25)
Traditional full-text database search relies on lexical keyword matching. If a user searches an IT support portal for "machine won't boot", keyword search fails to match articles titled "troubleshooting workstation power failure" because none of the words overlap. Vector embeddings solve this by converting text into dense mathematical arrays that capture conceptual semantic meaning.
2. Generating Embeddings with Open-Source Models
Using open-source embedding models from the Hugging Face Sentence Transformers library (such as all-MiniLM-L6-v2), sentences are mapped to high-dimensional dense vectors (e.g., 384 dimensions). Texts with similar conceptual meanings reside close to each other in the multidimensional vector space, regardless of specific vocabulary.
3. pgvector: Vector Storage Inside Existing PostgreSQL
Rather than deploying and managing a separate, complex standalone vector database, the open-source pgvector extension brings native vector storage directly into PostgreSQL. Developers simply add a vector column to existing relational tables: ALTER TABLE kb_articles ADD COLUMN embedding vector(384);.
4. Distance Metrics and Approximate Nearest Neighbor (ANN) Indexing
pgvector calculates semantic similarity using Cosine Distance (<=>), Euclidean Distance (<->), or Inner Product (<#>): SELECT title FROM kb_articles ORDER BY embedding <=> :query_vector LIMIT 5;. To maintain sub-50ms search speeds over millions of rows, pgvector builds Inverted File Flat (IVFFlat) or Hierarchical Navigable Small World (HNSW) indexes, balancing query recall with blazing speed.
