Search that survives misspellings and local product names
A kirana store's online catalog lists "poha" under the SKU `POHA-500`. A customer types "powa." Another searches for "flattened rice" — the English translation their mother-in-law uses. A third types "chivda" because that's what they call the dish they make with it. None of these match the indexed product name.
This is the search problem for Indian e-commerce: the gap between what's in your catalog and what the customer types is not a typo. It's a language difference, a regional synonym, a transliteration variant, or all three at once.
We ran into this building a storefront for a spice and grocery merchant in Pune. Their catalog had 4,200 SKUs with names in a mix of English, Hindi transliterated to English, and Marathi. Standard Postgres full-text search returned nothing for 23% of queries in the first week.
The fixes were layered, and each layer was cheap.
**Fuzzy matching on the query side.** Postgres `pg_trgm` gives you trigram similarity search. It breaks a string into three-character sequences and compares overlap. For single-word misspellings — "powa" vs "poha" — it works well with a similarity threshold around 0.3.
CREATE EXTENSION IF NOT EXISTS pg_trgm;
SELECT name, similarity(name, 'powa') AS score
FROM products
WHERE name % 'powa'
ORDER BY score DESC
LIMIT 10;The `%` operator uses the similarity threshold set by `pg_trgm.similarity_threshold`. We set it to 0.3 for product search. Lower catches more but returns noise; 0.3 was the sweet spot after testing against 800 real queries.
**Synonym mapping for local names.** Trigram matching won't bridge "flattened rice" to "poha" — the strings are too different. For these, we maintain a synonyms table that the merchant's staff can edit from the admin panel. Each row maps a search term to a canonical product name or category.
CREATE TABLE search_synonyms (
search_term TEXT NOT NULL,
canonical TEXT NOT NULL,
created_by INTEGER REFERENCES staff(id),
PRIMARY KEY (search_term, canonical)
);When a query comes in, we check the synonyms table first. If there's a match, we run the search against the canonical term. If not, we fall through to trigram search. This ordering matters: synonyms are exact, curated, and should win.
The merchant added 340 synonym mappings in the first three months. "Beaten rice" to "poha." "Khajur" to "dates." "Methi leaves" to "fenugreek." The table grows slowly and stays manageable because the staff adding synonyms are the ones who know what customers actually call things.
**Transliteration handling.** "Haldi" and "haldee" and "haldhi" are all the same product. Trigram similarity catches these because the character overlap is high. But "turmeric powder" to "haldi powder" needs the synonyms table again. We don't attempt automatic transliteration — the libraries we tested (indic-transliteration, ai4bharat) were accurate but added 80-120ms per query, and the synonyms table covers the common cases faster.
**Query logging to find gaps.** Every search that returns zero results gets logged with the query string and timestamp. We review these weekly. In the first month, 12% of zero-result queries were new synonyms waiting to be added. By month three, that dropped to 4%.
CREATE TABLE zero_result_searches (
query TEXT NOT NULL,
searched_at TIMESTAMPTZ DEFAULT NOW()
);We considered Elasticsearch with its built-in fuzzy and phonetic analyzers. For a 4,200-product catalog, it was overkill. The setup cost — a separate service, index syncing, another failure surface — wasn't justified. Postgres handles the query volume (about 8,000 searches per day) at 15-40ms per query. If the catalog grows past 50,000 SKUs or search volume crosses 100 queries per second, we'll revisit.
The tradeoff to name: this approach requires manual curation of synonyms. A machine learning model could cluster similar product names automatically, but for a catalog this size, the merchant's staff adding 10-15 synonyms per week outperforms what a model would produce without training data. The manual work is small, the feedback loop is tight, and the staff already know the regional naming patterns.
What we shipped: a search endpoint that tries exact match, then synonyms, then trigram similarity, and logs the misses. The whole thing is 90 lines of SQL and Python, runs on the existing Postgres instance, and brought the zero-result rate from 23% down to 6% over three months.