A B-tree index stores values in sorted order, which is what makes it fast: to find everything starting with a prefix, jump to that point and read forward.
That works for a trailing wildcard. It does not work when the pattern begins with a wildcard, because the search term could appear anywhere in the value and sorted order provides no help. The database scans every row.
What changed in 2026
- Trigram indexing became a standard tool. Widely available and routinely used for search-as-you-type and fuzzy matching features.
- Fuzzy matching demand grew. Typo-tolerant search became an expected product feature rather than a refinement.
- The dedicated-search-engine boundary clarified. Trigram indexing handled moderate needs; full search engines remained for ranking and language-aware requirements.
- Index size scrutiny increased. Teams learned that trigram indexes can be large relative to the data they index.
How trigrams work
The column's text is broken into every overlapping three-character sequence. The word search produces the trigrams sea, ear, arc, rch, plus padded ones at the boundaries.
Those trigrams are indexed, with the rows containing each. When you search for a substring, the search term is broken into its own trigrams, and the index finds rows containing all of them. Those candidates are then checked properly against the actual pattern.
Because a substring anywhere in the text produces trigrams present in the index, position no longer matters — which is exactly what a leading wildcard needed.
The two-stage nature is worth knowing: the index produces candidates, and a recheck confirms them. So the index reduces the rows examined rather than answering the query outright, which is why its benefit depends on how selective the trigrams are.
Where it works and does not
| Pattern |
B-tree |
Trigram |
LIKE 'abc%' |
Yes |
Yes, but B-tree is better |
LIKE '%abc' |
No |
Yes |
LIKE '%abc%' |
No |
Yes |
| Case-insensitive match |
With an expression index |
Yes |
| Similarity / typo matching |
No |
Yes |
| Term shorter than 3 chars |
N/A |
Poorly |
| Exact equality |
Yes, better |
Works, wasteful |
The short-term limitation is structural. A two-character search term produces essentially no useful trigrams, so the index cannot narrow the candidate set and the query degenerates toward a scan. Search-as-you-type features hit this on the first two keystrokes, which is why they typically wait for three characters before querying.
Fuzzy matching
The capability people adopt trigram indexing for and then find more useful than the substring search that motivated it.
Because text is represented as a set of trigrams, you can measure similarity between two strings by how many trigrams they share. That gives typo tolerance — recieve and receive share most of their trigrams — and near-match ranking.
That supports "did you mean" behaviour, deduplication of near-identical records, and matching user input against a canonical list. Implemented with an ordinary index rather than a separate search system, which is the appeal for moderate needs.
The tradeoff is that similarity is purely lexical. It knows nothing about meaning, so semantically related but lexically different terms do not match — that is what embeddings are for, per how vector embeddings work.
Costs
Trigram indexes are large. Each row contributes many trigrams, and the index stores row lists for each, so index size can approach or exceed the size of the indexed text.
Writes are correspondingly expensive, for the same reason as any index storing many entries per row — see GIN indexes, which is a common underlying structure here.
That makes trigram indexing a poor fit for very large text columns or write-heavy tables. It suits moderate-length values — names, titles, identifiers, descriptions — queried frequently and written less.
Common mistakes
- Using it for prefix-only search. A B-tree is smaller and faster.
- Expecting good performance on short terms. Fewer than three characters has nothing to match.
- Indexing very large text columns. Index size and write cost become severe.
- Assuming semantic matching. It is purely lexical.
- Not checking the plan. The planner may decline the index on unselective patterns.
- Ignoring index size. Can rival the table.
FAQ
Does this replace a search engine?
For substring and fuzzy matching on moderate data, yes. For ranked relevance, language-aware stemming, and large corpora, a dedicated search system does considerably more.
How large will the index be?
Potentially comparable to the indexed data. Measure on a representative sample before adding it to a large table.
Can I combine it with other indexes?
Yes, and it is common — a B-tree for exact and prefix matching, a trigram index for substring and fuzzy. The planner picks per query.
What about case sensitivity?
Trigram matching is typically case-insensitive by default, which is usually what search features want and worth confirming for your engine.
Where to go next
For the underlying index structure, read GIN indexes. For semantic rather than lexical matching, how vector embeddings work, and for index selection generally, database indexing explained.