Postgresql Fuzzy Search

Question

Forgive this general question. I have created a huge Fuzzy Search function in PostgreSQL that uses Similarity, Soundex, Metaphone, Levenshtein, and other types of logic comparisons. I've placed weighted values on each result. The function has grown to improve results, but it is slow and still not great! I've spent a lot of time researching and searching for a Fuzzy Search that someone has created who is smarter than me!

Does anyone know of a great fuzzy search function for PostgreSQL that compares two strings and produces a score that can be sorted? I am hoping for something that can work like:

SELECT tbl_name_column, fuzzy_function(tbl_name_column, 'Most like this string') as score FROM tbl ORDER BY score desc

Answer 1

The pg_trgm extension provides Trigram scoring, but there's also the fuzzystrmatch extension mentioned in this answer that offers other algorithmic support (the current PG documentation [v14] warns that everything but the levenshtein() function doesn't work well with multibyte encodings like UTF-8, so user beware).

pg_trgm:

SELECT 
    tbl_name_column, 
    similarity(tbl_name_column, 'search string') AS score 
FROM tbl 
ORDER BY score DESC; -- Trigram score increases with similarity

fuzzystrmatch:

SELECT 
    tbl_name_column, 
    levenshtein(tbl_name_column, 'search string') AS score 
FROM tbl 
ORDER BY score ASC; --levenshtein distance decreases with similarity