How do you do top-K semantic search with pgvector?

Category: SQL for AI Engineering

Answer

Embed the question -> use the SAME model -> ORDER BY embedding <=> :query_vec -> add a LIMIT. Pair the operator with the index op-class (vector_cosine_ops for <=>). All metadata filters MUST be index-friendly or you’ll fall back to Seq Scan.

Concrete examples from the fca project context

Example 1

SELECT id, content FROM chunks ORDER BY embedding <=> :q LIMIT 10;

Example 2

For tenant filter: WHERE tenant_id = :t AND embedding <=> :q ORDER BY embedding <=> :q LIMIT 10; — with HNSW.

Example 3

Different model = different dim = recreate column AND reindex.

Example 4

For top-K on filtered: pre-filter to 1.5*K rows then ANN within those.

Data flow / flow chart

embed(q) -> ORDER BY embedding <=> :q LIMIT K
  (cosine index for cosine, inner-product for normalized)

Takeaway

Cosine distance < 0.05 = strong match (scale 0..2, NOT 0..1). Pick op-class matching operator.