Users type "cafe" for "Café", "munchen" for "München", or miss a letter. You don't need a separate search engine for that. Postgres ships pgtrgm for fuzzy matching and unaccent for accent-insensitive comparison, and both are available on Supabase.
NEXO is a daily medical diagnosis game for clinical reasoning practice, built on Supabase in six languages, so tolerant search matters to me. This tutorial uses an invented places table; every snippet was run on PostgreSQL 17.
In the Supabase dashboard, go to Database → Extensions and enable pgtrgm and unaccent, or run this in the SQL editor:
Supabase's default searchpath includes extensions; inside functions, set it explicitly or schema-qualify.
pgtrgm splits text into trigrams (three-character groups, with words padded by spaces) and compares how many two strings share:
It provides scoring functions, threshold operators, and GIN/GiST operator classes so they (and LIKE '%...%') can use an index.
unaccent() is STABLE, not IMMUTABLE, because the dictionary it uses depends on searchpath. Indexes and generated columns need immutable functions, so wrap it and pin the dictionary with the schema-qualified two-argument form:
(If you installed the extension into public, use public.unaccent('public.unaccent', $1).) Now add a normalized column:
lower() follows the database's LCCTYPE, so with a UTF-8 locale "МОСКВА" and "москва" normalize to the same value.
GIN accelerates %, ; index-assisted nearest-neighbour ordering needs GiST (gisttrgmops). When you filter first and sort the candidates, GIN is a good default.
similarity vs wordsimilarity: which pgtrgm function should I use? similarity(a, b) compares whole strings; a % b is true when it exceeds pgtrgm.similaritythreshold (default 0.3). wordsimilarity(a, b) compares a with the best-matching part of b; a b is the distance, 1 - similarity(a, b).
Users expect "lon" to show "London" before "Babylon", so the function ranks exact matches, then prefixes, then word prefixes, then fuzzy matches:
Details worth keeping: security invoker (the default, made explicit) runs with the caller's privileges, so row-level security still applies. stable declares that it doesn't modify data. set searchpath makes the extension operators resolve no matter who calls it. Skip fuzzy matching under three characters; prefix matching is enough there. Cap the limit in the database, not only in the client.
Pick the threshold by scoring your own typos and unrelated words, not by guessing.
Postgres grants execute on new functions to PUBLIC, and Supabase's default privileges also grant it to anon and authenticated. If search should require sign-in:
Debounce input and ignore out-of-order responses. In NEXO, that plus a server-side limit keeps search responsive.
With seven rows Postgres will (correctly) scan the table, so I loaded 200,000 extra rows and ran the function's query with a literal:
Does pgtrgm work for Cyrillic, CJK and other languages? Check your locale. pgtrgm only keeps characters that LCCTYPE considers letters or digits. In a UTF-8 locale (such as C.UTF-8 or enUS.UTF-8) Cyrillic, Greek and accented Latin work. In a database with LCCTYPE = C, showtrgm('москва') returns {} and "Café" loses its "é". Check with select datctype from pgdatabase where datname = currentdatabase();. CJK is weak. Without spaces between words, a phrase becomes one long "word" and short queries score low: similarity('東京', '東京タワー') is about 0.29, under the default threshold. Consider bigram-based approaches such as the pgbigm extension, where available. unaccent is for Latin-script diacritics. It won't transliterate "Москва" to "Moskva". If you need cross-script matching, store a transliterated alias. Trigrams aren't stemming. Long documents want full-text search.