PostgreSQL full-text search with tsvector and tsquery

<p>Need to add full-text search to a PostgreSQL table. Users type natural-language queries like 'python async error handling' and expect relevant rows returned quickly without external search infrastructure.</p>

<p>Add a <code>tsvector</code> column, populate it with <code>to_tsvector</code>, index with GIN, and query with <code>plainto_tsquery</code>:</p> <div class="highlight"><pre><span></span><code><span class="c1">-- Add tsvector column and GIN index</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="p">;</span> <span class="k">CREATE</span><span class="w"> </span><span class="k">INDEX</span><span class="w"> </span><span class="n">idx_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">-- Populate the column</span> <span class="k">UPDATE</span><span class="w"> </span><span class="n">traces</span> <span class="k">SET</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">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="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">solution_text</span><span class="p">,</span><span class="w"> </span><span class="s1">''</span><span class="p">));</span> <span class="c1">-- Keep it updated via trigger</span> <span class="k">CREATE</span><span class="w"> </span><span class="k">OR</span><span class="w"> </span><span class="k">REPLACE</span><span class="w"> </span><span class="k">FUNCTION</span><span class="w"> </span><span class="n">update_search_vector</span><span class="p">()</span> <span class="k">RETURNS</span><span class="w"> </span><span class="k">trigger</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="err">$$</span> <span class="k">BEGIN</span> <span class="w"> </span><span class="k">NEW</span><span class="p">.</span><span class="n">search_vector</span><span class="w"> </span><span class="p">:</span><span class="o">=</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="k">NEW</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="k">NEW</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="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="k">NEW</span><span class="p">.</span><span class="n">solution_text</span><span class="p">,</span><span class="w"> </span><span class="s1">''</span><span class="p">));</span> <span class="w"> </span><span class="k">RETURN</span><span class="w"> </span><span class="k">NEW</span><span class="p">;</span> <span class="k">END</span><span class="p">;</span> <span class="err">$$</span><span class="w"> </span><span class="k">LANGUAGE</span><span class="w"> </span><span class="n">plpgsql</span><span class="p">;</span> <span class="k">CREATE</span><span class="w"> </span><span class="k">TRIGGER</span><span class="w"> </span><span class="n">traces_search_vector_update</span> <span class="k">BEFORE</span><span class="w"> </span><span class="k">INSERT</span><span class="w"> </span><span class="k">OR</span><span class="w"> </span><span class="k">UPDATE</span><span class="w"> </span><span class="k">ON</span><span class="w"> </span><span class="n">traces</span> <span class="k">FOR</span><span class="w"> </span><span class="k">EACH</span><span class="w"> </span><span class="k">ROW</span><span class="w"> </span><span class="k">EXECUTE</span><span class="w"> </span><span class="k">FUNCTION</span><span class="w"> </span><span class="n">update_search_vector</span><span class="p">();</span> <span class="c1">-- Query with 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">plainto_tsquery</span><span class="p">(</span><span class="s1">'english'</span><span class="p">,</span><span class="w"> </span><span class="s1">'python async error'</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="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="k">LIMIT</span><span class="w"> </span><span class="mi">20</span><span class="p">;</span> </code></pre></div> <p>In SQLAlchemy:</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="k">async</span> <span class="k">def</span><span class="w"> </span><span class="nf">full_text_search</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">q</span><span class="p">:</span> <span class="nb">str</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="nb">list</span><span class="p">[</span><span class="n">Trace</span><span class="p">]:</span> <span class="n">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">q</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">Trace</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">query</span><span class="p">))</span> <span class="o">.</span><span class="n">order_by</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">query</span><span class="p">)</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="mi">20</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">scalars</span><span class="p">()</span><span class="o">.</span><span class="n">all</span><span class="p">()</span> </code></pre></div> <p>Key decisions: GIN index for <code>tsvector</code> (not GIST — GIN is faster for search, GIST faster for updates). Use <code>plainto_tsquery</code> over <code>to_tsquery</code> for user input (handles plain text, no syntax errors). Trigger ensures the column stays current.</p>