skip to content

In Django, how do you build ranked full-text search over a recipe collection with SearchVector, SearchQuery and SearchRank, and keep it fast?

level: middleimportance: should knowfreq 42%

answer

  1. document, query, score
  2. weights A to D per field
  3. websearch for user input
  4. annotating recomputes every row
  5. stored vector plus a GIN index

basics

~20 s

Combine weighted SearchVector fields, filter them with a SearchQuery and order by SearchRank. Annotating the vector recomputes it for every row, so store it in a GeneratedField or SearchVectorField with a GinIndex, or index the same SearchVector expression.

solid answer

~30 s

Build the document with `SearchVector('title', weight='A', config='english') + SearchVector('ingredients', weight='B', config='english')`, parse the user's text with `SearchQuery(q, search_type='websearch', config='english')`, then `annotate(rank=SearchRank(vector, query)).filter(vector=query).order_by('-rank')`. The default weights D, C, B and A score 0.1, 0.2, 0.4 and 1.0, so a title hit outranks an ingredient hit. Annotating a `SearchVector` computes `to_tsvector` for every row on every query, which scans the table. To make it fast, store the vector: a `GeneratedField` with `output_field=SearchVectorField()` and `db_persist=True`, plus `GinIndex(fields=['search_vector'])`, or a functional `GinIndex` on exactly the same `SearchVector` expression. Keep the text search configuration the same on vector and query.

code

python · 19 lines
python
from django.contrib.postgres.indexes import GinIndex
from django.contrib.postgres.search import SearchVector, SearchVectorField
from django.db import models


class Recipe(models.Model):
    title = models.CharField(max_length=200)
    ingredients = models.TextField()
    method = models.TextField()
    search_vector = models.GeneratedField(
        expression=SearchVector('title', weight='A', config='english')
        + SearchVector('ingredients', weight='B', config='english')
        + SearchVector('method', weight='C', config='english'),
        output_field=SearchVectorField(),
        db_persist=True,
    )

    class Meta:
        indexes = [GinIndex(fields=['search_vector'], name='recipe_search_gin')]

go deeper

for a junior

Recall the search lookup, and that SearchVector builds the document, SearchQuery the query and SearchRank the score.

for a middle

Explain weights and their defaults, the search types, matching configs, and why annotating a vector per query scans the table.

for a senior

Choose between a functional index, a GeneratedField and a trigger-fed SearchVectorField, and keep vector definitions from drifting apart.

for a principal

Decide when PostgreSQL search is enough and when relevance tuning, typo tolerance or scale justify a dedicated search service.

## The three pieces PostgreSQL full-text search compares a **document** (`tsvector`, a list of normalised word stems called lexemes) with a **query** (`tsquery`). `django.contrib.postgres.search` wraps both, plus ranking: | Class | PostgreSQL function | Role | |---|---|---| | `SearchVector(*expressions, config=None, weight=None)` | `to_tsvector` | builds the document from fields | | `SearchQuery(value, config=None, search_type='plain')` | `plainto_tsquery` and friends | parses the search text | | `SearchRank(vector, query, weights=None, normalization=None, cover_density=False)` | `ts_rank` | scores each match | | `SearchHeadline(expression, query, ...)` | `ts_headline` | highlights matches in a snippet | The shortcut `Recipe.objects.filter(title__search='curry')` builds a vector from one column and a plain query using the database's default configuration; it needs `'django.contrib.postgres'` in `INSTALLED_APPS`. ## Weighting and ranking Fields matter differently: a word in a recipe's title says more than the same word in its method. Give each `SearchVector` a weight letter and combine them with `+`: - `A` defaults to 1.0, `B` to 0.4, `C` to 0.2 and `D` to 0.1. - `SearchRank(vector, query, weights=[0.1, 0.2, 0.4, 1.0])` overrides them, in D, C, B, A order. - `cover_density=True` switches to `ts_rank_cd`, which rewards query terms that appear close together. - Filter out weak matches with `.filter(rank__gte=0.1)` after annotating. ## Parsing the user's text `search_type` controls how `SearchQuery` interprets its input: 1. `'plain'` (the default) treats the words as terms that must all match. 2. `'phrase'` requires the words in order. 3. `'websearch'` accepts search-engine syntax: quotes, `or`, and `-` to exclude. It is the usual choice for a search box because it tolerates whatever users type. 4. `'raw'` passes text straight to `to_tsquery`, so malformed input causes a database error; keep it for queries your code builds. Since Django 6.0, `Lexeme` builds queries from parts with `&`, `|`, `~`, prefix matching and weights while escaping each term, which is the safe way to compose a query from separate inputs. The **config** (such as `'english'`) decides stemming and stop words. Use the same config on the vector and the query, or 'baking' in the document and 'bake' in the query will not meet. ## Why the naive version is slow ```python Recipe.objects.annotate(search=SearchVector('title', 'ingredients')).filter(search=query) ``` This asks PostgreSQL to run `to_tsvector` over every recipe on every request, then compare. There is nothing to index: an index on the `title` column does not help a `to_tsvector(...)` expression. It is fine for a few hundred rows and degrades linearly after that. ## Making it fast | Approach | How it stays current | Notes | |---|---|---| | Functional `GinIndex(SearchVector('title', 'ingredients', config='english'), name=...)` | PostgreSQL maintains the index | only used when the query repeats the **same** expression and config | | `GeneratedField(expression=..., output_field=SearchVectorField(), db_persist=True)` + `GinIndex(fields=['search_vector'])` | PostgreSQL recomputes the column on every write | `GeneratedField` exists since Django 5.0; needs an explicit config | | Plain `SearchVectorField` + `GinIndex` | a trigger, or your own `update(search_vector=SearchVector(...))` | older pattern; easy to let drift | The generated-column version is the cleanest in current Django: the vector is computed once per write, stored, indexed, and queried like a column with `filter(search_vector=query)`. Pass `config` explicitly, because PostgreSQL only accepts immutable expressions in generated columns and index expressions, and `to_tsvector` without a configuration depends on a server setting. ## Highlighting and multilingual collections Search results usually show a snippet with the matching words marked. `SearchHeadline('method', query, start_sel='<mark>', stop_sel='</mark>', max_words=30)` asks PostgreSQL for such a fragment. It works on the raw text, not the stored vector, so annotate it only on the page of results you actually display. A recipe collection in several languages needs a different stemming configuration per recipe. `SearchVector` and `SearchQuery` accept an expression for `config`, so `config=F('language')` reads it from a column on each row; the query must then use the same per-row config for stems to line up. Two practical consequences: - A single functional index with a fixed config no longer matches every row; a stored vector computed with each row's own config is the simpler route. - The config values must be text search configurations the database actually has, such as `'english'` or `'french'`, so validate the language column against that list. Tests for search code need a PostgreSQL test database, since none of these classes work on SQLite. ## Limits worth saying out loud - Full-text search matches **words**, so a misspelling such as 'lazagna' finds nothing; trigram similarity handles typos. - Ranking reads the vectors of every matching row, so a very common term on a large table is still expensive to rank. - `SearchHeadline` runs on the raw text of each returned row, so apply it after pagination.

  • Why might a functional GinIndex on SearchVector('title', config='english') go unused?
    PostgreSQL uses an expression index only when the query contains the same expression. If the query annotates `SearchVector('title')` without the config, or with fields in another order or with weights, the SQL differs and the index is ignored. Keep one shared definition of the vector, or store it in a column, so the index and the query cannot drift apart.
  • Which search_type should a public search box use, and why not 'raw'?
    `'websearch'`: it accepts free text with quotes, `or` and `-` exclusions and never fails on odd input. `'raw'` hands the text to `to_tsquery`, which expects operator syntax, so a stray `&` or unbalanced parenthesis from a user becomes a database error instead of a search.

Annotating a SearchVector on every query is like rebuilding a cookbook's back-of-book index each time someone looks up 'curry'. A stored vector with a GIN index is the printed index: built once, updated when a recipe changes, and read straight off the page.

saying these in an interview costs you the question

  • An index on the title column speeds up SearchVector('title') annotations.
  • Full-text search finds misspelled words because it stems them.
  • SearchQuery defaults to raw, so user text needs escaping first.
  • Weight A is the lowest weight and D the highest.
  • The vector and query can use different configs without affecting matches.