Text search and indexingversion 1.6
pg_trgm
Fuzzy text search and fast LIKE and ILIKE with trigram indexes.
What it is for
pg_trgm splits text into three-character pieces and compares the sets. That gives typo-tolerant matching (similarity, the % operator) and, with a GIN trigram index, fast LIKE and ILIKE searches with a leading wildcard, which a normal B-tree index cannot serve.
Enable it
create extension if not exists pg_trgm with schema extensions;Example
create index posts_title_trgm on posts using gin (title gin_trgm_ops);
-- uses the index
select id, title from posts where title ilike '%postgres%';
-- typo-tolerant ranking
select title, similarity(title, 'postgress') as score
from posts
where title % 'postgress'
order by score desc
limit 10;Questions
How do I enable pg_trgm?
Run create extension if not exists pg_trgm with schema extensions; in the SQL editor, or switch it on from the Extensions page in the console. You do not need superuser access.
Which version of pg_trgm is installed?
1.6, on Postgres 17.6, as read from the running engine on 2026-10-08. Check yours with: select extversion from pg_extension where extname = 'pg_trgm';
Upstream project: www.postgresql.org/docs/17/pgtrgm.html
More text search and indexing extensions
- unaccent Remove accents from text for search.
- fuzzystrmatch Soundex, Metaphone and Levenshtein distance.
- btree_gin GIN indexes over plain columns, for mixed indexes.
- btree_gist GiST indexes over plain columns, for exclusion constraints.