What is pg_trgm? (The Simple Version)

pg_trgm is a tool that PostgreSQL offers to help you find similar words or phrases. It works by breaking down words into tiny pieces—just three letters at a time—and then comparing these pieces between different words.

Think of it like this: if you're looking for "hello" but accidentally type "helo", the tool can still find what you want because both words share most of the same three-letter chunks.

How Trigrams Work: Breaking Words Into Pieces

A trigram is simply three letters in a row. Let's see how the word "hello" becomes trigrams:

"hello" → [" h", "he", "el", "ll", "lo", "o "]

Notice we added spaces at the start and end? That's on purpose—it helps the tool match better.

When you search for something, pg_trgm counts how many three-letter pieces are the same between your search and the words in the database. The more pieces that match, the more similar the words are.

Example:

• "hello" and "hallo" share 4 out of 6 trigrams → High similarity (good match!)
• "hello" and "world" share only 0 trigrams → No similarity (not a match)

Turning On pg_trgm in Your Database

First, you need to tell PostgreSQL to use this tool. It's a one-line command:

CREATE EXTENSION pg_trgm;

If you're using Django, there's an even easier way. Create a migration file:

In your migration file:

from django.contrib.postgres.operations import TrigramExtension

class Migration(migrations.Migration):
    operations = [TrigramExtension()]

Then run:

python manage.py migrate

Done! Now your database is ready to use trigram searching.

Main Functions: What Can pg_trgm Do?

similarity(word1, word2) - Gives a score from 0 to 1 showing how similar two words are

SELECT similarity('hello', 'helo'); -- Returns something like 0.75

word_similarity(word1, word2) - Similar to above, but compares whole words instead of all letters

SELECT word_similarity('the quick brown fox', 'brown'); -- Returns 0.5

The % symbol - Finds records that are similar to what you're searching for

SELECT * FROM products WHERE name % 'iphon';
-- This will find products like 'iPhone', 'iphone', 'Iphone'

Real-Life Uses For pg_trgm

1. Forgiving Search (Typo Tolerance)

Your user types "samsng phone" instead of "samsung phone"? No problem! pg_trgm will still find the right product.

2. Auto-Complete While Typing

As a user types a product name, show suggestions that get better with each letter. This is super fast with pg_trgm.

3. Finding Duplicate Records

If you have a messy database with entries like "Jon Doe", "John Doe", and "Jon D.", you can find these similar names and clean them up.

4. Better User Experience

Users get results even when they're not sure about exact spelling. Your app feels smart and helpful.

Speed It Up: Using Indexes

Searching through millions of records can be slow. PostgreSQL has a trick: indexes. Think of it like a library's card catalog—it helps you find books faster.

pg_trgm supports two types of indexes:

GIN Index - Better for:

• Large, unchanging datasets
• Faster searches
• Takes longer to build

CREATE INDEX product_name_gin ON products USING gin (name gin_trgm_ops);

GiST Index - Better for:

• Data that changes often
• Slightly slower searches
• Faster to update

CREATE INDEX product_name_gist ON products USING gist (name gist_trgm_ops);

Which one should you pick? In most cases, use GIN if your data doesn't change much (like product catalogs). Use GiST if you're always adding or updating data.

Real Examples You Can Try

Let's say you have a products table:

-- Find products similar to 'iPhone'
SELECT name FROM products WHERE name % 'iPhone';

-- Show the similarity score
SELECT name, similarity(name, 'iPhone') as match_score 
FROM products 
WHERE name % 'iPhone'
ORDER BY match_score DESC;

-- Show all three-letter chunks of a word
SELECT show_trgm('Samsung');
-- Returns: {" s", " sa, "am", "ms", "sa ", "sam", "sun", "ung"}

Fine-Tuning: The Similarity Score

By default, pg_trgm says two words are "similar" if they match 30% or more. You can change this:

SET pg_trgm.similarity_threshold = 0.2;
-- Now 20% match is "good enough"

SET pg_trgm.similarity_threshold = 0.5;
-- Now 50% match is needed (stricter)

Lower number = More results but maybe less accurate

Higher number = Fewer results but all very close matches

Using pg_trgm in Django

Step 1: Add PostgreSQL Support

Make sure this is in your settings.py:

INSTALLED_APPS = [
    # ... other apps
    'django.contrib.postgres',
]

Step 2: Import the Tools

from django.contrib.postgres.search import TrigramSimilarity

Step 3: Use in Your Views

Simple fuzzy search:

from django.contrib.postgres.search import TrigramSimilarity

def search_products(request):
    query = request.GET.get('q', '')
    
    products = Product.objects.annotate(
        similarity_score=TrigramSimilarity('name', query)
    ).filter(similarity_score__gt=0.3).order_by('-similarity_score')
    
    return render(request, 'products.html', {'products': products})

This code:

1. Takes the search term from the user
2. Compares it to every product name
3. Keeps only products that are 30% similar
4. Sorts by how similar they are (best matches first)

More advanced: Search multiple fields

def search_products(request):
    query = request.GET.get('q', '')
    
    products = Product.objects.annotate(
        name_similarity=TrigramSimilarity('name', query),
        desc_similarity=TrigramSimilarity('description', query),
        total_similarity=TrigramSimilarity('name', query) + TrigramSimilarity('description', query)
    ).filter(total_similarity__gt=0.3).order_by('-total_similarity')
    
    return render(request, 'products.html', {'products': products})

A Complete Example: Building a Product Search

Let's build a simple product search from start to finish.

Your model (models.py):

from django.db import models

class Product(models.Model):
    name = models.CharField(max_length=200)
    description = models.TextField()
    price = models.DecimalField(max_digits=10, decimal_places=2)

    def __str__(self):
        return self.name

Your view (views.py):

from django.shortcuts import render
from django.contrib.postgres.search import TrigramSimilarity
from .models import Product

def search(request):
    query = request.GET.get('search', '')
    results = []
    
    if query:
        results = Product.objects.annotate(
            similarity=TrigramSimilarity('name', query)
        ).filter(
            similarity__gt=0.2
        ).order_by('-similarity')
    
    return render(request, 'search.html', {
        'results': results,
        'query': query
    })

Your template (search.html):

<h1>Search Products</h1>
<form method="get">
    <input type="text" name="search" value="{{ query }}" placeholder="Type product name...">
    <button type="submit">Search</button>
</form>

{% if results %}
    <h2>Found {{ results|length }} products:</h2>
    <ul>
    {% for product in results %}
        <li>
            <strong>{{ product.name }}</strong> - ${{ product.price }}
            <p>{{ product.description }}</p>
        </li>
    {% endfor %}
    </ul>
{% elif query %}
    <p>No products found for "{{ query }}"</p>
{% endif %}

Things to Keep in Mind (Limitations)

1. It's Not Perfect for Long Text

If you're searching through long articles or documents, full-text search (a different tool) might work better.

2. You Can't Sort by Similarity Without Extra Work

If you use a GIN index, the database won't automatically sort by similarity. You'll need to do it in Python or use extra tools.

3. You Need Special Permissions

Sometimes you need database admin (superuser) access to turn on pg_trgm. If you can't do it yourself, ask your database administrator.

4. Database Size

Trigram indexes take up space. For very large tables, make sure you have enough disk space.

pg_trgm vs Full-Text Search: Which Should You Use?

Simple rule: Use pg_trgm when you want forgiving, typo-friendly search. Use full-text search when you want smart, meaning-aware search.

Final Thoughts

pg_trgm is like giving your search superpowers. Your users type "samsng" and find Samsung phones. They type "mcdonalds" and find McDonald's. That's the magic of trigrams—they focus on what words have in common, not what makes them different.

With Django's built-in tools, adding this kind of smart search to your app is easier than you might think. Start small, test it out, and watch your users love the improved search experience.

Happy searching! 🚀