Fuzzy Matching with pg_trgm Similarity
Power typo-tolerant and autocomplete search using trigram indexes and similarity thresholds.
Why Fuzzy Matching?
Users misspell things. They type jonh instead of john, or postgers instead of postgres. A plain = or even LIKE comparison returns nothing for these typos.
Fuzzy matching finds rows that are close enough to the search term, not just exact matches. PostgreSQL ships this capability in the pg_trgm extension, which powers:
- Typo-tolerant search — match despite small spelling errors
- Autocomplete — suggest as the user types
- Deduplication — find near-duplicate names or addresses
The key idea is measuring similarity rather than equality.
What Is a Trigram?
A trigram is a group of three consecutive characters in a string. pg_trgm breaks every string into its set of trigrams, padding the start and end with spaces.
For the word cat, PostgreSQL produces the trigrams: " c", " ca", "cat", "at ". You can inspect this yourself with show_trgm().
Two strings are considered similar when they share many trigrams. Because trigrams overlap, a single typo only damages a few of them, so similar words still share most of their set.
-- Enable the extension once per database
CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- Inspect the trigrams of a word
SELECT show_trgm('cat');
-- {" c"," ca","at ","cat"}All lessons in this course
- Designing tsvector Columns and GIN Indexes
- Ranking and Relevance Tuning with ts_rank
- Fuzzy Matching with pg_trgm Similarity
- Combining Filters with Search Predicates