pgvector ANN search with trust re-ranking in SQLAlchemy

<p>I have PostgreSQL with pgvector embeddings and a trust_score. I want semantic search that combines vector similarity with trust score for final ranking, without cutting off high-trust results before re-ranking.</p>

<p>Over-fetch then re-rank:</p> <div class="highlight"><pre><span></span><code><span class="kn">from</span><span class="w"> </span><span class="nn">sqlalchemy</span><span class="w"> </span><span class="kn">import</span> <span class="n">select</span> <span class="k">async</span> <span class="k">def</span><span class="w"> </span><span class="nf">search_traces</span><span class="p">(</span> <span class="n">session</span><span class="p">:</span> <span class="n">AsyncSession</span><span class="p">,</span> <span class="n">query_embedding</span><span class="p">:</span> <span class="nb">list</span><span class="p">[</span><span class="nb">float</span><span class="p">],</span> <span class="n">limit</span><span class="p">:</span> <span class="nb">int</span> <span class="o">=</span> <span class="mi">10</span><span class="p">,</span> <span class="n">ann_limit</span><span class="p">:</span> <span class="nb">int</span> <span class="o">=</span> <span class="mi">100</span><span class="p">,</span> <span class="p">)</span> <span class="o">-&gt;</span> <span class="nb">list</span><span class="p">:</span> <span class="n">cosine_dist</span> <span class="o">=</span> <span class="n">Trace</span><span class="o">.</span><span class="n">embedding</span><span class="o">.</span><span class="n">op</span><span class="p">(</span><span class="s2">"&lt;=&gt;"</span><span class="p">)(</span> <span class="n">func</span><span class="o">.</span><span class="n">cast</span><span class="p">(</span><span class="n">query_embedding</span><span class="p">,</span> <span class="n">Vector</span><span class="p">(</span><span class="mi">1536</span><span class="p">))</span> <span class="p">)</span> <span class="c1"># ANN: over-fetch 100 candidates for re-ranking</span> <span class="n">ann_q</span> <span class="o">=</span> <span class="p">(</span> <span class="n">select</span><span class="p">(</span> <span class="n">Trace</span><span class="o">.</span><span class="n">id</span><span class="p">,</span> <span class="n">Trace</span><span class="o">.</span><span class="n">title</span><span class="p">,</span> <span class="n">Trace</span><span class="o">.</span><span class="n">trust_score</span><span class="p">,</span> <span class="p">(</span><span class="mi">1</span> <span class="o">-</span> <span class="n">cosine_dist</span><span class="p">)</span><span class="o">.</span><span class="n">label</span><span class="p">(</span><span class="s2">"similarity_score"</span><span class="p">),</span> <span class="p">)</span> <span class="o">.</span><span class="n">where</span><span class="p">(</span><span class="n">Trace</span><span class="o">.</span><span class="n">status</span> <span class="o">==</span> <span class="s2">"validated"</span><span class="p">)</span> <span class="o">.</span><span class="n">where</span><span class="p">(</span><span class="n">Trace</span><span class="o">.</span><span class="n">embedding</span><span class="o">.</span><span class="n">is_not</span><span class="p">(</span><span class="kc">None</span><span class="p">))</span> <span class="o">.</span><span class="n">order_by</span><span class="p">(</span><span class="n">cosine_dist</span><span class="p">)</span> <span class="o">.</span><span class="n">limit</span><span class="p">(</span><span class="n">ann_limit</span><span class="p">)</span> <span class="o">.</span><span class="n">subquery</span><span class="p">()</span> <span class="p">)</span> <span class="c1"># Re-rank: 70% similarity + 30% trust</span> <span class="n">combined</span> <span class="o">=</span> <span class="p">(</span><span class="n">ann_q</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">similarity_score</span> <span class="o">*</span> <span class="mf">0.7</span> <span class="o">+</span> <span class="n">ann_q</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">trust_score</span> <span class="o">*</span> <span class="mf">0.3</span><span class="p">)</span><span class="o">.</span><span class="n">label</span><span class="p">(</span><span class="s2">"score"</span><span class="p">)</span> <span class="n">result</span> <span class="o">=</span> <span class="k">await</span> <span class="n">session</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span> <span class="n">select</span><span class="p">(</span><span class="n">ann_q</span><span class="p">,</span> <span class="n">combined</span><span class="p">)</span><span class="o">.</span><span class="n">order_by</span><span class="p">(</span><span class="n">combined</span><span class="o">.</span><span class="n">desc</span><span class="p">())</span><span class="o">.</span><span class="n">limit</span><span class="p">(</span><span class="n">limit</span><span class="p">)</span> <span class="p">)</span> <span class="k">return</span> <span class="n">result</span><span class="o">.</span><span class="n">all</span><span class="p">()</span> <span class="c1"># HNSW index:</span> <span class="c1"># CREATE INDEX ON traces USING hnsw (embedding vector_cosine_ops)</span> <span class="c1"># WITH (m=16, ef_construction=64);</span> <span class="c1"># SET hnsw.ef_search = 100; -- at query time for higher recall</span> </code></pre></div> <p>Key points: - Fetch ann_limit=100 before trust re-ranking to avoid cutting off high-trust results - Wilson score returns [0,1] -- naturally normalized for combination with similarity - &lt;=&gt; is cosine distance; 1 - distance = similarity - Adjust 0.7/0.3 weights based on corpus maturity and user needs</p>