Add full-text search with PostgreSQL

Approach

Store a precomputed SearchVector in a generated column with a GIN index, query it with SearchQuery (web search syntax), and order by SearchRank.

Solution

from django.contrib.postgres.indexes import GinIndex
from django.contrib.postgres.search import SearchQuery, SearchRank, SearchVector, SearchVectorField
from django.db import models


class Article(models.Model):
    title = models.CharField(max_length=200)
    body = models.TextField()
    search = models.GeneratedField(
        expression=SearchVector("title", weight="A", config="english")
        + SearchVector("body", weight="B", config="english"),
        output_field=SearchVectorField(),
        db_persist=True,
    )

    class Meta:
        indexes = [GinIndex(fields=["search"])]


def search_articles(text: str):
    query = SearchQuery(text, search_type="websearch", config="english")
    return (
        Article.objects.filter(search=query)
        .annotate(rank=SearchRank("search", query))
        .order_by("-rank")[:20]
    )

Pitfalls

• Computing SearchVector in every query instead of storing and indexing it.
• Mixing text search configurations (language) between the column and the query.
• Expecting typo tolerance; full-text search matches stems, not misspellings (use trigram similarity for that).

Variations

• Add TrigramSimilarity (pg_trgm) for fuzzy matching on titles.
• Move to a search engine (OpenSearch, Meilisearch) for facets and typo tolerance at scale.

Further reading: PostgreSQL full-text search