Fuzzy search in Postgres with pg_trgm and Supabase (multilingual, typo-tolerant)

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 pg_trgm 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.

How do I enable pg_trgm and unaccent on Supabase?

In the Supabase dashboard, go to Database → Extensions and enable pg_trgm and unaccent, or run this in the SQL editor:

create extension if not exists pg_trgm  with schema extensions;
create extension if not exists unaccent with schema extensions;

Supabase's default search_path includes extensions; inside functions, set it explicitly or schema-qualify.

What is pg_trgm and how does it match typos?

pg_trgm splits text into trigrams (three-character groups, with words padded by spaces) and compares how many two strings share:

select show_trgm('londn');
-- {"  l"," lo","dn ",lon,ndn,ond}

It provides scoring functions, threshold operators, and GIN/GiST operator classes so they (and LIKE '%...%') can use an index.

What example table does this tutorial use?

create table public.places (
  id    bigint generated always as identity primary key,
  lang  text not null check (lang in ('en', 'es', 'fr', 'de', 'pt', 'ru')),
  name  text not null
);

insert into public.places (lang, name) values
  ('en', 'London'), ('en', 'Londonderry'), ('en', 'New London'),
  ('en', 'Babylon'), ('fr', 'Café de Flore'), ('de', 'München'),
  ('ru', 'Москва');

How do I make Postgres search accent-insensitive?

unaccent() is STABLE, not IMMUTABLE, because the dictionary it uses depends on search_path. Indexes and generated columns need immutable functions, so wrap it and pin the dictionary with the schema-qualified two-argument form:

create or replace function public.immutable_unaccent(text)
returns text
language sql
immutable
parallel safe
strict
as $$
  select extensions.unaccent('extensions.unaccent'::regdictionary, $1)
$$;

(If you installed the extension into public, use public.unaccent('public.unaccent', $1).) Now add a normalized column:

alter table public.places
  add column name_norm text
  generated always as (lower(public.immutable_unaccent(name))) stored;

lower() follows the database's LC_CTYPE, so with a UTF-8 locale "МОСКВА" and "москва" normalize to the same value.

Which index should I use for pg_trgm, GIN or GiST?

create index places_name_norm_trgm
  on public.places
  using gin (name_norm extensions.gin_trgm_ops);

GIN accelerates %, <%, LIKE and ILIKE, but not ORDER BY ... <->; index-assisted nearest-neighbour ordering needs GiST (gist_trgm_ops). When you filter first and sort the candidates, GIN is a good default.

similarity vs word_similarity: which pg_trgm function should I use?

select similarity('london', 'londn'),        -- 0.44444445
       word_similarity('lond', 'new london'),  -- 0.8
       'londn' % 'london'     as fuzzy,        -- true
       'lond' <% 'new london' as word_fuzzy;   -- true

• similarity(a, b) compares whole strings; a % b is true when it exceeds pg_trgm.similarity_threshold (default 0.3).
• word_similarity(a, b) compares a with the best-matching part of b; a <% b uses pg_trgm.word_similarity_threshold (default 0.6). Ideal for search-as-you-type.
• a <-> b is the distance, 1 - similarity(a, b).

Thresholds are ordinary settings (per session, role or function):

set pg_trgm.similarity_threshold = 0.4;

select name, similarity(name_norm, 'munchen') as score
from places
where name_norm % 'munchen'
order by name_norm <-> 'munchen'
limit 5;
-- München | 1

How do I rank exact, prefix and fuzzy matches in one query?

Users expect "lon" to show "London" before "Babylon", so the function ranks exact matches, then prefixes, then word prefixes, then fuzzy matches:

create or replace function public.search_places(
  p_query text,
  p_lang  text,
  p_limit int default 20
)
returns table (id bigint, name text, score real)
language plpgsql
stable
security invoker
set search_path = public, extensions
set pg_trgm.word_similarity_threshold = 0.5
as $$
declare
  q text := lower(public.immutable_unaccent(trim(coalesce(p_query, ''))));
begin
  if q = '' then
    return;
  end if;

  return query
  select p.id, p.name,
         (case
            when p.name_norm = q                   then 4
            when p.name_norm like q || '%'         then 3
            when p.name_norm like '% ' || q || '%' then 2
            else word_similarity(q, p.name_norm)
          end)::real as score
  from places p
  where p.lang = p_lang
    and (
      p.name_norm like q || '%'
      or p.name_norm like '% ' || q || '%'
      or (length(q) >= 3 and q <% p.name_norm)
    )
  order by score desc, length(p.name), p.name
  limit least(greatest(p_limit, 1), 50);
end;
$$;

select * from search_places('lon', 'en');
-- London 3, Londonderry 3, New London 2, Babylon 0.5
select name from search_places('cafe', 'fr');     -- Café de Flore
select name from search_places('MUNCHEN', 'de');  -- München
select name from search_places('москв', 'ru');    -- Москва

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 search_path 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.

How do I secure the search function with grants and RLS on Supabase?

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:

revoke execute on function public.search_places(text, text, int) from public, anon;
grant  execute on function public.search_places(text, text, int) to authenticated;

alter table public.places enable row level security;
create policy "places are readable by signed-in users"
  on public.places for select
  to authenticated
  using (true);

Replace using (true) with your own rule.

How do I call the search function from supabase-js?

import { createClient } from '@supabase/supabase-js';

const supabase = createClient(SUPABASE_URL, SUPABASE_ANON_KEY);

export async function searchPlaces(query: string, lang: string) {
  if (!query.trim()) return [];
  const { data, error } = await supabase.rpc('search_places', {
    p_query: query,
    p_lang: lang,
    p_limit: 20,
  });
  if (error) throw error;
  return data;
}

Debounce input and ignore out-of-order responses. In NEXO, that plus a server-side limit keeps search responsive.

How do I check that Postgres uses the trigram index?

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:

explain (costs off)
select id, name from places
where lang = 'en'
  and (name_norm like 'londn%'
       or name_norm like '% londn%'
       or 'londn' <% name_norm);

 Bitmap Heap Scan on places
   Recheck Cond: ((name_norm ~~ 'londn%'::text) OR (name_norm ~~ '% londn%'::text) OR ('londn'::text <% name_norm))
   Filter: ((lang = 'en'::text) AND ((name_norm ~~ 'londn%'::text) OR (name_norm ~~ '% londn%'::text) OR ('londn'::text <% name_norm)))
   ->  BitmapOr
         ->  Bitmap Index Scan on places_name_norm_trgm
               Index Cond: (name_norm ~~ 'londn%'::text)
         ->  Bitmap Index Scan on places_name_norm_trgm
               Index Cond: (name_norm ~~ '% londn%'::text)
         ->  Bitmap Index Scan on places_name_norm_trgm
               Index Cond: (name_norm %> 'londn'::text)

For broad terms matching many rows, a sequential scan can be the right plan.

Does pg_trgm work for Cyrillic, CJK and other languages?

• Check your locale. pg_trgm only keeps characters that LC_CTYPE considers letters or digits. In a UTF-8 locale (such as C.UTF-8 or en_US.UTF-8) Cyrillic, Greek and accented Latin work. In a database with LC_CTYPE = C, show_trgm('москва') returns {} and "Café" loses its "é". Check with select datctype from pg_database where datname = current_database();.
• 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 pg_bigm 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.

What I'd keep from this

An immutable unaccent wrapper, a generated normalized column, one GIN index and a stable, security invoker function cover most search boxes without new infrastructure.

FAQ

Is pg_trgm available on Supabase?
Yes. Enable it under Database, Extensions, or run create extension pg_trgm with schema extensions.

Why can't I use unaccent() directly in an index?
It is STABLE, not IMMUTABLE. Wrap it in an immutable SQL function that pins the dictionary, then index a generated column.

GIN or GiST for trigram search?
GIN is a good default when you filter first and then sort. GiST is needed for index-assisted ORDER BY ... <-> nearest-neighbour ordering.

Does pg_trgm replace full-text search?
For names, titles and short labels, usually yes. For long documents and stemming, use full-text search.

I'm building NEXO, a daily medical diagnosis game for clinical reasoning practice, where a wrong guess is scored by its proximity in the ICD-10 hierarchy, available in six languages. See how it works or how it compares. On iPhone: App Store.

Every case in NEXO is fictional and written for teaching. NEXO is not medical advice.