Generative AI models excel at reasoning over text provided inside their immediate context window. However, LLMs suffer from knowledge cutoff limits and hallucination when queried about private enterprise databases, recent code commits, or proprietary documentation.

To solve this without spending millions of dollars on continuous model re-training, engineers implement Retrieval-Augmented Generation (RAG). At the heart of every production RAG architecture lies a Vector Database capable of executing Approximate Nearest Neighbor (ANN) search across millions of high-dimensional embedding vectors in sub-10ms latencies.

In this deep database engineering guide, we examine embedding vector spaces, similarity distance metrics (Cosine Similarity vs Dot Product), indexing algorithms (HNSW vs IVF-Flat), and production PostgreSQL pgvector SQL implementations.

1. What Are High-Dimensional Vector Embeddings?

An embedding model (such as OpenAI text-embedding-3-large or open-source bge-large-en-v1.5) converts unstructured text into a dense floating-point vector array of 768 to 3,072 dimensions.

Unlike legacy SQL keyword matching (WHERE title LIKE '%docker%'), vector embeddings capture semantic meanings and conceptual relationships. Words with similar meanings (e.g. "Kubernetes container deployment" and "K8s pod orchestration") map to points in space located close to each other.

2. Mathematical Distance Metrics Compared

To query the most relevant context chunks for a user query embedding $u$, the vector database measures spatial distance against stored document vectors $v$:

A. Cosine Similarity (Angle Between Vectors)

Measures the directional angle between two vectors, ignoring difference in magnitude length. Ideal for text documents of varying length:

$$\text{Cosine Similarity}(u, v) = \frac{u \cdot v}{\|u\| \|v\|} = \frac{\sum_{i=1}^d u_i v_i}{\sqrt{\sum_{i=1}^d u_i^2} \sqrt{\sum_{i=1}^d v_i^2}}$$

B. Inner Product / Dot Product

Calculates $u \cdot v = \sum u_i v_i$. If vectors are normalized to unit length ($\|u\|=1, \|v\|=1$), the Dot Product is mathematically identical to Cosine Similarity, but runs significantly faster on GPU/CPU SIMD instructions.

C. L2 Squared Euclidean Distance

Measures straight-line geometric distance $\|u - v\|^2 = \sum (u_i - v_i)^2$.

3. Indexing Algorithms: HNSW vs IVF-Flat

Performing brute-force cosine distance comparisons across 10 million 1536-dim vectors requires $O(N \cdot d)$ floating-point operations per query—causing multi-second database latencies. Approximate Nearest Neighbor (ANN) indexes trade tiny recall accuracy (e.g. 98% recall) for 100x speedups:

  • HNSW (Hierarchical Navigable Small World): Constructs a multi-layer graph where top layers have long-range links for fast coarse navigation, and bottom layers have short-range links for precise local search. Offers sub-millisecond query latency at the expense of higher RAM usage.
  • IVF-Flat (Inverted File Index): Partitions vector space into Voronoi clusters using k-means. Queries search only the nearest centroids. Uses less RAM than HNSW but suffers recall loss if cluster centroids shift.

4. Production PostgreSQL pgvector Implementation

Instead of introducing separate standalone vector databases (Pinecone, Qdrant), enterprise systems often use the open-source pgvector extension for PostgreSQL to combine relational SQL joins with vector search in a single database engine:

-- 1. Enable vector extension in PostgreSQL CREATE EXTENSION IF NOT EXISTS vector; -- 2. Create knowledge base table storing 1536-dim embeddings CREATE TABLE document_chunks ( id BIGSERIAL PRIMARY KEY, document_id UUID NOT NULL, content TEXT NOT NULL, metadata JSONB, embedding vector(1536) -- OpenAI text-embedding-3-small dimension ); -- 3. Create HNSW index using Cosine Distance operator (<=>) CREATE INDEX ON document_chunks USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64); -- 4. Execute Sub-5ms Vector Similarity Search SELECT id, content, 1 - (embedding <=> '[0.012, -0.045, 0.089, ...]'::vector) AS cosine_similarity FROM document_chunks WHERE metadata->>'category' = 'architecture' ORDER BY embedding <=> '[0.012, -0.045, 0.089, ...]'::vector ASC LIMIT 5;

5. Hybrid Search: BM25 Keyword + Vector Search Reciprocal Rank Fusion

Pure vector search sometimes fails on exact keyword identifiers like product part numbers (e.g. ERR_504_TIMEOUT).

Best practice RAG systems combine dense vector search with sparse BM25 keyword search using Reciprocal Rank Fusion (RRF) to re-rank results:

$$\text{RRF\_Score}(d) = \sum_{m \in M} \frac{1}{k + \text{rank}_m(d)}$$

6. Frequently Asked Questions (FAQ)

Q1: How much RAM does pgvector require?

One million 1536-dim vectors with an HNSW index require approximately 8 GB to 10 GB of RAM to keep the index fully in memory for sub-10ms queries.

Q2: What is the optimal document chunk size for RAG?

Chunk sizes between 256 and 512 tokens with a 10-15% overlap yield the best trade-off between semantic specificity and retrieval recall.