Why our product search returned nothing for misspelled Hebrew and English queries

People do not search our catalog the way we named the products. On ramo.co.il a title is often Hebrew plus a Latin brand, and the query is whatever the customer typed with one thumb. For a long time a single wrong letter produced an empty grid.

The matcher was ILIKE. A correct phrase such as "מכונת שטיפה" returned the pressure washers. "karcher" returned them too, because the brand sits in the title in Latin letters. "karchr", or the Hebrew brand spelled "קרכר" instead of "קרשר", returned nothing. Support could paste those same words into the admin, squint, and find the SKU. That gap is embarrassing when the product is in stock and the only problem is one transposed letter.

A tools catalog makes this worse than a blog. Titles are long, bilingual, and full of numbers: bar ratings, sizes, model codes. Customers mix scripts in one query. "karcher 190" and "קרשר 190" are the same intent. They also misspell the English half and the Hebrew half independently. We kept seeing this on washers, sockets, and detectors, not as a rare edge case.

The first patch was more synonyms on the title. That fixed the brands we remembered to type in, and nothing else. Someone suggested a Hebrew stemmer next. Our misses were not inflections. They were typos. Stemming does not turn "karchr" into "karcher", and it does not help a Hebrew word with one letter swapped.

PostgreSQL's pg_trgm does. It chops a string into three-character slices and scores how many slices the query and the stored text share. One wrong letter knocks out a few trigrams. The score drops, but it does not go to zero. The extension is UTF-8, so Hebrew trigrams are ordinary trigrams. We did not need a language-specific index.

We store one search_text per product: lowercased Hebrew name, Latin brand, and SKU, joined by spaces. A GIN index with gin_trgm_ops sits on that column. Searching one concatenated column matters. When we scored name, brand, and SKU separately and OR-ed the predicates, a lucky SKU fragment outranked the actual washer, and the same product showed up more than once.

SELECT p.id,
       p.name,
       GREATEST(
         similarity(p.search_text, q.raw),
         word_similarity(q.raw, p.search_text)
       ) AS score
FROM products p
CROSS JOIN (SELECT 'karchr 190' AS raw) q
WHERE length(q.raw) >= 4
  AND (p.search_text % q.raw OR p.search_text ILIKE '%' || q.raw || '%')
ORDER BY score DESC
LIMIT 24;

% means "similar enough" under pg_trgm.similarity_threshold. We set that to 0.3 for queries of four characters or more. similarity() compares the whole strings, which punishes a short query against a long title. word_similarity() looks for the best matching slice inside search_text, which is what we want when the query is one brand inside a sentence-length name. GREATEST of the two keeps a tight full-string match at the top without hiding partials. The ILIKE branch stays so an exact substring still returns even if the trigram score is odd on a numeric token like "190".

Queries shorter than four characters skip trigrams. "בר" and "מס" are real stems in this catalog (ברז, מסמר, and a pile of size codes). At the default threshold they match half the table. Those stay on prefix ILIKE.

Trigrams do not fix the other mess we see: a query typed on the wrong keyboard layout. "karcher" typed with the Hebrew layout is not one edit away from "karcher"; it is a different string, and the score stays low. We map the two layouts in the app and run the search for both forms. That map is a 26-entry table, not a model. If neither form scores, we still show the empty state. The empty state is correct when we do not stock the thing.

The index is larger than a btree, and product writes got a little slower. We have thousands of SKUs, not millions, so we accepted it. On a bigger catalog we would run the plain ILIKE or a tsvector first and only hit trigrams when that returns zero. The common query then stays cheap, and the typo path still works.

After the change, "קרכר" and "karchr 190" both land on the Karcher washers, including the 190 bar pressure washers. We did not add a badge that says the search is fuzzy. The box just returns the product.