Weighted full-text search and trigrams in PostgreSQL

Two search families

PostgreSQL gives two useful search families that are easy to confuse: full-text search and trigram similarity. They answer related but different questions.

Full-text search asks whether a document contains lexical terms that match a query. It tokenizes text, normalizes words into lexemes, and lets the database rank matches. Trigram search compares strings by overlapping three-character fragments, which makes it useful for typos, partial names, and fuzzy matching.

A good in-database search design often uses both. The important part is to let each tool do the work it is good at instead of forcing one mechanism to imitate the other.

tsvector and tsquery

The full-text path starts with tsvector and tsquery. A tsvector is the indexed representation of searchable document text. A tsquery is the parsed search expression.

Functions such as to_tsvector, plainto_tsquery, and websearch_to_tsquery help convert ordinary text into those internal forms. The configuration matters because tokenization and stemming differ by language.

For performance, expensive vector construction should usually be precomputed or indexed. Rebuilding a vector for every row at query time is a common reason a search endpoint feels fine in development and slow in production.

Weights and ranking

Weights let different fields contribute differently to rank. A title match may deserve more weight than a body match. A tag or topic match may sit somewhere between them.

PostgreSQL supports weighting inside the vector and ranking with functions such as ts_rank or ts_rank_cd. The useful discipline is to keep ranking explainable. If a result appears first, the team should be able to say whether that happened because of title weight, term frequency, proximity, or a separate business rule.

Search quality is not only a database feature. It is a product contract. The rank formula should match what users expect to find.

Trigrams

The pg_trgm extension breaks strings into trigrams and compares them by similarity. That makes it practical for fuzzy names, misspellings, short titles, and autocomplete-like flows where exact lexical matching is too strict.

Trigrams are not a replacement for semantic search. They are a pragmatic string tool. They can find that two spellings are close, but they do not understand that two different words are conceptually related unless the characters overlap enough.

The extension becomes much more useful with the right indexes. Without indexes, similarity checks across many rows are CPU-heavy string work.

Combining them

A common pattern is to use full-text search for the main candidate set and trigram similarity for fallback or scoring boosts. For example, an exact title term match can rank strongly, while a trigram score can rescue a misspelled title.

The mistake is to put too much computation in a function that runs for every row. Combining three trigram calls, dynamic to_tsvector, and ranking over a large view can become expensive quickly.

Start with indexed searchable columns, precomputed vectors where needed, and separate measurements for candidate selection and final ranking. Search systems fail slowly when every layer is blended into one opaque score.

A minimal trigram search

Trigram search is useful when users type approximate names, fragments, or misspellings. Full-text search understands words and lexemes; trigrams are closer to fuzzy string matching.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX index_articles_on_title_trgm
ON articles USING gin (title gin_trgm_ops);

SELECT id, title, similarity(title, 'activ record') AS score
FROM articles
WHERE title % 'activ record'
ORDER BY score DESC
LIMIT 10;

The operator finds rows similar enough to the query according to the current trigram threshold. For a search box, the usual pattern is to combine this with full-text ranking instead of replacing one with the other.

SELECT id, title
FROM articles
WHERE search_vector @@ plainto_tsquery('english', $1)
   OR title % $1
ORDER BY
  ts_rank(search_vector, plainto_tsquery('english', $1)) DESC,
  similarity(title, $1) DESC
LIMIT 20;

This keeps exact linguistic matches strong while still rescuing typos and short title fragments.

Tuning the threshold

The default trigram threshold is only a starting point. Lower thresholds find more typo-tolerant matches but can also return noisy results and force more ranking work. Higher thresholds keep results cleaner but may miss short or heavily misspelled queries. The right value depends on query length, language, and whether the field is a title, name, slug, or body text.

Measure with real search logs when possible. A good test set includes exact matches, prefix fragments, swapped letters, missing accents, and common misspellings. Trigram search is often best as a rescue lane alongside full-text search, not as the only ranking signal.

Short queries deserve special handling. A two-letter query can match too much, while a long misspelled title can still be meaningfully similar. Many applications use minimum query lengths, field-specific ranking, or a fallback path for very short inputs.

The final ranking should be inspected by humans, not only benchmarked by query time. Search quality is visible in the first page of results.