Free tools Windows power users keep installed
One-click scans. No signup required.
pg_trgm adds typo-tolerant matching to PostgreSQL by comparing character trigrams—the groups of three consecutive characters shared between strings. On Supabase, enable the extension for your project, choose a matching operator and index that fit your query, and validate results against the languages and scripts your users actually search. PostgreSQL says trigram matching can be effective for words in many natural languages; the documentation does not promise equal accuracy or performance across languages.
What pg_trgm does—and what it does not do
PostgreSQL’s pg_trgm documentation defines a trigram as a group of three consecutive characters taken from a string. The extension compares strings by measuring how many trigrams they share. This makes it useful when a search term is close to a stored value despite a typo, or when a query should match a word-like portion of a longer field.
As an Amazon Associate I earn from qualifying purchases.
Trigram matching is character-based, not a language-aware spelling system. It does not itself translate, stem words, or guarantee that similar-looking terms are semantically related. PostgreSQL describes the approach as effective for words in many natural languages, but does not publish language-by-language accuracy or performance measurements. Test it with representative queries in the languages, scripts, and text lengths used by your application.
How to enable pg_trgm in Supabase
Supabase lists pg_trgm among its Postgres extensions. Its extensions guide documents installation through the SQL Editor or a PostgreSQL client. Confirm the extension is available and enabled in the specific project you are deploying to; availability and installed versions should not be assumed across every project.
#1 Best Overall
- Open the Supabase project. Use the project’s SQL Editor or connect with a PostgreSQL client, as described in the Supabase extensions guide.
- Enable the extension. Follow the guide’s extension workflow for
pg_trgm, or runCREATE EXTENSION IF NOT EXISTS pg_trgm;if your database role and project allow the SQL command. - Verify installation. Run
SELECT extname, extversion FROM pg_extension WHERE extname = 'pg_trgm';. A returned row confirms an installed extension and reports its version; no row means it is not installed in that database. - Check the PostgreSQL version and project settings. Use documentation matching the deployed PostgreSQL version for operator and configuration details. Supabase notes that accessing a newly available extension version may require a software upgrade.
Choose the matching semantics for the query
The main decision is whether the query compares whole strings, looks for a query term within a longer text value, or ranks the closest candidates. PostgreSQL documents similarity functions, operators, thresholds, and distance operators in its pg_trgm module reference.
| Need | Mechanism | What it means |
|---|---|---|
| Compare two strings overall | similarity(a, b) or a % b |
similarity returns a similarity measure. The % operator is true when similarity exceeds the active pg_trgm.similarity_threshold. |
| Match a query against a word-like extent inside a longer value | word_similarity(query, value) or query <% value |
Compares the query with a continuous extent in the ordered trigram set of the second argument; useful when a term appears within a longer field. |
| Require the matching extent to align with word boundaries | strict_word_similarity(query, value) or query <<% value |
Uses word-similarity logic with the extent constrained to word boundaries. |
| Return closest candidates in order | ORDER BY value <-> query LIMIT n |
Orders values by trigram distance, allowing a nearest-neighbor result list instead of a yes/no threshold filter. |
The operator direction and argument order matter for word-similarity operations: the query is compared against an extent of the second argument’s trigram set. Check the PostgreSQL documentation for your deployed version when adapting examples.
Rank #2
Thresholds are starting settings, not quality scores
PostgreSQL 16 documents defaults of 0.3 for pg_trgm.similarity_threshold, 0.6 for pg_trgm.word_similarity_threshold, and 0.5 for pg_trgm.strict_word_similarity_threshold in its version 16 reference. These are configuration defaults, not measured accuracy rates or universal relevance settings. Tune the threshold against real examples: a lower threshold admits more candidates, while a higher threshold is more selective.
Choose GiST or GIN for the query shape
PostgreSQL documents both GiST with gist_trgm_ops and GIN with gin_trgm_ops for supported trigram operations. Both can support similarity matching and indexed LIKE, ILIKE, regular-expression, and equality searches in the documented version. There is no universal speed winner; the right choice depends on the workload and their relative performance characteristics.
| Query pattern | GiST (gist_trgm_ops) |
GIN (gin_trgm_ops) |
|---|---|---|
| Threshold-based trigram similarity | Supported | Supported |
Supported LIKE, ILIKE, regular-expression, and equality searches |
Supported | Supported |
Nearest-neighbor order, such as ORDER BY name <-> 'query' LIMIT 10 |
PostgreSQL 16 documents efficient distance-ordered retrieval | PostgreSQL 16 does not document this efficient nearest-neighbor support |
| Pattern with few or no extractable trigrams | May have poor selectivity or require a full-index scan | May have poor selectivity or require a full-index scan |
For a column named name, a basic index definition is CREATE INDEX name_trgm_gist_idx ON items USING GIST (name gist_trgm_ops); or, if the query pattern calls for GIN, CREATE INDEX name_trgm_gin_idx ON items USING GIN (name gin_trgm_ops);. These are alternatives for the same column and workload, not indexes to create automatically in tandem. The PostgreSQL 16 pg_trgm documentation warns that patterns without extractable trigrams can degenerate to a full-index scan. Very short search terms therefore need particular care: an index existing does not guarantee a selective lookup.
Combine trigram matching with full-text search
Full-text search and trigram matching solve different problems. PostgreSQL full-text search provides tokenization and normalization for document retrieval; trigram matching compares character sequences and can help recover from misspelled input that would otherwise fail to match a full-text term. PostgreSQL calls trigram matching useful in conjunction with a full-text index in its pg_trgm documentation.
For spelling suggestions, PostgreSQL describes building an auxiliary vocabulary of unique, unstemmed document words, then indexing that vocabulary with GIN and gin_trgm_ops. Its example extracts words with ts_stat using the simple text-search configuration. The vocabulary is a separate data structure from the full-text index and needs periodic regeneration to stay reasonably current.
This design lets full-text search retrieve documents while trigram similarity proposes likely spellings when a user’s word has no direct match. It does not make pg_trgm a stemmer or language-specific correction engine; the two mechanisms should be tuned and evaluated for their separate roles.
Validate multilingual behavior with your own search data
The official documentation supports the qualified claim that trigram matching can work effectively for words in many natural languages, not a guarantee of equal behavior for every language or script. The cited PostgreSQL and Supabase sources do not provide comparative multilingual benchmarks or quantified typo-correction accuracy.
Quick Recap
- Test common misspellings, inflections, accents, punctuation, and mixed-script terms found in your users’ searches.
- Include short and long queries; patterns with few extractable trigrams can be less selective for indexed pattern searches.
- Review false positives as well as missed matches, then tune thresholds for the particular operator and search experience.
- Keep full-text configuration and trigram logic distinct: full-text tokenization and normalization do not come from
pg_trgm. - Measure the chosen index under your actual query mix before deciding between GiST and GIN, especially if you need nearest-neighbor ordering.
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.
