阅读中文版本

Trigram Indexes for Fuzzy Matching

A trigram index breaks text into three-character sequences so LIKE, ILIKE, and similarity queries can be answered without scanning every row.

Definition

A trigram index indexes overlapping three-character substrings of a text value, enabling the planner to translate pattern and similarity predicates into index conditions.

It is used for substring search, autocomplete, and typo-tolerant matching where exact equality is too strict. Compared to simpler alternatives, this concept fits best when the trade-off it represents is the one the system actually cares about.

When to use it

A common use case is applying it at the boundary where the cost of getting it wrong is highest, and the scenario usually involves a concrete guarantee the system must uphold. Unlike an ad hoc approach, it makes that guarantee explicit, reviewable, and testable.

How it works

The extension splits text into trigrams and stores them in a GIN or GiST index, so a pattern that contains at least one literal three-character sequence can be partly satisfied by the index.

Patterns with no usable trigram, such as a very short search term, force a full scan, which is why short inputs should be handled or rejected explicitly.

Trade-offs and alternatives

The similarity operator returns a score and relies on the same index, letting a query rank near matches by how many trigrams they share. The main drawback is the extra complexity it introduces, so the benefit should clearly outweigh that cost before a team adopts it.

Common misconceptions

A persistent misconception is that trigram indexes only help with equality comparisons. Another is that they replace the need for a real full-text search engine. For example, an engineer who assumes otherwise may design around a guarantee that does not actually exist, which only becomes obvious under load or failure.

Anti-patterns to avoid

Two recurring anti-patterns are to indexing a very short column where trigrams carry little information and to expecting a trigram index to accelerate leading-wildcard scans on unrelated data. Both tend to surface as hard-to-debug production issues rather than obvious test failures. Related concepts include Full-Text Search Vectors, Advisory Locks, Cost-Based Query Optimization.