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.
Take a spice and grocery storefront with a few thousand SKUs, names in a mix of English, Hindi transliterated to English, and a regional language. Standard Postgres full-text search on a catalogue like that can return nothing for a fifth of queries or more in the first week.
The fixes are layered, and each layer is 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 is where we usually land after testing against a sample of 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.
A merchant will typically add a few hundred synonym mappings in the first few 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. Review these weekly. Early on, a large share of zero-result queries are just new synonyms waiting to be added; that share shrinks steadily as the table fills in.
CREATE TABLE zero_result_searches (
query TEXT NOT NULL,
searched_at TIMESTAMPTZ DEFAULT NOW()
);Elasticsearch, with its built-in fuzzy and phonetic analyzers, is the obvious alternative. For a catalogue of a few thousand products it is overkill. The setup cost — a separate service, index syncing, another failure surface — isn't justified. Postgres handles a few thousand searches a day at tens of milliseconds per query. If the catalogue grows past 50,000 SKUs or search volume crosses 100 queries per second, 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 this gives you: a search endpoint that tries exact match, then synonyms, then trigram similarity, and logs the misses. The whole thing is under a hundred lines of SQL and Python and runs on the existing Postgres instance. We would expect it to cut the zero-result rate by two-thirds or more over a few months, as the synonyms table catches up with how customers actually talk.