`pg_trgm`: fuzzy search, similarity, and autocomplete without Elasticsearch

Capsule description

Your FTS works, ranks well, and shows highlighted snippets. But there's a hole that's visible to the user: if they type "pythn" (a common typo), or "fastpi", or "potgresql", FTS returns nothing because those words don't exist in the spanish_unaccent dictionary. The user assumes your product has no content on the topic and leaves. The same happens with autocomplete: the user types "pos" into the input and expects "PostgreSQL" to show up, but FTS needs a complete word to match.

This capsule teaches you the pg_trgm extension (trigrams), which indexes text by three-character sequences and enables similarity search ('pythn' ≈ 'python') and prefix matching ('pos' → 'PostgreSQL'). You're going to learn the two indexes it offers (GIN and GiST), the functions (similarity(), word_similarity()) and operators (%, <%, <<%), and the canonical pattern of combining pg_trgm with FTS: FTS as the main relevance search, pg_trgm as a fuzzy fallback when FTS returns zero results.

By the end you'll be able to add typo tolerance to your search engine, implement efficient autocomplete, and understand when pg_trgm complements FTS and when it replaces it.

Mental model: trigrams are the text's "fingerprints"

A trigram is any sequence of three consecutive characters within a word. PostgreSQL tokenizes the word (with space padding at the start and end) and extracts all its trigrams.

Word: "python"

Tokenization with padding:  "  python "     ← two spaces in front, one behind
Extracted trigrams:         "  p", " py", "pyt", "yth", "tho", "hon", "on "
                             ↑                                          ↑
                             (leading padding)             (trailing padding)

That's 7 trigrams. You can check it yourself with show_trgm():

SELECT show_trgm('python');
-- {"  p"," py",hon,"on ",pyt,tho,yth}

For "pythn" (the typo):

SELECT show_trgm('pythn');
-- {"  p"," py","hn ",pyt,thn,yth}

That's 6 trigrams. The two sets share 4: " p", " py", "pyt", "yth". Similarity is computed as (trigrams in common) / (total unique trigrams across both):

Similarity("python", "pythn") = 4 / (7 + 6 - 4) = 4/9 ≈ 0.444

And that's exactly what PostgreSQL returns (0.44444445). The formula isn't an approximation: it's the algorithm.

┌────────────────────────────────────────────────────────────────┐
│                                                                │
│  Word → set of trigrams (fingerprints)                         │
│                                                                │
│  "python"  → {  p,  py, pyt, yth, tho, hon, on }               │
│  "pythn"   → {  p,  py, pyt, yth, thn, hn }                    │
│  "java"    → {  j,  ja, jav, ava, va }                         │
│                                                                │
│  Comparing words = comparing sets of trigrams                  │
│                                                                │
│  similarity('python', 'pythn')  ≈ 0.44                         │
│  similarity('python', 'java')   =  0.0                         │
│  similarity('python', 'pthon')  ≈ 0.44                         │
│                                                                │
└────────────────────────────────────────────────────────────────┘

Three ideas to internalize:

1. Similarity is math over sets of trigrams. It doesn't use dictionaries, it doesn't use stemming, it doesn't understand language. It just counts trigrams in common.

2. It's language-agnostic. It works the same for English, Spanish, transliterated Japanese, or made-up words. That's why it complements FTS — where FTS needs a dictionary, pg_trgm doesn't.

3. It doesn't replace FTS for meaning-based search. "Cantando" and "cantar" have low trigram similarity (canta and canta are the only ones adjacent). FTS joins them because it knows they're the same root. pg_trgm doesn't know that. That's why they're different tools.

Setup: enabling the extension

CREATE EXTENSION IF NOT EXISTS pg_trgm;

After this, you have access to:

• Functions: similarity(text, text), word_similarity(text, text), strict_word_similarity(text, text), show_trgm(text).
• Operators: % (similar), <% (word similar), <<% (strict word similar), <-> (distance, the inverse of similarity).
• Indexes: GIN and GiST with an operator class specific to pg_trgm.

The similarity() function: comparing two strings

SELECT similarity('python', 'pythn');
-- 0.44444445

SELECT similarity('python', 'pthon');
-- 0.44444445

SELECT similarity('python', 'java');
-- 0

SELECT similarity('postgresql', 'postgres');
-- 0.6666667

SELECT similarity('postgresql', 'potgresql');
-- 0.61538464

The result is a float between 0 (nothing in common) and 1 (identical). The threshold for considering things "similar" is adjustable. By default, the % operator uses 0.3:

SELECT 'python' % 'pythn';   -- true (similarity > 0.3)
SELECT 'python' % 'java';    -- false (similarity ≈ 0)

You can change the threshold per session or per query with set_limit():

SELECT set_limit(0.5);  -- raise the threshold to 0.5
SELECT 'python' % 'pythn';   -- now false (similarity 0.44 < 0.5)

The threshold trade-off:

• Low (0.2): many matches, including false positives. Useful for aggressive autocomplete.
• Medium (0.3, the default): a balance between fuzzy and precise. The default for an FTS fallback.
• High (0.5+): only very clear matches. Useful for deduplication or a very conservative "did you mean".

word_similarity() and strict_word_similarity(): comparing a word against text

similarity() compares two complete strings. But sometimes you want to compare a word (the user's input) against a long text (a title or body) and see whether the word appears "similar" to some part of the text.

-- full similarity: compares the entire strings
SELECT similarity('python', 'aprende python para principiantes');
-- 0.21875 (low because most of the second string doesn't match)

-- word_similarity: the best similarity between 'python' and any subset of the text
SELECT word_similarity('python', 'aprende python para principiantes');
-- 1.0 (the word "python" appears literally)

-- strict_word_similarity: like word_similarity but requires matching complete words (not fragments)
SELECT strict_word_similarity('python', 'aprende python para principiantes');
-- 1.0

Associated operators:

• % — the similarity operator (compares complete strings).
• <% — the word_similarity operator (compares a word against text, fragments OK).
• <<% — the strict_word_similarity operator (compares a word against text, whole words).
• <-> — distance (1 - similarity), useful for ORDER BY (lowest distance first).

When to use each one:

• similarity() / %: comparing short strings like product names, slugs, identifiers.
• word_similarity() / <%: finding a word inside a long text. Useful for fuzzy FTS where the user types "pythn" and you want to match any post containing something similar.
• strict_word_similarity() / <<%: like word_similarity but stricter (it requires matching a whole word, not a fragment). Fewer false positives.

pg_trgm indexes: GIN or GiST

Without an index, similarity() and % require a sequential scan. For large tables, that's slow. pg_trgm offers two index types:

-- Option A: GIN (faster for searching)
CREATE INDEX idx_posts_title_trgm_gin ON posts USING GIN (title gin_trgm_ops);

-- Option B: GiST (faster for writing, useful for distance / ORDER BY <->)
CREATE INDEX idx_posts_title_trgm_gist ON posts USING GiST (title gist_trgm_ops);

Differences:

Rule:

• Typical fuzzy search (binary "similar or not" matching): GIN.
• Top N by similarity (ORDER BY col <-> 'query' LIMIT 10): GiST. It's what makes "find the 10 most similar" efficient for autocomplete.
• Mixed workload: GiST is more balanced.

For autocomplete, GiST is almost always the choice, because you want to order by similarity.

Pattern 1: fuzzy fallback for FTS

The most useful pattern. The main search is FTS (fast, with stemming, ranking). If FTS returns zero results (because the user typed it wrong), you fall back to pg_trgm, which does tolerate typos.

# search_with_fallback.py
from typing import Any

from sqlalchemy import desc, func, select
from sqlalchemy.ext.asyncio import AsyncSession

from models import Post


TS_CONFIG = "spanish_unaccent"


async def search_with_fallback(
    session: AsyncSession, q: str, limit: int = 20
) -> dict[str, Any]:
    """
    Two-step search:
    1. Main FTS with ranking.
    2. If FTS returns 0, fall back to pg_trgm over title.
    """
    tsquery = func.websearch_to_tsquery(TS_CONFIG, q)

    # Step 1: FTS
    rank = func.ts_rank_cd(Post.tsv, tsquery).label("rank")
    fts_stmt = (
        select(Post.id, Post.title, rank)
        .where(Post.tsv.bool_op("@@")(tsquery))
        .order_by(desc(rank))
        .limit(limit)
    )
    fts_result = (await session.execute(fts_stmt)).all()

    if fts_result:
        return {
            "query": q,
            "method": "fts",
            "count": len(fts_result),
            "results": [
                {"id": r.id, "title": r.title, "rank": float(r.rank)}
                for r in fts_result
            ],
        }

    # Step 2: fuzzy fallback with pg_trgm
    # Uses <-> to order by distance (lower = more similar)
    similarity = func.similarity(Post.title, q).label("similarity")
    trgm_stmt = (
        select(Post.id, Post.title, similarity)
        .where(Post.title.bool_op("%")(q))  # pg_trgm's % operator
        .order_by(desc(similarity))
        .limit(limit)
    )
    trgm_result = (await session.execute(trgm_stmt)).all()

    return {
        "query": q,
        "method": "fuzzy",
        "count": len(trgm_result),
        "results": [
            {"id": r.id, "title": r.title, "similarity": float(r.similarity)}
            for r in trgm_result
        ],
    }

Example usage:

# The user types it correctly
await search_with_fallback(session, "python")
# → method="fts", returns ranked results

# The user types it with a typo
await search_with_fallback(session, "pythn")
# → FTS returns 0 ("pythn" doesn't exist in the dictionary)
# → fall back to pg_trgm: matches posts with "python" in the title by similarity
# → method="fuzzy", returns results ordered by similarity

For the fallback to be fast, you add an index:

CREATE INDEX CONCURRENTLY idx_posts_title_trgm
  ON posts USING GiST (title gist_trgm_ops);

GiST because you want ORDER BY similarity DESC (equivalent to ORDER BY title <-> q ASC).

Why this pattern wins:

• FTS is still the fast path for well-typed queries (most of them).
• pg_trgm only comes into play when FTS fails, avoiding unnecessary overhead.
• The user never sees "0 results" because of a trivial typo.

Pattern 2: autocomplete with prefix matching

For autocomplete, you want "PostgreSQL", "Posición", "Postal", etc. to show up while the user types "pos". There are two strategies:

Option A: LIKE 'pos%' with a B-tree index

SELECT title FROM posts WHERE title ILIKE 'pos%' LIMIT 10;

It needs a text_pattern_ops (or varchar_pattern_ops) index for LIKE 'prefix%' to use an index:

CREATE INDEX idx_posts_title_pattern ON posts (title text_pattern_ops);

Pros: fast, predictable. Cons: it only matches from the start of the title. "Posición de PostgreSQL" matches "pos" but "El nuevo PostgreSQL" doesn't match even though it obviously should for "post".

Option B: pg_trgm with <-> and GiST

SELECT title, title <-> 'pos' AS distance
FROM posts
WHERE title % 'pos'
ORDER BY title <-> 'pos'
LIMIT 10;

It needs a GiST index:

CREATE INDEX idx_posts_title_trgm ON posts USING GiST (title gist_trgm_ops);

Pros: it matches similarity anywhere in the word. "El nuevo PostgreSQL" does show up. It tolerates typos. Cons: slower than plain LIKE. For very large corpora (>10M rows) it may not be enough.

Option C (best): combine both

For production-ready autocomplete, exact prefix first (faster), fuzzy afterwards if there aren't enough results:

async def autocomplete(session: AsyncSession, prefix: str, limit: int = 10) -> list[dict]:
    """
    1. Exact prefix with LIKE (fast).
    2. If there are fewer than `limit` results, fill in with pg_trgm fuzzy.
    """
    # Step 1: exact prefix
    prefix_stmt = (
        select(Post.id, Post.title)
        .where(Post.title.ilike(f"{prefix}%"))
        .order_by(Post.title)
        .limit(limit)
    )
    prefix_results = (await session.execute(prefix_stmt)).all()

    if len(prefix_results) >= limit:
        return [{"id": r.id, "title": r.title} for r in prefix_results]

    # Step 2: fuzzy to fill the remaining slots
    already_ids = [r.id for r in prefix_results]
    remaining = limit - len(prefix_results)

    fuzzy_stmt = (
        select(Post.id, Post.title)
        .where(Post.title.bool_op("%")(prefix))
        .where(Post.id.notin_(already_ids) if already_ids else True)
        .order_by(Post.title.op("<->")(prefix))
        .limit(remaining)
    )
    fuzzy_results = (await session.execute(fuzzy_stmt)).all()

    return [
        *[{"id": r.id, "title": r.title, "method": "prefix"} for r in prefix_results],
        *[{"id": r.id, "title": r.title, "method": "fuzzy"} for r in fuzzy_results],
    ]

This gives maximum-quality results and reasonable latency: the exact stuff comes out fast, the fuzzy fills in.

Worked example: complete setup + search with fallback + autocomplete

Let's put it all together end-to-end.

1. Alembic migration

# alembic/versions/20260503_add_pg_trgm.py
"""Enables pg_trgm and creates indexes for fuzzy search and autocomplete.

Revision ID: 20260503_pg_trgm
Revises: 20260502_fts_posts
Create Date: 2026-05-03
"""
from alembic import op


revision = "20260503_pg_trgm"
down_revision = "20260502_fts_posts"


def upgrade() -> None:
    # 1. Enable the extension
    op.execute("CREATE EXTENSION IF NOT EXISTS pg_trgm")

    # 2. GiST index for fuzzy + autocomplete over title
    op.execute("COMMIT")  # for CONCURRENTLY
    op.execute(
        "CREATE INDEX CONCURRENTLY idx_posts_title_trgm "
        "ON posts USING GiST (title gist_trgm_ops)"
    )

    # 3. B-tree index for fast exact prefix
    op.execute(
        "CREATE INDEX CONCURRENTLY idx_posts_title_pattern "
        "ON posts (title text_pattern_ops)"
    )


def downgrade() -> None:
    op.execute("DROP INDEX IF EXISTS idx_posts_title_trgm")
    op.execute("DROP INDEX IF EXISTS idx_posts_title_pattern")
    # We don't drop pg_trgm because other tables may depend on it

2. FastAPI endpoints

# search_full.py
from typing import Any, Literal

from fastapi import FastAPI, Query
from pydantic import BaseModel
from sqlalchemy import desc, func, select
from sqlalchemy.ext.asyncio import async_sessionmaker, create_async_engine

from models import Post


engine = create_async_engine(
    "postgresql+asyncpg://postgres:postgres@localhost:5432/blog"
)
SessionLocal = async_sessionmaker(engine, expire_on_commit=False)
app = FastAPI()

TS_CONFIG = "spanish_unaccent"


class SearchResult(BaseModel):
    id: int
    title: str
    score: float
    method: Literal["fts", "fuzzy"]


class SearchResponse(BaseModel):
    query: str
    count: int
    method: Literal["fts", "fuzzy", "empty"]
    results: list[SearchResult]


@app.get("/search", response_model=SearchResponse)
async def search(
    q: str = Query(..., min_length=1, max_length=200),
    limit: int = Query(20, ge=1, le=100),
) -> SearchResponse:
    """
    Search with a fuzzy fallback:
    1. Main FTS (fast, with stemming + ranking).
    2. If FTS returns 0, pg_trgm fuzzy over title.
    """
    async with SessionLocal() as session:
        # Step 1: FTS
        tsquery = func.websearch_to_tsquery(TS_CONFIG, q)
        rank = func.ts_rank_cd(Post.tsv, tsquery).label("score")
        fts_stmt = (
            select(Post.id, Post.title, rank)
            .where(Post.tsv.bool_op("@@")(tsquery))
            .order_by(desc(rank))
            .limit(limit)
        )
        fts_rows = (await session.execute(fts_stmt)).all()

        if fts_rows:
            return SearchResponse(
                query=q,
                count=len(fts_rows),
                method="fts",
                results=[
                    SearchResult(
                        id=r.id, title=r.title, score=float(r.score), method="fts"
                    )
                    for r in fts_rows
                ],
            )

        # Step 2: fuzzy fallback
        similarity = func.similarity(Post.title, q).label("score")
        fuzzy_stmt = (
            select(Post.id, Post.title, similarity)
            .where(Post.title.bool_op("%")(q))
            .order_by(desc(similarity))
            .limit(limit)
        )
        fuzzy_rows = (await session.execute(fuzzy_stmt)).all()

        if fuzzy_rows:
            return SearchResponse(
                query=q,
                count=len(fuzzy_rows),
                method="fuzzy",
                results=[
                    SearchResult(
                        id=r.id, title=r.title, score=float(r.score), method="fuzzy"
                    )
                    for r in fuzzy_rows
                ],
            )

        # No results
        return SearchResponse(query=q, count=0, method="empty", results=[])


@app.get("/autocomplete")
async def autocomplete(
    prefix: str = Query(..., min_length=2, max_length=50),
    limit: int = Query(10, ge=1, le=20),
) -> dict[str, Any]:
    """Autocomplete combining exact prefix + fuzzy."""
    async with SessionLocal() as session:
        # Exact prefix first
        prefix_stmt = (
            select(Post.id, Post.title)
            .where(Post.title.ilike(f"{prefix}%"))
            .order_by(Post.title)
            .limit(limit)
        )
        prefix_rows = (await session.execute(prefix_stmt)).all()

        if len(prefix_rows) >= limit:
            return {
                "prefix": prefix,
                "suggestions": [
                    {"id": r.id, "title": r.title, "method": "prefix"}
                    for r in prefix_rows
                ],
            }

        # Fill the rest with fuzzy
        already_ids = [r.id for r in prefix_rows]
        remaining = limit - len(prefix_rows)

        fuzzy_q = (
            select(Post.id, Post.title)
            .where(Post.title.bool_op("%")(prefix))
            .order_by(Post.title.op("<->")(prefix))
            .limit(remaining)
        )
        if already_ids:
            fuzzy_q = fuzzy_q.where(Post.id.notin_(already_ids))

        fuzzy_rows = (await session.execute(fuzzy_q)).all()

        return {
            "prefix": prefix,
            "suggestions": [
                *[{"id": r.id, "title": r.title, "method": "prefix"} for r in prefix_rows],
                *[{"id": r.id, "title": r.title, "method": "fuzzy"} for r in fuzzy_rows],
            ],
        }

3. Testing end-to-end

# Correct search — uses FTS
curl 'http://localhost:8000/search?q=python+fastapi'
# {"query":"python fastapi","count":4,"method":"fts","results":[...]}

# Search with a typo — uses the fuzzy fallback
curl 'http://localhost:8000/search?q=pythn'
# {"query":"pythn","count":3,"method":"fuzzy","results":[...]}

# Autocomplete
curl 'http://localhost:8000/autocomplete?prefix=pos'
# {"prefix":"pos","suggestions":[{"title":"PostgreSQL avanzado","method":"prefix"},{"title":"Las posibilidades de Postgres","method":"prefix"}, ...]}

Why does this matter in real work?

1. Typo tolerance is what separates a "Google-style" search engine from a "primitive SQL" one. The user on mobile types fast and makes mistakes. If your search returns zero because of a typo, they assume the content doesn't exist. If it returns similar results, they perceive your product as smart.

2. Autocomplete is UX the user expects and will notice if it's missing. In 2026, every search input in a consumer app has autocomplete. If your app doesn't, it feels old. pg_trgm gives it to you without adding Algolia or Typesense.

3. It's the answer to "we need Elasticsearch for search-as-you-type." When someone proposes ES for autocomplete, your answer is "PostgreSQL does prefix matching with text_pattern_ops and fuzzy with pg_trgm in <50ms. Why do we need a new service?". Defend it with numbers, not with an opinion.

4. Telling when to combine FTS + pg_trgm from when to pick just one is senior work. FTS is for searching meaning (known words, stemming, ranking). pg_trgm is for searching syntactic similarity (typos, autocomplete, deduplication). Confusing them leads to suboptimal setups. Knowing the "main FTS + trigram fallback" pattern sets you apart from someone copying a random tutorial.

Traps and common mistakes

Mistake 1 (conceptual): using pg_trgm as a replacement for FTS

Symptom: someone reads about pg_trgm and builds the whole search with WHERE title % q AND body % q. It works, but the results are irrelevant for multi-word queries and there's no reasonable ranking.

Why it happens: pg_trgm looks more "modern" or "fuzzy," so people assume it's better than FTS. It isn't — they're different tools.

How to tell:

• Does the query have several words and should it rank by relevance? → FTS.
• Is the query a single word that may have typos? → pg_trgm.
• Do you want both? → combine them (main FTS + pg_trgm fallback).

Fix: follow the capsule's canonical pattern. FTS for meaning-based search, pg_trgm for fuzzy/autocomplete.

Mistake 2 (practical): a GIN index over title without the gin_trgm_ops operator class

Symptom: CREATE INDEX ON posts USING GIN (title) fails with an error or creates an index that's useless for pg_trgm.

Why it happens: GIN over types like text isn't direct — it needs an operator class that says "how to decompose this type into indexable values." For pg_trgm, that operator class is gin_trgm_ops.

Fix:

CREATE INDEX idx_posts_title_trgm ON posts USING GIN (title gin_trgm_ops);
-- or GiST:
CREATE INDEX idx_posts_title_trgm ON posts USING GiST (title gist_trgm_ops);

Without the operator class, the index is useless for %, <%, <<%, <->.

Mistake 3 (conceptual): assuming <-> is the Earth Distance operator

Symptom: you see <-> in the pg_trgm docs and confuse it with the PostGIS or cube operator.

Why it happens: many extensions reuse <-> for "distance." In pg_trgm, it's 1 - similarity. In PostGIS, it's geographic distance. In cube, it's n-dimensional distance.

How to tell: the context. text <-> text is pg_trgm. point <-> point is geometry. cube <-> cube is cube.

Useful for: ORDER BY title <-> 'q' orders by most similar first (lowest distance). It's the basis of KNN search with GiST.

Mistake 4 (practical): the default 0.3 threshold doesn't work for your case

Symptom: the % operator returns nothing for queries that look obviously similar ('iphone' % 'aifone').

Why it happens: the similarity of "iphone" and "aifone" is 0.1667, below the 0.3 default. PostgreSQL returns no match.

Fix: adjust the threshold for your case:

-- At the session level
SELECT set_limit(0.2);

-- Or per query: use similarity directly
SELECT title, similarity(title, 'aifone') AS sim
FROM posts
WHERE similarity(title, 'aifone') > 0.15
ORDER BY sim DESC;

Note: lowering the threshold increases the matches but also the false positives. Test with your real data to find the balance.

Mistake 5 (conceptual): thinking pg_trgm is slow for large tables

Symptom: someone says "pg_trgm doesn't scale, better to use Elasticsearch for autocomplete."

Why it's confusing: without an index, it is slow. With a GiST index and LIMIT 10, queries are typically sub-100ms up into the millions of rows.

How to refute it: show an EXPLAIN ANALYZE with a correct index. For LIMIT 10 with GiST KNN search, the plan is a direct Index Scan and the latency is proportional to log(N).

EXPLAIN ANALYZE
SELECT title FROM posts ORDER BY title <-> 'pos' LIMIT 10;

-- Index Scan using idx_posts_title_trgm on posts
--   Order By: (title <-> 'pos'::text)
--   Execution Time: 4.2 ms (on a 1M-row table)

For 100M+ corpora with autocomplete-as-you-type where sub-50ms latency is critical, you may indeed need Elasticsearch or an equivalent. For smaller corpora, pg_trgm covers the case.

Mistake 6 (practical): wrapping everything in lower() "just in case"

Symptom: you copy the WHERE lower(title) % lower(:q) pattern from a tutorial and create the index over lower(title). Everything works… but you just duplicated an index and complicated every query for nothing.

Why it happens: people assume pg_trgm, like LIKE, is case-sensitive. It isn't. pg_trgm lowercases the text before extracting the trigrams. Check it:

SELECT show_trgm('Python') = show_trgm('python');  -- true
SELECT similarity('Python', 'python');             -- 1
SELECT 'PYTHON' % 'python';                        -- true

The trigrams of Python and of python are identical. There's nothing to fix.

Fix: query directly against the column. It's simpler and it uses the index you already have:

CREATE INDEX idx_posts_title_trgm ON posts USING GiST (title gist_trgm_ops);

SELECT title FROM posts WHERE title % 'python';   -- matches "Python", "PYTHON", "python"

What you do have to solve is accents. pg_trgm normalizes case but it does not normalize accents or eñes: similarity('canción', 'cancion') gives 0.45, not 1. If your search engine is in Spanish (and it is), index the already-normalized expression:

-- An index over the accent-free text...
CREATE INDEX idx_posts_title_unaccent_trgm
  ON posts USING GiST (unaccent(title) gist_trgm_ops);

-- ...and query against the SAME expression, or the index won't be used
SELECT title FROM posts WHERE unaccent(title) % unaccent('cancion');

⚠️ To index unaccent(title), PostgreSQL requires the function to be IMMUTABLE, and unaccent() isn't by default (it depends on the dictionary). The usual pattern is to wrap it:

CREATE FUNCTION immutable_unaccent(text) RETURNS text AS $$
  SELECT unaccent('unaccent', $1)
$$ LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE;

and use immutable_unaccent(title) in the index and in the query. It's the same symmetry care from capsule 03: the index's expression and the WHERE's expression have to be identical.

Exercises

Exercise 1: predict the similarity

Without running the SQL, predict whether similarity() will be high (>0.5), medium (0.2-0.5), or low (<0.2):

a) similarity('python', 'pythonista') b) similarity('postgresql', 'mysql') c) similarity('fastapi', 'fast api') d) similarity('javascript', 'java') e) similarity('canción', 'cancion')

SELECT similarity(unaccent('canción'), unaccent('cancion'));
-- 1 (identical)

Exercise 2: implement the "did you mean" pattern

When the user searches for something and FTS returns zero, you want to suggest a single alternative word based on trigram similarity against a dictionary of popular terms.

You have a popular_terms (term TEXT) table with words that are frequent in your corpus. Implement async def suggest(session, q: str) -> str | None that returns the word most similar to q with similarity > 0.4, or None.

from sqlalchemy import desc, func, select


async def suggest(session: AsyncSession, q: str) -> str | None:
    """
    Suggests an alternative word for 'q' based on trigram similarity.
    Returns the most similar one if it clears the threshold, otherwise None.
    """
    similarity = func.similarity(PopularTerm.term, q).label("sim")

    stmt = (
        select(PopularTerm.term, similarity)
        .where(PopularTerm.term.bool_op("%")(q))
        .where(similarity > 0.4)
        .order_by(desc(similarity))
        .limit(1)
    )
    result = await session.execute(stmt)
    row = result.first()
    return row.term if row else None


# Usage
suggestion = await suggest(session, "pythn")
# → "python"

suggestion = await suggest(session, "xyzwq")
# → None

CREATE INDEX idx_popular_terms_trgm ON popular_terms USING GiST (term gist_trgm_ops);

INSERT INTO popular_terms (term)
SELECT word FROM (
  SELECT unnest(tsvector_to_array(tsv)) AS word, count(*) AS freq
  FROM posts
  GROUP BY word
  HAVING count(*) > 10
  ORDER BY freq DESC
  LIMIT 5000
) sub
ON CONFLICT (term) DO NOTHING;

fts_results = await search_fts(session, q)
if not fts_results:
    suggestion = await suggest(session, q)
    if suggestion:
        # Retry with the suggestion
        fts_results = await search_fts(session, suggestion)
        return {"results": fts_results, "did_you_mean": suggestion}

Exercise 3: choose GIN or GiST by scenario

For each case, decide GIN or GiST:

a) A products table (5M rows), fuzzy name search with %, no need to order by similarity. b) A tags table (50k rows), autocomplete with ORDER BY tag <-> 'prefix' LIMIT 10. c) An audit_logs table (200M rows, ~5000 inserts/sec), occasional fuzzy search over the actor_name field. d) A customers table (1M rows), three queries:

• WHERE email % 'q' (binary)
• ORDER BY name <-> 'q' LIMIT 5 (KNN)
• Inserts ~50/min

Exercise 4: combine FTS + pg_trgm with SQLAlchemy

Implement async def search_smart(session, q, limit=20) that:

1. Tries the main FTS with ts_rank_cd.
2. If FTS returns fewer than 5 results, supplements (doesn't replace) with pg_trgm to reach limit.
3. Marks each result with method: "fts" or method: "fuzzy".

from sqlalchemy import desc, func, select
from sqlalchemy.ext.asyncio import AsyncSession


TS_CONFIG = "spanish_unaccent"


async def search_smart(
    session: AsyncSession, q: str, limit: int = 20
) -> list[dict]:
    """
    Main FTS + a fuzzy supplement if the results are scarce.
    """
    tsquery = func.websearch_to_tsquery(TS_CONFIG, q)

    # Step 1: FTS
    rank = func.ts_rank_cd(Post.tsv, tsquery).label("score")
    fts_stmt = (
        select(Post.id, Post.title, rank)
        .where(Post.tsv.bool_op("@@")(tsquery))
        .order_by(desc(rank))
        .limit(limit)
    )
    fts_rows = (await session.execute(fts_stmt)).all()

    fts_results = [
        {
            "id": r.id,
            "title": r.title,
            "score": float(r.score),
            "method": "fts",
        }
        for r in fts_rows
    ]

    # If we have enough, we return
    if len(fts_results) >= 5:
        return fts_results[:limit]

    # Step 2: supplement with fuzzy
    already_ids = [r["id"] for r in fts_results]
    remaining = limit - len(fts_results)

    similarity = func.similarity(Post.title, q).label("score")
    fuzzy_stmt = (
        select(Post.id, Post.title, similarity)
        .where(Post.title.bool_op("%")(q))
        .order_by(desc(similarity))
        .limit(remaining)
    )
    if already_ids:
        fuzzy_stmt = fuzzy_stmt.where(Post.id.notin_(already_ids))

    fuzzy_rows = (await session.execute(fuzzy_stmt)).all()

    fuzzy_results = [
        {
            "id": r.id,
            "title": r.title,
            "score": float(r.score),
            "method": "fuzzy",
        }
        for r in fuzzy_rows
    ]

    return fts_results + fuzzy_results

-- Step 1: FTS
SELECT posts.id, posts.title,
  ts_rank_cd(posts.tsv, websearch_to_tsquery('spanish_unaccent', $1)) AS score
FROM posts
WHERE posts.tsv @@ websearch_to_tsquery('spanish_unaccent', $1)
ORDER BY score DESC
LIMIT 20;

-- Step 2: Fuzzy (only if step 1 returned < 5)
SELECT posts.id, posts.title, similarity(posts.title, $1) AS score
FROM posts
WHERE posts.title % $1
  AND posts.id NOT IN ($2, $3, ...)
ORDER BY score DESC
LIMIT $4;

# In FTS results: score / max_fts_score → between 0 and 1
# In fuzzy results: already between 0 and 1
# Then combine: results.sort(key=lambda r: r["score_normalized"])

Summary and next step

In this capsule you learned to tolerate typos and do autocomplete with pg_trgm:

• Trigrams are sequences of 3 consecutive characters. pg_trgm indexes the text as sets of trigrams and compares them with similarity().
• Main functions: similarity(a, b), word_similarity(word, text), strict_word_similarity(word, text). Associated operators: %, <%, <<%. Inverse distance: <->.
• Indexes: GIN (faster for binary search), GiST (faster for writes, supports KNN with <->). For autocomplete with ordering, GiST.
• The canonical pattern: main FTS + pg_trgm as a fuzzy fallback. FTS for search with stemming/ranking. pg_trgm when FTS returns zero or as a supplement.
• Production-ready autocomplete: combine exact prefix (LIKE with text_pattern_ops) + fuzzy (pg_trgm GiST) for maximum quality and performance.
• pg_trgm normalizes case but does NOT normalize accents. For "canción" vs "cancion" with pg_trgm, apply unaccent first.
• The default 0.3 threshold works for general cases. Adjust it with set_limit() for your corpus.
• pg_trgm doesn't replace FTS. They're tools for different problems (meaning vs syntactic similarity).

Before moving on you should be able to:

• Enable pg_trgm and create GIN/GiST indexes with the right operator class.
• Implement a fuzzy fallback for FTS with SQLAlchemy.
• Build autocomplete combining prefix + fuzzy.
• Decide between GIN and GiST based on the workload.
• Diagnose why pg_trgm doesn't match words with accents.

Next capsule — FTS vs Elasticsearch: when PostgreSQL is enough. Your Blog API has Spanish FTS + ranking + snippets + fuzzy + autocomplete, all in PostgreSQL. But the tech lead asks: "shouldn't we migrate to Elasticsearch to scale?". Capsule 07 gives you the decision matrix with concrete criteria: corpus volume, query complexity, real-time requirements, aggregations, operational cost. You're going to come out able to defend "PostgreSQL FTS is enough for our case" or "we need Elasticsearch" with data, not with an opinion. It's the capsule that closes the module and prepares you for the project.

Resources

1. PostgreSQL 16 — pg_trgm extension — the complete reference with all the functions, operators, and operator classes.
2. PostgreSQL 16 — KNN-GiST indexing — how <-> works with GiST for nearest-neighbor search.
3. Crunchy Data — "Fuzzy Name Matching in Postgres" — real use cases of pg_trgm for deduplication and matching.
4. Hubert "depesz" Lubaczewski — "Speedup pg_trgm operator class" — a performance analysis of the operator classes and cases where one beats the other.
5. Lukas Fittl (pganalyze) — "Trigram Indexes in PostgreSQL" — a practical guide with real benchmarks.
6. PostgreSQL 16 — text_pattern_ops — the operator class that lets a B-tree speed up LIKE 'prefix%'.

Module 3 — Advanced PostgreSQL for Backend Guide

Next capsule: FTS vs Elasticsearch — the decision matrix you defend in a technical meeting.