PostgreSQL full-text search with GIN index and ts_rank

<p>I need full-text search in PostgreSQL for finding traces by keywords. LIKE queries are too slow and do not support stemming. I want indexed text search with relevance ranking.</p>

<p>Full-text search with generated tsvector column:</p> <div class="highlight"><pre><span></span><code><span class="c1">-- Generated column (auto-maintained):</span> <span class="k">ALTER</span><span class="w"> </span><span class="k">TABLE</span><span class="w"> </span><span class="n">traces</span><span class="w"> </span><span class="k">ADD</span><span class="w"> </span><span class="k">COLUMN</span><span class="w"> </span><span class="n">search_vector</span><span class="w"> </span><span class="n">tsvector</span> <span class="w"> </span><span class="k">GENERATED</span><span class="w"> </span><span class="n">ALWAYS</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="p">(</span> <span class="w"> </span><span class="n">to_tsvector</span><span class="p">(</span><span class="s1">'english'</span><span class="p">,</span> <span class="w"> </span><span class="k">coalesce</span><span class="p">(</span><span class="n">title</span><span class="p">,</span><span class="w"> </span><span class="s1">''</span><span class="p">)</span><span class="w"> </span><span class="o">||</span><span class="w"> </span><span class="s1">' '</span><span class="w"> </span><span class="o">||</span> <span class="w"> </span><span class="k">coalesce</span><span class="p">(</span><span class="n">context_text</span><span class="p">,</span><span class="w"> </span><span class="s1">''</span><span class="p">)</span> <span class="w"> </span><span class="p">)</span> <span class="w"> </span><span class="p">)</span><span class="w"> </span><span class="n">STORED</span><span class="p">;</span> <span class="c1">-- GIN index for O(log n) search:</span> <span class="k">CREATE</span><span class="w"> </span><span class="k">INDEX</span><span class="w"> </span><span class="n">ix_traces_search_vector</span><span class="w"> </span><span class="k">ON</span><span class="w"> </span><span class="n">traces</span><span class="w"> </span><span class="k">USING</span><span class="w"> </span><span class="n">gin</span><span class="p">(</span><span class="n">search_vector</span><span class="p">);</span> <span class="c1">-- Search with relevance ranking:</span> <span class="k">SELECT</span><span class="w"> </span><span class="n">id</span><span class="p">,</span><span class="w"> </span><span class="n">title</span><span class="p">,</span><span class="w"> </span><span class="n">ts_rank</span><span class="p">(</span><span class="n">search_vector</span><span class="p">,</span><span class="w"> </span><span class="n">query</span><span class="p">)</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="n">rank</span> <span class="k">FROM</span><span class="w"> </span><span class="n">traces</span><span class="p">,</span><span class="w"> </span><span class="n">to_tsquery</span><span class="p">(</span><span class="s1">'english'</span><span class="p">,</span><span class="w"> </span><span class="s1">'react &amp; hooks'</span><span class="p">)</span><span class="w"> </span><span class="n">query</span> <span class="k">WHERE</span><span class="w"> </span><span class="n">search_vector</span><span class="w"> </span><span class="o">@@</span><span class="w"> </span><span class="n">query</span><span class="w"> </span><span class="k">AND</span><span class="w"> </span><span class="n">status</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="s1">'validated'</span> <span class="k">ORDER</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">rank</span><span class="w"> </span><span class="k">DESC</span><span class="w"> </span><span class="k">LIMIT</span><span class="w"> </span><span class="mi">20</span><span class="p">;</span> </code></pre></div> <p>SQLAlchemy equivalent:</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">func</span><span class="p">,</span> <span class="n">text</span> <span class="n">ts_query</span> <span class="o">=</span> <span class="n">func</span><span class="o">.</span><span class="n">to_tsquery</span><span class="p">(</span><span class="s1">'english'</span><span class="p">,</span> <span class="s1">'react &amp; hooks'</span><span class="p">)</span> <span class="n">stmt</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="p">,</span> <span class="n">func</span><span class="o">.</span><span class="n">ts_rank</span><span class="p">(</span><span class="n">Trace</span><span class="o">.</span><span class="n">search_vector</span><span class="p">,</span> <span class="n">ts_query</span><span class="p">)</span><span class="o">.</span><span class="n">label</span><span class="p">(</span><span class="s1">'rank'</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">search_vector</span><span class="o">.</span><span class="n">op</span><span class="p">(</span><span class="s1">'@@'</span><span class="p">)(</span><span class="n">ts_query</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="s1">'validated'</span><span class="p">)</span> <span class="o">.</span><span class="n">order_by</span><span class="p">(</span><span class="n">text</span><span class="p">(</span><span class="s1">'rank DESC'</span><span class="p">))</span> <span class="o">.</span><span class="n">limit</span><span class="p">(</span><span class="mi">20</span><span class="p">)</span> <span class="p">)</span> <span class="c1"># For user input (no boolean syntax required):</span> <span class="n">ts_query</span> <span class="o">=</span> <span class="n">func</span><span class="o">.</span><span class="n">plainto_tsquery</span><span class="p">(</span><span class="s1">'english'</span><span class="p">,</span> <span class="n">user_input</span><span class="p">)</span> </code></pre></div> <p>Key points: - GENERATED ALWAYS AS STORED keeps tsvector in sync automatically - GIN index makes @@ search O(log n) not sequential scan - to_tsquery normalizes with stemming -- 'running' matches 'run' - Combine with pgvector for hybrid semantic + keyword search - Use plainto_tsquery for user input -- handles spaces without boolean operators</p>