How to Add Postgres Full-Text Search (tsvector and tsquery)

Why ILIKE falls short

A query like body ILIKE '%running%' looks simple but has three hard limits:

• No stemming - 'running' won't match a row containing 'run' or 'ran'.
• No ranking - every match is equal; the most relevant result is not first.
• No index - a leading wildcard forces a full sequential scan on every query.

Postgres full-text search solves all three with built-in functions.

The core pattern

Replace the ILIKE WHERE clause with a tsvector @@ tsquery match and add ts_rank to the ORDER BY:

import psycopg2
from flask import Flask, request, jsonify

app = Flask(__name__)

def db():
    return psycopg2.connect(host="127.0.0.1", dbname="app", user="postgres")

@app.get("/api/search")
def search():
    q = request.args.get("q", "")
    conn = db()
    try:
        cur = conn.cursor()
        cur.execute(
            """
            SELECT id, title,
                   ts_rank(to_tsvector('english', title || ' ' || body),
                           plainto_tsquery('english', %s)) AS rank
            FROM articles
            WHERE to_tsvector('english', title || ' ' || body)
                  @@ plainto_tsquery('english', %s)
            ORDER BY rank DESC
            """,
            (q, q),
        )
        rows = [{"id": r[0], "title": r[1]} for r in cur.fetchall()]
    finally:
        conn.close()
    return jsonify(results=rows)

to_tsvector('english', text) tokenizes and stems the document - 'running' becomes the lexeme 'run'. plainto_tsquery('english', q) does the same to the search term, so they match even when the exact word form differs. @@ is the match operator, and ts_rank assigns a float score used for ordering.

Speed it up with a GIN index

Calling to_tsvector inside the WHERE clause re-computes the document vector on every query. For production, add a generated column and a GIN index:

ALTER TABLE articles
  ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    to_tsvector('english', title || ' ' || body)
  ) STORED;

CREATE INDEX articles_fts_idx ON articles USING GIN (search_vector);

Then simplify the query to use the stored column:

SELECT id, title,
       ts_rank(search_vector, plainto_tsquery('english', $1)) AS rank
FROM articles
WHERE search_vector @@ plainto_tsquery('english', $1)
ORDER BY rank DESC;

Production extras

• Weight title over body - use setweight before concatenating: setweight(to_tsvector('english', title), 'A') || setweight(to_tsvector('english', body), 'B'). Hits in the title score higher than hits in the body.
• Highlight matches - ts_headline('english', body, query) wraps matching terms in <b> tags for search result snippets.
• When to add Elasticsearch - Postgres FTS covers most apps well into millions of rows. Reach for Elasticsearch or OpenSearch when you need cross-index federation, fuzzy phonetic matching, or complex aggregation facets.