Skip to Content

Vector Similarity Search with pgvector

When to use

Retrieve the top-K most semantically similar chunks, documents, or items given a query embedding — the SQL half of every RAG pipeline.

Analogy

pgvector turns the database into a vector brain: you ask ‘what’s nearest to this embedding?’ and distance becomes the order-by column.

Data-flow diagram

  query_vec = [0.12, -0.04, ..., 0.83]      -- 1536 floats
  SELECT id, content
    FROM chunks
   ORDER BY embedding <=> query_vec          -- cosine distance
   LIMIT 5;

  +----+  ANN index (HNSW or IVFFlat)
  |HNSW|  -> O(log n) nearest-neighbour lookup
  +----+

Deep explanation

The pgvector extension adds a vector(N) type, distance operators (&lt;-> L2, &lt;=> cosine, &lt;#> inner product), and ANN indexes (HNSW and IVFFlat). Cosine distance (&lt;=>) is the right operator any time embeddings are normalized — it ignores magnitude and focuses on direction. Index choice: HNSW wins on recall and low-latency K-NN; IVFFlat wins on memory and bulk-build time. Always store the embedding column with the dimension matching the model exactly (1536 for OpenAI text-embedding-3-small/large, 1024 for voyage-large, etc.).

Examples

Example 1

-- 13a: cosine nearest-neighbour retrieval
CREATE EXTENSION IF NOT EXISTS vector;
 
SELECT id, content, embedding <=> :query_vec AS distance
  FROM chunks
 ORDER BY embedding <=> :query_vec
 LIMIT 5;

Cosine distance via &lt;=> — works on the entire pgvector-indexed column without round-trip to Python.

Example 2

-- 13b: filtered ANN search (metadata pre-filter)
SELECT id, content
  FROM chunks
 WHERE tenant_id = :t
   AND embedding <=> :q < 0.3
 ORDER BY embedding <=> :q
 LIMIT 10;

Tenant filter BEFORE the ANN lets HNSW prune, then it scores only the filtered set — this is essential for multi-tenant RAG at scale.

Example 3

-- 13c: hybrid: vector + lex (RRF-style merge)
WITH vec AS (
  SELECT id, content, ROW_NUMBER() OVER (ORDER BY embedding <=> :q) AS r_v
    FROM chunks ORDER BY embedding <=> :q LIMIT 50
),
lex AS (
  SELECT id, content, ROW_NUMBER() OVER (ORDER BY ts_rank DESC) AS r_l
    FROM chunks
   WHERE to_tsvector('english', content) @@ plainto_tsquery('english', :q)
   LIMIT 50
)
SELECT v.id, v.content,
       (1.0 / (60 + v.r_v)) + (1.0 / (60 + l.r_l)) AS rrf
  FROM vec v JOIN lex l USING (id)
 ORDER BY rrf DESC LIMIT 10;

RRF (Reciprocal Rank Fusion) merge of vector and BM25 ranks combines complementary signal: vector for semantics, lex for exact terms.

Common mistake

Mixing distance operators. Using &lt;-> (L2) on non-normalized embeddings gives misleading results — same direction vectors can have wildly different L2 distances. Always normalize the embedding before inserting (or pick an embedding model that returns unit vectors). Another trap: HNSW with the wrong operator class (vector_l2_ops when your query uses &lt;=>) — index is silently skipped.

Key takeaway

pgvector: cosine (&lt;=>) for direction-only similarity; HNSW for low-latency K-NN; IVFFlat for memory-tight bulk build; always normalize embeddings before insert; filter on metadata BEFORE the ANN order-by for accurate scaling.

Production Failure Playbook

Failure scenario 1: hnsw-wrong-operator-class

Failure scenario 2: embedding-dim-mismatch