Trigram Search

ELI5 — The Vibe Check

Trigram search breaks words into groups of three letters and matches them fuzzily. So searching for 'postgre' would still find 'PostgreSQL' because they share trigrams like 'pos', 'ost', 'stg'. It's amazing for typo-tolerant search and 'did you mean...' features. Users can't spell, and trigrams don't care.

Real Talk

Trigram search uses the pg_trgm extension in PostgreSQL to split strings into three-character subsequences and calculate similarity between strings. It enables fuzzy matching, typo tolerance, and LIKE/ILIKE query acceleration through GIN or GiST indexes. The similarity threshold is configurable for precision vs. recall trade-offs.

Show Me The Code

CREATE EXTENSION pg_trgm;
CREATE INDEX idx_name_trgm ON users USING GIN(name gin_trgm_ops);

SELECT name, similarity(name, 'Jonh') AS sim
FROM users
WHERE name % 'Jonh'
ORDER BY sim DESC;

When You'll Hear This

"Trigram search catches typos that full-text search would miss." / "We use pg_trgm for our autocomplete feature."