Full-Text Search with PostgreSQL

Most apps need search. PostgreSQL ships with a full-text search engine built in — tsvector, tsquery, GIN indexes, relevance ranking. No Elasticsearch, no external service, no extra bill.

Why Not Just Use LIKE?

-- LIKE: slow on large tables, no ranking
SELECT * FROM posts WHERE title LIKE '%flask%';

-- Full-text: uses an index, returns ranked results
SELECT *, ts_rank(search_vector, query) AS rank
FROM posts, plainto_tsquery('english', 'flask tutorial') query
WHERE search_vector @@ query
ORDER BY rank DESC;

LIKE scans every row. Full-text search uses a GIN index — it stays fast even with millions of rows.

Add a Search Column to Your Table

Add a tsvector column to store the preprocessed search tokens:

-- Add the column
ALTER TABLE posts ADD COLUMN search_vector tsvector;

-- Populate it from title + body (title weighted more heavily)
UPDATE posts SET search_vector =
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body, '')), 'B');

-- Create a GIN index so searches use the index instead of scanning every row
CREATE INDEX idx_posts_search ON posts USING GIN(search_vector);

The setweight function assigns label A (highest) to title tokens and B to body tokens. This makes title matches rank above body matches.

Keep the Column Updated Automatically

Use a trigger so the column updates whenever a row is inserted or changed:

CREATE OR REPLACE FUNCTION posts_search_update() RETURNS trigger AS $$
BEGIN
    NEW.search_vector :=
        setweight(to_tsvector('english', coalesce(NEW.title, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(NEW.body, '')), 'B');
    RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER posts_search_vector_update
    BEFORE INSERT OR UPDATE ON posts
    FOR EACH ROW EXECUTE FUNCTION posts_search_update();

Now every INSERT or UPDATE automatically refreshes search_vector.

The Search Route

from flask import request, jsonify
from utils.db import get_db

@app.route("/api/search")
def search():
    query = request.args.get("q", "").strip()

    if not query:
        return jsonify({"results": [], "count": 0})

    with get_db() as conn:
        with conn.cursor() as cur:
            cur.execute("""
                SELECT
                    id,
                    title,
                    excerpt,
                    slug,
                    ts_rank(search_vector, plainto_tsquery('english', %s)) AS rank
                FROM posts
                WHERE search_vector @@ plainto_tsquery('english', %s)
                ORDER BY rank DESC
                LIMIT 20
            """, (query, query))
            results = cur.fetchall()

    return jsonify({
        "results": results,
        "count": len(results),
        "query": query
    })

plainto_tsquery converts a plain search string like "flask tutorial" into a proper query. It handles stopwords, stemming, and operator precedence automatically.

Search Form With Vanilla JS

<input type="text" id="search-input" placeholder="Search...">
<div id="search-results"></div>

const input = document.getElementById("search-input");
const results = document.getElementById("search-results");

let debounceTimer;

input.addEventListener("input", () => {
    clearTimeout(debounceTimer);
    debounceTimer = setTimeout(async () => {
        const q = input.value.trim();
        if (q.length < 2) {
            results.innerHTML = "";
            return;
        }

        const response = await fetch(`/api/search?q=${encodeURIComponent(q)}`);
        const data = await response.json();

        // Note: title and slug come from your own database — trusted data.
        // If you ever reflect the user's search query back into innerHTML, escape it first.
        results.innerHTML = data.results.map(r => `
            <a href="/posts/${r.slug}" class="search-result">
                <strong>${r.title}</strong>
            </a>
        `).join("");
    }, 300);
});

The 300ms debounce avoids firing a query on every keystroke.

Highlighting Matched Terms

PostgreSQL can highlight the matching words in a snippet:

cur.execute("""
    SELECT
        title,
        ts_headline(
            'english',
            body,
            plainto_tsquery('english', %s),
            'MaxWords=30, MinWords=15, StartSel=<mark>, StopSel=</mark>'
        ) AS snippet
    FROM posts
    WHERE search_vector @@ plainto_tsquery('english', %s)
    LIMIT 10
""", (query, query))

ts_headline returns a short excerpt with matched terms wrapped in your chosen tags (here <mark>).

AI Prompt That Generated This

"Add full-text search to a Flask + PostgreSQL app. The posts table has title and body columns. Use tsvector with a GIN index, trigger for auto-update, and a /api/search route using plainto_tsquery. Return ranked results as JSON. Use psycopg2 with %s parameters."

Next Steps

• Add Pagination to the search results