CockroachDB: fuzzystrmatch

Description

CockroachDB 26.2 achieves full feature parity with PostgreSQL's fuzzystrmatch extension by introducing three new algorithms: Dmetaphone, Dmetaphone alt, and Daitch-Mokotoff. These join existing functions like Soundex, Difference, Metaphone, and Levenshtein to provide a comprehensive suite for approximate string matching. Each algorithm serves specific needs, such as Soundex for simple phonetic encoding, Metaphone for improved English pronunciation handling, Dmetaphone for cross-language name variations, and Daitch-Mokotoff for Slavic and Yiddish names. Performance is a critical focus, as these algorithms can be computationally expensive on large datasets. For example, running a Soundex query against one million rows without an index requires a full table scan, taking over half a second. To ensure scalability and low-latency responses, Rob Reid demonstrates using expression indexes. By pre-calculating the fuzzy match codes within the index, CockroachDB can immediately look up values rather than processing every row during query execution. 00:00 - Feature parity update 00:38 - Soundex algorithm 01:15 - Difference (distance) function 02:23 - Metaphone algorithm 03:16 - Double Metaphone (Dmetaphone) 04:39 - Daitch-Mokotoff 05:30 - Levenshtein algorithm 06:23 - Levenshtein less equal 07:30 - Performance and full scans 08:37 - Expression index optimization 10:23 - Summary and conclusion Learn more at cockroachlabs.com #distributedsql #databases #cockroachdb