From ElasticSearch to PostgreSQL Vector Search: Optimizing Nepali Language Search

The Developer's Dilemma

Picture this: You're sitting at your desk at 2 AM, surrounded by coffee cups, furiously googling "best search engine solutions." Sound familiar? We've all been there.

That's exactly where I found myself – staring at an empty whiteboard, sketching out system designs that could probably handle NASA's search needs. Diving deep into complex search engines like ElasticSearch and Meilisearch, convinced I needed their heavyweight features. But guess what? After all that complexity, the answer was sitting right there in our PostgreSQL database all along, quietly waiting with its vector search capabilities.

Sometimes the best solutions are the ones already in your toolbox.

Racing Cars vs. Daily Commutes

Ever caught yourself buying a race car when all you needed was a reliable sedan? That's what I did with my search infrastructure. Sure, ElasticSearch and Meilisearch are amazing tools – they're like those high-end sports cars with all the bells and whistles. But there I was, preparing for an F1 race when I really just needed to make it to the grocery store and back.

I had gotten caught up in the "what if" game: What if we hit a million users? What if we need to search through billions of records? What if we need super-complex queries? Meanwhile, my actual dataset was perfectly content sitting in a regular database.

That's when I discovered something pretty cool – PostgreSQL wasn't just that reliable database we all know. It was like finding out your dependable family car actually had some serious hidden features under the hood. Turns out, it could handle full-text search and even vector-based queries like a champ.

When a Flamethrower is Overkill

Don't get me wrong – ElasticSearch and Meilisearch are incredible pieces of technology. They're like bringing a flamethrower to toast your marshmallows. Super cool? Absolutely. But necessary for my needs? Not exactly.

I was building for this imaginary future where I'd have millions of users firing off complex search queries every second. Meanwhile, back in reality, my actual dataset was growing at a perfectly reasonable pace that any decent database could handle.

That's when I had my "aha" moment with PostgreSQL. Good old Postgres wasn't just sitting there managing my data – it had some serious search capabilities hidden up its sleeve. Full-text search? Check. Vector-based search? You bet.

The PostgreSQL Solution

PostgreSQL turned out to be a game-changer. With its robust extensions like pgvector and native support for full-text search, I implemented a solution that was not only efficient but also tailored to my needs—searching through a Nepali-language dataset.

One critical challenge was ensuring the system worked seamlessly while the search index was being built. During this time, I implemented a fallback search mechanism to maintain functionality.

The solution uses psycopg for database connectivity, a fallback_search function using simple ILIKE queries, and a vector_search function leveraging PostgreSQL's tsvector and plainto_tsquery for ranked results with automatic fallback on errors.

Under the Hood

Text Search Vector (tsvector): The tsvector column stores preprocessed text data optimized for search operations.

Query Conversion: The plainto_tsquery function converts the search query into a format PostgreSQL understands, enabling accurate results in Nepali.

Fallback Mechanism: When the vector search encounters issues (e.g., during index building), a simple ILIKE-based fallback search ensures the system remains operational.

Ranking: Results are ranked using ts_rank, ensuring the most relevant entries appear first.

Unified Architecture: By keeping the search index in the same database, operational overhead is significantly reduced.

Lessons from the Trenches

Looking back, we learned some pretty valuable lessons from this project. The biggest one was probably about keeping things simple at the start. It's much better to start basic and let the system grow naturally as you actually need new features.

We also had a bit of a wake-up call about downtime. Having a backup plan isn't just nice to have – it's essential. Whether you're rebuilding indexes or facing unexpected issues, having a solid fallback option can really save your bacon when things go sideways.

Something else that surprised us was just how capable PostgreSQL turned out to be. Good old Postgres handled things beautifully. And finally, we really learned the value of focusing on our specific needs. When we stopped trying to use one-size-fits-all solutions and actually optimized for Nepali-language search, everything just clicked.

Conclusion

Switching to PostgreSQL vector search was a revelation. It simplified the architecture, reduced costs, and delivered the performance I needed. While ElasticSearch and Meilisearch are phenomenal in the right contexts, they weren't the right fit for this project. Sometimes, the tools you already have can be the most powerful. Building a fallback search not only kept the system resilient but also reinforced the importance of pragmatic development.