Choose an inverted index when people search for words and analyzed terms across documents. Choose a trigram index when they need substring matching, approximate string matching, or help finding misspelled input. If your app needs both normal full-text retrieval and typo recovery, a hybrid design can use each index for the job it suits.
The decision starts with the queries your app must answer—not with a general claim that one index is faster or smaller. Matching behavior, text processing, ranking needs, update patterns, and results on your own workload all matter.
How an inverted index finds documents by words
An inverted index maps each analyzed term to the documents containing it. Instead of scanning every document for a word, a search engine can look up the term and retrieve its associated documents.
For example, if analysis turns “running shoes” into the terms “run” and “shoe,” the index can associate each term with documents that contain it. The exact result depends on the engine’s analyzer and configuration: tokenization and other text-processing choices determine which terms are indexed. Elasticsearch’s 8.19 guide describes this process as analyzing text into tokens and storing those tokens in an inverted index.
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →#1 Best Overall
This structure is a natural fit for term-oriented full-text search, where users expect to find documents that contain relevant words and may expect results to be ranked for relevance.
How a trigram index matches strings
A trigram is a group of three consecutive characters. Trigram matching compares groups shared by strings, or uses indexed trigrams to find candidates for a pattern. It works at the character level rather than relying on the same word-oriented matching model as a full-text index.
Rank #2
That makes trigrams useful when a query may contain a typo or when an app needs to find a substring inside a value. PostgreSQL’s pg_trgm extension supports similarity searches and index-assisted LIKE, ILIKE, and regular-expression searches. Its patterns do not have to be left-anchored, so a pattern such as '%phone%' can be considered.
There is an important selectivity caveat: PostgreSQL extracts trigrams from a pattern to guide an index search. If a pattern contains no extractable trigrams, the query can degenerate to a full-index scan. A pattern with more extractable trigrams gives the index more useful search keys.
Rank #3
Compare the approaches by the query you need to serve
| Decision axis | Inverted index | Trigram index |
|---|---|---|
| Matching unit | Analyzed terms or lexemes mapped to documents. | Character sequences of three consecutive characters, used for similarity or pattern matching. |
| Best fit | Full-text retrieval over words and documents. | Misspellings, approximate string matching, and substring patterns. |
| Text handling | Depends on the search engine’s analyzer and configuration. | Character-based; PostgreSQL documents case-insensitive similarity in a default build. |
| Query behavior | Token-oriented retrieval and ranking. | Similarity thresholds and, in PostgreSQL, LIKE, ILIKE, and extractable regular-expression patterns. |
| Main caution | Results depend on how the configured analysis tokenizes and processes text. | Short or otherwise unextractable patterns may provide little index selectivity; PostgreSQL warns that some can lead to a full-index scan. |
| Practical choice | Use for ordinary full-text retrieval. | Use when the app needs fuzzy or substring matching; pair with full text if both behaviors matter. |
This compares documented matching behavior, not benchmark results. The available documentation does not establish that either index type is universally faster, smaller, or easier to maintain.
What PostgreSQL’s trigram options mean
PostgreSQL 17’s pg_trgm extension provides GiST and GIN operator classes for trigram searches. Its documented default thresholds are configuration values—not quality scores or performance guarantees:
pg_trgm.similarity_threshold: 0.3.pg_trgm.word_similarity_threshold: 0.6.pg_trgm.strict_word_similarity_threshold: 0.5.
These thresholds are configurable, so an application’s matching behavior depends in part on its chosen settings. PostgreSQL also documents a distinction in index capabilities: GiST can efficiently implement nearest-neighbor distance ordering when retrieving a small number of closest matches, while GIN cannot implement that particular distance ordering. For ordinary equality, the documentation cautions that trigram indexes may not be as efficient as regular B-tree indexes.
SQLite FTS5 offers a tokenizer-specific option
SQLite FTS5 is a full-text extension that includes an optional trigram tokenizer. Its behavior depends on tokenizer configuration: with case_sensitive=1, trigram tables may support GLOB queries but not LIKE queries. Confirm that the SQLite build used by your deployment includes FTS5, and verify the exact tokenizer and query behavior your app intends to rely on.
FTS5 also documents auxiliary functions including bm25(), which returns a numeric relevance value, and snippet(), which produces contextual excerpts. These help with presentation and ranking, but they do not make every FTS5 configuration behave like every other full-text or trigram implementation.
When a hybrid design makes sense
If users need normal full-text results but often mistype search terms, a trigram index can complement—not replace—the full-text index. PostgreSQL’s documentation describes using trigram matching to identify misspelled query words that do not directly match the lexemes in a full-text index.
A practical flow is to run the normal full-text query first, then use trigram matching to suggest or recover likely intended terms when direct matching is inadequate. Decide how to combine the results and how much extra work to perform based on your app’s desired experience; the index documentation does not prescribe a universal ranking or fallback policy.
How to choose using your own workload
Feature documentation explains what these indexes can match, but it does not provide a controlled comparison establishing a universal speed or storage winner. Benchmark the options against the data and behavior your app actually needs.
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →- Build a representative corpus. Use realistic text, field lengths, language mix, and data volume for the app.
- Use real query patterns. Include ordinary word searches, misspellings, substring patterns, and any short or unusual patterns users can enter.
- Include writes. Measure index build and update costs under the app’s expected write rate, including concurrent updates if that is part of production use.
- Check result quality as well as latency. Confirm that relevant documents appear, fuzzy matches are useful, and rankings meet product expectations.
- Measure storage and response time. Compare the tested configurations on the same corpus and workload; do not infer a universal advantage from one setup.
For term-focused retrieval, start with an inverted index. For typo tolerance or substring patterns, test a trigram index. If users need both, evaluate a hybrid against the same representative queries and operational conditions.
Quick Recap
Sources
- PostgreSQL 17
pg_trgmdocumentation - SQLite FTS5 Extension reference
- Elasticsearch 8.19 full-text search guide
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
