`pg_trgm` advanced cases: deduplication, "did you mean"

You already saw pg_trgm in module 3 for fuzzy full-text search. But the extension has use cases beyond typical search. This capsule covers two applications many devs don't know about: deduplication of similar records ("does this record already exist with another spelling?") and "did you mean" suggestions (smart autocomplete).

---

Recap: how pg_trgm works

Quick recap from module 3:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- similarity(): how similar two strings are (0-1)
SELECT similarity('PostgreSQL', 'Postgres');
-- 0.461538

-- % operator: similarity > threshold
SELECT 'PostgreSQL' % 'Postgres';
-- true (default threshold 0.3)

-- GIN index for fast fuzzy queries
CREATE INDEX idx_name_trgm ON things USING gin (name gin_trgm_ops);

Trigrams are windows of 3 characters. 'Hello' decomposes into ' H', ' He', 'Hel', 'ell', 'llo', 'lo ', 'o '. Compare trigrams between strings → similarity score.

---

Case 1: record deduplication

You have a customers table. You imported data from several sources. There are duplicates with different spelling:

"John Smith"
"John A. Smith"
"Jhon Smith"
"John Smith Jr."
"john smith"

Are they the same person? Possibly yes. But you can't SELECT DISTINCT because the strings differ.

Approach: similarity-based clustering

-- Find pairs with high similarity
SELECT
    a.id, a.name AS name_a,
    b.id, b.name AS name_b,
    similarity(a.name, b.name) AS sim
FROM customers a
JOIN customers b ON a.id < b.id  -- Avoid duplicates A-B / B-A
WHERE similarity(a.name, b.name) > 0.6  -- High similarity threshold
ORDER BY sim DESC;

Output:

 id_a | name_a            | id_b | name_b              | sim
------+-------------------+------+---------------------+------
   1  | John Smith        |   2  | John A. Smith       | 0.78
   1  | John Smith        |   3  | Jhon Smith          | 0.72
   2  | John A. Smith     |   3  | Jhon Smith          | 0.65
   1  | John Smith        |   5  | john smith          | 1.00 (case-insensitive)

Pairs with high similarity are candidates for dedup.

Dedup pipeline

async def find_duplicates(session, threshold: float = 0.7):
    """Find pairs of customers with similar names."""
    result = await session.execute(text("""
        SELECT
            a.id AS id_a, a.name AS name_a,
            b.id AS id_b, b.name AS name_b,
            similarity(a.name, b.name) AS sim
        FROM customers a
        JOIN customers b ON a.id < b.id
        WHERE similarity(a.name, b.name) > :threshold
        ORDER BY sim DESC
    """), {"threshold": threshold})

    return result.mappings().all()


# Usage
duplicates = await find_duplicates(session, threshold=0.7)
for d in duplicates:
    print(f"Possible duplicate: {d['name_a']} ↔ {d['name_b']} (sim={d['sim']:.2f})")

Manual client review or auto-merge depending on the threshold.

Performance: GIN index required

Without an index, the dedup query is O(n²). With a GIN index, much faster:

CREATE INDEX idx_customers_name_trgm ON customers USING gin (name gin_trgm_ops);

Plan with the index:

Hash Join
  ->  Bitmap Heap Scan on customers a
  ->  Bitmap Index Scan on idx_customers_name_trgm
        Index Cond: (name % b.name)

The GIN index lets you filter out unlikely pairs quickly.

Threshold tuning

-- Increase the global threshold
SET pg_trgm.similarity_threshold = 0.5;

-- Default is 0.3

0.6-0.7 is a typical threshold for "probable duplicate" without too many false positives.

---

Case 2: "did you mean" suggestions

A user searches for "Postgres" but types "Postres". How do you suggest the correct one?

-- Find closest matches by trigram similarity
SELECT name, similarity(name, 'Postres') AS sim
FROM products
WHERE name % 'Postres'
ORDER BY sim DESC
LIMIT 5;

Output:

 name        | sim
-------------+------
 Postgres    | 0.71
 Posters     | 0.43
 Posts       | 0.40

Suggest the top result: "Did you mean Postgres?"

In a FastAPI endpoint

@router.get("/search")
async def search_with_suggestions(
    q: str,
    db: AsyncSession = Depends(get_db),
):
    # Exact search first
    exact_results = await db.execute(
        text("SELECT id, name FROM products WHERE name ILIKE :q"),
        {"q": f"%{q}%"}
    )
    exact = exact_results.mappings().all()

    # If few results, add suggestions
    suggestions = []
    if len(exact) < 3:
        sug_results = await db.execute(text("""
            SELECT id, name, similarity(name, :q) AS sim
            FROM products
            WHERE name % :q AND name NOT ILIKE :pattern
            ORDER BY sim DESC
            LIMIT 5
        """), {"q": q, "pattern": f"%{q}%"})

        suggestions = sug_results.mappings().all()

    return {
        "results": list(exact),
        "did_you_mean": [s["name"] for s in suggestions] if suggestions else None,
    }

The client receives:

{
  "results": [],
  "did_you_mean": ["Postgres", "Posters", "Posts"]
}

---

Case 3: smart autocomplete

A user types "Postgr" in the search box. Show suggestions as they type:

-- Combine prefix match (fast) + similarity (catch typos)
SELECT name, COUNT(*) OVER () AS total
FROM products
WHERE name ILIKE 'Postgr%'  -- Exact prefix, uses B-tree index
   OR name % 'Postgr'        -- Similarity, uses GIN trigram
ORDER BY
    CASE WHEN name ILIKE 'Postgr%' THEN 0 ELSE 1 END,  -- Exact prefix first
    similarity(name, 'Postgr') DESC
LIMIT 10;

It combines:

• Prefix match (typically what the user wants).
• Similarity (catch typos).
• Order: prefix matches first, then similarity desc.

Performance: composite index

-- For the combination, two indexes
CREATE INDEX idx_products_name ON products (name);
CREATE INDEX idx_products_name_trgm ON products USING gin (name gin_trgm_ops);

PostgreSQL picks the right one based on the query.

---

Case 4: deduplicating emails with common typos

-- Emails like "user@gmial.com" (typo of gmail)
SELECT
    a.email AS email_a,
    b.email AS email_b,
    similarity(a.email, b.email) AS sim
FROM users a
JOIN users b ON a.id < b.id
WHERE similarity(a.email, b.email) > 0.85  -- High threshold for emails
  AND a.email <> b.email  -- Exclude exact match
ORDER BY sim DESC;

gmial.com and gmail.com: sim ~ 0.88. Identifiable.

Combine with domain extraction

SELECT
    email,
    split_part(email, '@', 1) AS local_part,
    split_part(email, '@', 2) AS domain
FROM users;

-- Find duplicates with the same local_part but similar domain
WITH parts AS (
    SELECT id, email,
           split_part(email, '@', 1) AS local,
           split_part(email, '@', 2) AS domain
    FROM users
)
SELECT a.email, b.email, similarity(a.domain, b.domain) AS domain_sim
FROM parts a
JOIN parts b ON a.id < b.id
WHERE a.local = b.local  -- Same local part
  AND similarity(a.domain, b.domain) > 0.85
  AND a.domain <> b.domain;

Detects typos in the domain while keeping the local part.

---

Case 5: clustering products by name

E-commerce with 1M products. Some are nearly identical with spelling differences:

-- Cluster using self-join + threshold
WITH similar_pairs AS (
    SELECT
        a.id AS id_a, b.id AS id_b,
        similarity(a.name, b.name) AS sim
    FROM products a
    JOIN products b ON a.id < b.id AND similarity(a.name, b.name) > 0.8
)
SELECT * FROM similar_pairs
ORDER BY sim DESC
LIMIT 100;

Manual review or auto-merge based on the sim threshold.

Performance considerations

Self-join with a high threshold:

• > 0.8: few pairs, fast query (seconds for 1M rows).
• > 0.5: many pairs, slow query (minutes).

For large datasets, batch processing:

async def dedup_in_batches(session, batch_size: int = 10_000):
    """Process customers in batches, find duplicates per batch."""
    offset = 0
    while True:
        # Get batch
        result = await session.execute(
            text("SELECT id, name FROM customers ORDER BY id LIMIT :lim OFFSET :off"),
            {"lim": batch_size, "off": offset}
        )
        batch = result.mappings().all()
        if not batch:
            break

        # Find duplicates within batch + against whole table
        ids = [r["id"] for r in batch]
        dups = await session.execute(text("""
            SELECT a.id, a.name, b.id, b.name, similarity(a.name, b.name) AS sim
            FROM customers a
            JOIN customers b ON a.id <> b.id
            WHERE a.id = ANY(:ids)
              AND similarity(a.name, b.name) > 0.8
        """), {"ids": ids})

        for d in dups:
            print(f"Dup: {d}")

        offset += batch_size

---

Traps and common mistakes

1. Without a GIN index, dedup queries are O(n²) — minutes on large datasets.

Always create CREATE INDEX ... USING gin (col gin_trgm_ops) before running dedup queries.

2. Threshold too low: many false positives.

> 0.3 (default) catches almost anything similar. For dedup, 0.7+ is typical.

3. Threshold too high: missing real duplicates.

> 0.95 only catches near-exact matches. Misses significant typos.

4. Comparing strings of very different lengths.

similarity("a", "abcdefg") is low even though "a" is contained. Trigram similarity assumes strings of comparable length.

5. Using similarity in critical queries without caching.

The similarity function is deterministic — cache the results in Redis if the rate is high.

6. Forgetting to combine with exact search.

-- Exact + similarity, exact first
WHERE name ILIKE 'Postg%' OR name % 'Postg'
ORDER BY (name ILIKE 'Postg%') DESC, similarity(name, 'Postg') DESC;

Better UX.

7. "Did you mean" without excluding the original query.

-- ❌ If the user searched "Postres" and it exists (rare but possible), it shows up as a suggestion
WHERE name % 'Postres'

-- ✅ Exclude exact matches
WHERE name % 'Postres' AND name NOT ILIKE 'Postres'

8. Performance assumption: trigram equals FTS.

Trigram (pg_trgm) and FTS (tsvector) are different:

• Trigram: typo-tolerant, similarity scoring.
• FTS: stemming, language-aware, ranking.

Combining both gives better search. Capsule 03 of module 3 already covered FTS.

---

Exercise: implement a dedup pipeline

Setup:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    email VARCHAR(200)
);

CREATE INDEX idx_customers_name_trgm ON customers USING gin (name gin_trgm_ops);

-- Insert data with duplicates
INSERT INTO customers (name, email) VALUES
    ('John Smith', 'john@example.com'),
    ('John A. Smith', 'john.smith@example.com'),
    ('Jhon Smith', 'jhon@example.com'),
    ('john smith', 'john2@example.com'),
    ('Jane Doe', 'jane@example.com'),
    ('Jane M. Doe', 'jane.doe@example.com'),
    ('Different Person', 'different@example.com');

Step 1: run the dedup query.

SELECT
    a.name AS name_a,
    b.name AS name_b,
    similarity(a.name, b.name) AS sim
FROM customers a
JOIN customers b ON a.id < b.id
WHERE similarity(a.name, b.name) > 0.5
ORDER BY sim DESC;

Step 2: experiment with different thresholds.

-- High threshold: stricter
WHERE similarity(a.name, b.name) > 0.8

-- Low threshold: looser
WHERE similarity(a.name, b.name) > 0.4

Which ones appear at each level?

Step 3: implement "did you mean" in Python.

async def search_with_did_you_mean(session, q: str):
    # Exact match
    exact = await session.execute(
        text("SELECT name FROM customers WHERE name ILIKE :p"),
        {"p": f"%{q}%"}
    )
    exact_results = [r[0] for r in exact]

    # Suggestions
    suggestions = []
    if len(exact_results) < 3:
        sug = await session.execute(
            text("""
                SELECT name, similarity(name, :q) AS sim
                FROM customers
                WHERE name % :q AND name NOT ILIKE :p
                ORDER BY sim DESC
                LIMIT 3
            """),
            {"q": q, "p": f"%{q}%"}
        )
        suggestions = [r[0] for r in sug]

    return {"results": exact_results, "did_you_mean": suggestions}


# Test
result = await search_with_did_you_mean(session, "Jhon")
print(result)
# Expected: results=[], did_you_mean=['John Smith', 'John A. Smith', 'john smith']

Step 4: implement auto-merge.

If two rows have sim > 0.95, treat them as exact duplicates:

async def find_exact_duplicates(session):
    """Find ROWS that are very likely the SAME entity."""
    result = await session.execute(text("""
        SELECT
            a.id, a.name, a.email,
            b.id, b.name, b.email,
            similarity(a.name, b.name) AS sim
        FROM customers a
        JOIN customers b ON a.id < b.id
        WHERE similarity(a.name, b.name) > 0.95
        ORDER BY sim DESC
    """))

    return result.mappings().all()


dups = await find_exact_duplicates(session)
for d in dups:
    print(f"Likely same: '{d['name']}' (id={d['id']}) and '{d['name_1']}' (id={d['id_1']})")

See discussion

Step 1: the dedup query finds 4 pairs with sim > 0.5 — the similar Johns/Joneses.

Step 2: threshold 0.8 catches only "John Smith"/"John A. Smith"/"john smith" (case insensitive matches). Threshold 0.4 catches more, possibly false positives.

Step 3: "did you mean" returns useful suggestions for typos.

Step 4: sim > 0.95 catches near-exact (mostly case-only differences).

Key takeaways:

1. pg_trgm extends far beyond fuzzy search.
2. Threshold tuning is key to useful dedup.
3. Combining exact + similarity = better UX.
4. GIN index required for performance.

---

Summary and next step

What you learned:

• pg_trgm extends fuzzy search to cases like dedup and "did you mean".
• Dedup: self-join with similarity > threshold. Threshold 0.7+ typical.
• "Did you mean": combine exact match with similarity matching.
• Autocomplete: prefix match + similarity for typos.
• GIN index required for performance.
• Traps: threshold tuning, unequal lengths, performance without an index.

In the next capsule we briefly mention large extensions that belong to other guides: pgcrypto (auth), postgis (geospatial), pgvector (AI/embeddings). Without going deep — just so you know they exist and when to redirect.

---

Resources

1. PostgreSQL Docs — pg_trgm — reference.
2. Crunchy Data — Fuzzy text search — practical cases.
3. Postgres trigram similarity — available functions.
4. Levenshtein distance vs trigram — comparison.

---

Capsule 06 of 08 — Module 7 — Advanced PostgreSQL for Backend Guide