sb logoToday I Learned

Using trigrams for better searches in Postgres

Having used Elasticsearch in the past, I thought it was the best and easiest way to handle fuzzy searches. Today I discovered an extension for Postgres called “pg_trgm” that might prevent you from needing an Elastic instance after all. Postgres is actually very good at text searches using ILIKE, but they are optimized for terms that are left-anchored (eg. ILIKE 'term%' and not ILIKE '%erm%). Trigrams will work the same no matter where the match is in the column. In addition, it will give a weight to each match expressing how close it is.

CREATE EXTENSION pg_trgm;
CREATE INDEX names_last_name_idx ON names USING GIN(last_name gin_trgm_ops);

To see what the index looks like:

select show_trgm('resudek');
# {  r, re,dek,ek ,esu,res,sud,ude}

(these are the indexed trigrams!)

And to perform a search with weighting:

select last_name, similarity('dek', last_name) from names;
# last_name | similarity
# resudek   | 0.2
# rezutek   | 0.090909
# johnson   | 0