pgvector on Postgres you already run: prove you picked the right distance operator

The Stack

Steps

1. 1

   Prove the three pgvector operators disagree, and which one is safe

   pgvector exposes <-> (L2), <=> (cosine), and <#> (negative inner product). Over three sample rows and one query, CI ranks the query under each operator: L2 rewards the row closest by raw distance, cosine the row closest by direction, and inner product the row with the largest magnitude, so they pick three different nearest rows. Then it L2-normalizes and shows cosine and inner product now agree, which is why <=> (or normalized <#>) is the safe default for cosine-trained embeddings.

   python3 - <<'EOF'
   import math
   docs = {"near_by_distance": [0.9, 0.4], "right_direction": [3.0, 0.05], "big_magnitude": [9.0, 7.0]}
   q = [1.0, 0.0]
   def dot(u, v): return sum(a * b for a, b in zip(u, v))
   def l2(u, v): return math.sqrt(sum((a - b) * (a - b) for a, b in zip(u, v)))
   def cosine_distance(u, v): return 1 - dot(u, v) / (math.sqrt(dot(u, u)) * math.sqrt(dot(v, v)))
   def neg_inner_product(u, v): return -dot(u, v)
   def nearest(fn, D, query): return min(D, key=lambda k: fn(D[k], query))
   l2_top = nearest(l2, docs, q)
   cos_top = nearest(cosine_distance, docs, q)
   ip_top = nearest(neg_inner_product, docs, q)
   assert len({l2_top, cos_top, ip_top}) == 3, "the three operators should pick different rows on un-normalized vectors"
   def unit(v):
       n = math.sqrt(dot(v, v))
       return [x / n for x in v]
   ndocs = {k: unit(v) for k, v in docs.items()}
   nq = unit(q)
   ncos = nearest(cosine_distance, ndocs, nq)
   nip = nearest(neg_inner_product, ndocs, nq)
   assert ncos == nip, "after L2-normalization cosine and inner product must agree"
   print("operator check OK: on un-normalized embeddings pgvector's three operators pick DIFFERENT nearest rows (<-> L2=" + l2_top + ", <=> cosine=" + cos_top + ", <#> inner-product=" + ip_top + "); after L2-normalization <=> and <#> agree (" + ncos + "), so cosine is the safe default. Which operator matches YOUR embedding model, the SQL, and ANN recall tuning are fenced")
   EOF

2. 2

   Wire it into your Postgres (the parts CI cannot do for you)

   In your own database run CREATE EXTENSION vector, add a vector(N) column sized to your model, and build an index whose op-class matches your operator: hnsw (embedding vector_cosine_ops) for <=>, vector_l2_ops for <->, vector_ip_ops for <#>. Query with the SAME operator, for example ORDER BY embedding <=> query LIMIT k. Then measure recall@k against an exact scan and tune hnsw.ef_search or ivfflat.probes. Which operator matches your embeddings and what recall you accept are the fenced parts.