Rebuilding Search From Scratch
When SQL LIKE queries just don't cut it anymore — and your users are noticing.
STACK
01 — THE PROBLEM
The platform had grown to over 800,000 SKUs across 60+ categories. The existing search was built on PostgreSQL full-text indexes — a perfectly fine solution at 10,000 products, but a slow, expensive disaster at 800K. Average search latency had crept above 4 seconds. Mobile users were abandoning search entirely. Support tickets about "can't find what I'm looking for" were filling up. The business knew this was a revenue problem. They just didn't know how big.
02 — THE APPROACH
First, I spent a week just observing. I instrumented every search query with latency logging and built a simple analytics dashboard to understand what people were actually searching for. Turns out 60% of queries were filtered by category + price range — a use case ElasticSearch handles trivially. Another 15% included obvious typos. Nobody was getting corrected, they were just getting zero results.
I designed the new platform in two layers: PostgreSQL remained the source of truth for product data (with a write-through invalidation strategy), and ElasticSearch handled all reads. I mapped out the indexing schema carefully — most search platforms get this wrong by indexing everything, which tanks performance. I indexed only the fields users actually query: name, brand, category hierarchy, tags, and price range.
The API layer was rebuilt in FastAPI, exposing a single unified search endpoint that handled fuzzy matching, synonym expansion, category boosting, and price filtering in a single query. I wrote a custom tokenizer for product-specific language (e.g., "2TB SSD" should match "2 TB solid state drive").
03 — THE OUTCOME
The new system went live in a phased rollout over 3 weeks. A/B test ran for 4 weeks before full cutover. Old system stayed warm as a fallback. Zero downtime migration.
THE TAKEAWAY
The lesson here isn't "use ElasticSearch". It's: understand the actual query patterns before picking a tool. SQL full-text search is fine for most applications. This one had outgrown it — and the data made that obvious before a line of new code was written.