Recipe search in a Django app misses misspellings like 'lazagna'; how do you add typo-tolerant matching with trigram_similar or TrigramSimilarity, and index it?

seniornice to knowfreq 24%

answer

characters, not words
an extension first
operator lookup versus annotated score
an operator class on the index
argument order differs for word similarity

basics

~20 s

Create pg_trgm with TrigramExtension, filter with title__trigram_similar and order by a TrigramSimilarity annotation. Add a GinIndex with the gin_trgm_ops operator class; the lookup's operator can use it, while filtering on an annotated similarity score cannot.

solid answer

~30 s

Full-text search matches stemmed words, so 'lazagna' never meets 'lasagna'. Trigram similarity compares three-character chunks instead. Add TrigramExtension() in a migration and keep 'django.contrib.postgres' in INSTALLED_APPS. Recipe.objects.filter(title__trigram_similar=q) compiles to pg_trgm's % operator, which uses PostgreSQL's similarity threshold setting; annotate(sim=TrigramSimilarity('title', q)).order_by('-sim') gives a score to sort by. Index the column with GinIndex(OpClass('title', name='gin_trgm_ops'), name='recipe_title_trgm'): the % operator can use it, but filter(sim__gt=0.3) on the annotated function cannot, so filter with the lookup and rank with the function. For a term inside long titles, use trigram_word_similar or TrigramWordSimilarity(q, 'title'), whose arguments come in the reverse order.

Full-text search matches stemmed words, so 'lazagna' never meets 'lasagna'. Trigram similarity compares three-character chunks instead. Add `TrigramExtension()` in a migration and keep `'django.contrib.postgres'` in `INSTALLED_APPS`. `Recipe.objects.filter(title__trigram_similar=q)` compiles to pg_trgm's `%` operator, which uses PostgreSQL's similarity threshold setting; `annotate(sim=TrigramSimilarity('title', q)).order_by('-sim')` gives a score to sort by. Index the column with `GinIndex(OpClass('title', name='gin_trgm_ops'), name='recipe_title_trgm')`: the `%` operator can use it, but `filter(sim__gt=0.3)` on the annotated function cannot, so filter with the lookup and rank with the function. For a term inside long titles, use `trigram_word_similar` or `TrigramWordSimilarity(q, 'title')`, whose arguments come in the reverse order.

code

python · 20 lines

python

from django.contrib.postgres.indexes import GinIndex, OpClass
from django.contrib.postgres.search import TrigramSimilarity
from django.db import models


class Recipe(models.Model):
    title = models.CharField(max_length=200)

    class Meta:
        indexes = [
            GinIndex(OpClass('title', name='gin_trgm_ops'), name='recipe_title_trgm'),
        ]


def fuzzy_titles(q):
    return (
        Recipe.objects.filter(title__trigram_similar=q)
        .annotate(sim=TrigramSimilarity('title', q))
        .order_by('-sim')[:10]
    )

go deeper

for a junior

Know that trigram_similar finds near-matches such as typos, and that it needs the pg_trgm extension via TrigramExtension.

for a middle

Explain the lookup versus the TrigramSimilarity function, the reversed argument order of the word variants, and the gin_trgm_ops index.

for a senior

Design a search path that filters with the indexable operator, ranks survivors, falls back from full-text search, and tunes the threshold where it lives.

for a principal

Decide when fuzzy matching in PostgreSQL is enough and when typo tolerance and relevance justify a separate search engine and its sync cost.

Why full-text search misses typos

PostgreSQL full-text search reduces text to lexemes, stemmed whole words. A misspelled query word becomes a different lexeme, so 'lazagna' matches nothing even though a human sees the recipe at once. Trigram similarity instead splits strings into overlapping three-character chunks and measures how many they share, which survives a wrong letter or two.

Setting it up

Keep 'django.contrib.postgres' in INSTALLED_APPS; its ready() hook registers the trigram lookups on CharField and TextField.
Create the pg_trgm extension with TrigramExtension() in a migration, before any index that uses its operator classes.
Add an index with a trigram operator class so the lookup does not scan the table.

The Django API

API	Kind	SQL	Use
`title__trigram_similar=q`	lookup	`%`	filter, threshold from PostgreSQL's setting
`title__trigram_word_similar=q`	lookup	`%>`	q resembles some part of a longer title
`title__trigram_strict_word_similar=q`	lookup	`%>>`	as above, matching word boundaries
`TrigramSimilarity('title', q)`	function (expression, string)	`similarity()`	a score to annotate and order by
`TrigramWordSimilarity(q, 'title')`	function (string, expression)	`word_similarity()`	a score for part-of-title matches
`TrigramDistance('title', q)`	function	`<->`	1 minus similarity

Watch the argument order: TrigramSimilarity takes the field first, while TrigramWordSimilarity and TrigramStrictWordSimilarity take the search string first. Swapping them compiles without error and returns quietly wrong scores.

Lookup versus annotation, and the index

The two common query shapes behave very differently at scale:

python

# Shape 1: annotate a score and filter on it
Recipe.objects.annotate(sim=TrigramSimilarity('title', q)).filter(sim__gt=0.3)

# Shape 2: filter with the operator lookup, then rank
Recipe.objects.filter(title__trigram_similar=q).annotate(
    sim=TrigramSimilarity('title', q)
).order_by('-sim')

Shape 1 lets you choose the threshold in code, but PostgreSQL must compute similarity() for every row: a function result compared with a number cannot use a trigram index.
Shape 2 filters with the % operator, which a GIN or GiST index built with a trigram operator class can serve, then scores only the rows that passed. The threshold is PostgreSQL's pg_trgm similarity setting, adjustable per session or role, not a Django argument.

The index needs the operator class, which Django expresses in two ways; both need a name, and an unnamed index with opclasses raises ValueError:

python

GinIndex(OpClass('title', name='gin_trgm_ops'), name='recipe_title_trgm')
GinIndex(fields=['title'], opclasses=['gin_trgm_ops'], name='recipe_title_trgm')

A GistIndex with gist_trgm_ops also works and can additionally help ordering by TrigramDistance; GIN is the usual choice for filtering.

Combining with full-text search

Trigram and full-text search answer different questions:

Full-text search: which recipes are about these words, ranked by where the words appear.
Trigram: which titles look like this string, typos included.

A common pattern for a recipe search box is to run the full-text query first and, when it returns nothing, fall back to trigram_similar on the title with a 'Did you mean...' list. Running trigram over long bodies such as the method text is rarely useful: long texts share many trigrams with almost anything.

Choosing the threshold and checking the plan

Threshold and index choices are empirical, so work from real data:

Collect genuine misspellings from search logs, such as 'lazagna', 'brocoli' and 'tiramisou', together with the recipe each one should find.
In a shell, annotate TrigramSimilarity('title', q) for those queries and look at where the intended recipe lands and which unrelated titles score close to it.
Set the database's similarity threshold just below the lowest score you want to accept, and confirm the false positives it lets in are tolerable.
Run queryset.explain() on the final query and check that PostgreSQL uses the trigram index rather than a sequential scan; if it does not, the query shape or the operator class is wrong.

Short queries are the weak spot: a three-letter search has very few trigrams, so its similarity to many titles is low or erratic. Many search boxes skip fuzzy matching below a minimum query length and use a prefix match instead.

Accents

The unaccent lookup (with UnaccentExtension()) is a transform, so it chains: title__unaccent__trigram_similar='creme brulee'. Such a chain changes the expression, so a plain trigram index on title no longer applies, and Django's documentation warns that unaccent filters generally scan the table.

## Why full-text search misses typos PostgreSQL full-text search reduces text to **lexemes**, stemmed whole words. A misspelled query word becomes a different lexeme, so 'lazagna' matches nothing even though a human sees the recipe at once. **Trigram similarity** instead splits strings into overlapping three-character chunks and measures how many they share, which survives a wrong letter or two. ## Setting it up 1. Keep `'django.contrib.postgres'` in `INSTALLED_APPS`; its `ready()` hook registers the trigram lookups on `CharField` and `TextField`. 2. Create the `pg_trgm` extension with `TrigramExtension()` in a migration, before any index that uses its operator classes. 3. Add an index with a trigram operator class so the lookup does not scan the table. ## The Django API | API | Kind | SQL | Use | |---|---|---|---| | `title__trigram_similar=q` | lookup | `%` | filter, threshold from PostgreSQL's setting | | `title__trigram_word_similar=q` | lookup | `%>` | q resembles some part of a longer title | | `title__trigram_strict_word_similar=q` | lookup | `%>>` | as above, matching word boundaries | | `TrigramSimilarity('title', q)` | function (expression, string) | `similarity()` | a score to annotate and order by | | `TrigramWordSimilarity(q, 'title')` | function (string, expression) | `word_similarity()` | a score for part-of-title matches | | `TrigramDistance('title', q)` | function | `<->` | 1 minus similarity | Watch the **argument order**: `TrigramSimilarity` takes the field first, while `TrigramWordSimilarity` and `TrigramStrictWordSimilarity` take the search string first. Swapping them compiles without error and returns quietly wrong scores. ## Lookup versus annotation, and the index The two common query shapes behave very differently at scale: ```python # Shape 1: annotate a score and filter on it Recipe.objects.annotate(sim=TrigramSimilarity('title', q)).filter(sim__gt=0.3) # Shape 2: filter with the operator lookup, then rank Recipe.objects.filter(title__trigram_similar=q).annotate( sim=TrigramSimilarity('title', q) ).order_by('-sim') ``` - Shape 1 lets you choose the threshold in code, but PostgreSQL must compute `similarity()` for every row: a function result compared with a number cannot use a trigram index. - Shape 2 filters with the `%` operator, which a GIN or GiST index built with a trigram operator class can serve, then scores only the rows that passed. The threshold is PostgreSQL's pg_trgm similarity setting, adjustable per session or role, not a Django argument. The index needs the operator class, which Django expresses in two ways; both need a `name`, and an unnamed index with `opclasses` raises `ValueError`: ```python GinIndex(OpClass('title', name='gin_trgm_ops'), name='recipe_title_trgm') GinIndex(fields=['title'], opclasses=['gin_trgm_ops'], name='recipe_title_trgm') ``` A `GistIndex` with `gist_trgm_ops` also works and can additionally help ordering by `TrigramDistance`; GIN is the usual choice for filtering. ## Combining with full-text search Trigram and full-text search answer different questions: - Full-text search: which recipes are **about** these words, ranked by where the words appear. - Trigram: which titles **look like** this string, typos included. A common pattern for a recipe search box is to run the full-text query first and, when it returns nothing, fall back to `trigram_similar` on the title with a 'Did you mean...' list. Running trigram over long bodies such as the method text is rarely useful: long texts share many trigrams with almost anything. ## Choosing the threshold and checking the plan Threshold and index choices are empirical, so work from real data: 1. Collect genuine misspellings from search logs, such as 'lazagna', 'brocoli' and 'tiramisou', together with the recipe each one should find. 2. In a shell, annotate `TrigramSimilarity('title', q)` for those queries and look at where the intended recipe lands and which unrelated titles score close to it. 3. Set the database's similarity threshold just below the lowest score you want to accept, and confirm the false positives it lets in are tolerable. 4. Run `queryset.explain()` on the final query and check that PostgreSQL uses the trigram index rather than a sequential scan; if it does not, the query shape or the operator class is wrong. Short queries are the weak spot: a three-letter search has very few trigrams, so its similarity to many titles is low or erratic. Many search boxes skip fuzzy matching below a minimum query length and use a prefix match instead. ## Accents The `unaccent` lookup (with `UnaccentExtension()`) is a transform, so it chains: `title__unaccent__trigram_similar='creme brulee'`. Such a chain changes the expression, so a plain trigram index on `title` no longer applies, and Django's documentation warns that `unaccent` filters generally scan the table.

Why is TrigramWordSimilarity('title', q) a bug when TrigramSimilarity('title', q) is correct?
`TrigramWordSimilarity` takes `(string, expression)`: the search text first, then the field. Passing the field first asks how well the whole title fits inside the search text, which scores short titles oddly and ignores long ones. It raises no error, so it only shows up as poor ranking; write `TrigramWordSimilarity(q, 'title')`.
How do you tune how loose trigram_similar is?
The lookup compiles to pg_trgm's `%` operator, which compares against PostgreSQL's similarity threshold setting, so tune it on the database side for the session or role. If you need a per-query threshold in Django code, annotate `TrigramSimilarity` and filter on it, accepting that this filter cannot use the trigram index; combining both, lookup first then a stricter score filter, keeps the index.

saying these in an interview costs you the question

Full-text search already handles misspellings through stemming.
filter(sim__gt=0.3) on a TrigramSimilarity annotation uses the trigram index.
A plain GinIndex(fields=['title']) without an operator class serves trigram_similar.
trigram_similar accepts a threshold argument in Django.
TrigramWordSimilarity takes the field first, like TrigramSimilarity.

Recipe search in a Django app misses misspellings like 'lazagna'; how do you add typo-tolerant matching with trigram_similar or TrigramSimilarity, and index it?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

Why full-text search misses typos

Setting it up

The Django API

Lookup versus annotation, and the index

Combining with full-text search

Choosing the threshold and checking the plan

Accents

▾common follow-ups2 questions

▾red flags5 flags

Why full-text search misses typos

Setting it up

The Django API

Lookup versus annotation, and the index

Combining with full-text search

Choosing the threshold and checking the plan

Accents

answer

go deeper