Trigram Search (pg_trgm) for Fuzzy Matching
Find similar strings with the pg_trgm extension, GiST/GIN trigram indexes, and similarity().
Trigram Search (pg_trgm) for Fuzzy Matching is a free SQL Academy lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
Why Trigrams?
LIKE %word% can't use a B-tree index. Trigrams ("3-character substrings") combined with GIN/GiST give you fast fuzzy and substring search.
Enable the Extension
pg_trgm ships with PostgreSQL contrib:
CREATE EXTENSION IF NOT EXISTS pg_trgm;Trigram of a Word
Show the trigrams of a string:
SELECT show_trgm('database');
-- {dat, aba, atab, tab, abas, base, ase, ...}Similarity Function
Float in [0..1]:
SELECT similarity('database', 'datbase'); -- ~0.75
SELECT similarity('hello', 'world'); -- ~0.0The % Operator
Returns true if similarity is above the threshold (default 0.3):
SELECT 'database' % 'datbase'; -- t
SET pg_trgm.similarity_threshold = 0.4;Index for Substring Search
GIN trigram index makes LIKE '%word%' fast:
CREATE INDEX users_name_trgm ON users USING GIN (full_name gin_trgm_ops);
-- Now this is index-supported:
SELECT * FROM users WHERE full_name ILIKE '%ali%';GiST vs GIN
- GIN — faster reads, slower writes, bigger index
- GiST — faster writes, slower reads, smaller index
Most teams pick GIN for fuzzy search.
Fuzzy Match Queries
Find similar strings even with typos:
SELECT full_name, similarity(full_name, 'datbase') AS sim
FROM users
WHERE full_name % 'datbase'
ORDER BY sim DESC
LIMIT 10;Distance for Ranking
The <-> operator returns distance (1 - similarity), so you can ORDER BY distance ASC:
SELECT full_name
FROM users
WHERE full_name % 'alice'
ORDER BY full_name <-> 'alice'
LIMIT 10;Auto-Complete with Trigrams
Combine pg_trgm with LIKE prefix for snappy autocomplete:
SELECT name
FROM products
WHERE name % $1
OR name ILIKE $1 || '%'
ORDER BY similarity(name, $1) DESC
LIMIT 8;Limits of Trigrams
Trigrams work on the alphabet of the input. CJK languages need different tokenisation. For full natural-language search, use tsvector + GIN.
Combining With Other Indexes
A query with multiple filters can use a trigram index AND a B-tree via Bitmap And:
EXPLAIN ANALYZE
SELECT * FROM users
WHERE full_name ILIKE '%ali%' AND country = 'US';Recap
pg_trgm fixes the "leading wildcard" problem.
- GIN trigram index → fast LIKE %x%
- % operator and similarity() for fuzzy match
- <-> distance for ranking
- Great for autocomplete and typo-tolerant search
Quick Check
Why can't a regular B-tree index help with WHERE name LIKE '%alice%'?
Frequently asked questions
Is the “Trigram Search (pg_trgm) for Fuzzy Matching” lesson free?
Yes — the full text of “Trigram Search (pg_trgm) for Fuzzy Matching” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.
What will I learn in “Trigram Search (pg_trgm) for Fuzzy Matching”?
Find similar strings with the pg_trgm extension, GiST/GIN trigram indexes, and similarity(). You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Academy?
No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Trigram Search (pg_trgm) for Fuzzy Matching” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Academy lesson?
Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
All lessons in this course
- Trigram Search (pg_trgm) for Fuzzy Matching
- Full-Text Search with tsvector and GIN
- Geospatial Indexing with PostGIS
- Vector Search with pgvector