You know that search that takes forever to return results? Or worse, one that comes back with a bunch of irrelevant stuff that has nothing to do with what you typed?
Yeah. There’s probably a LIKE '%product%' behind it all.
The problem with LIKE
When you use LIKE with wildcards, you're basically telling the database to perform a table scan: it reads every row, character by character, until it finds what you're looking for. In a small project with little data, you won’t even notice. But as the application grows and traffic increases, this becomes the fastest way to kill your performance.
With LIKE, the database reads every row. With FTS, it goes straight to the index.
Think about it: imagine having to read an entire dictionary from start to finish just to find a single word. That’s exactly what your database is doing every time this query runs.
The golden rule: don’t use what you don’t need
Take it easy.
If you're building a side project, an MVP, or an application with few users, forget Elasticsearch and any complex FTS implementation. The cost of maintenance, infrastructure, and data synchronization is way too high for a scenario that doesn’t require that level of robustness.
Your relational database LIKE is perfectly fine for the beginning. Don’t try to apply iFood-level architecture to a barbershop scheduling system!
The solution: Full-Text Search (for high-scale systems)
When we’re talking about thousands or millions of requests and complex queries, the game changes completely. Full-Text Search (FTS) solves this using a structure called an inverted index. Instead of scanning text, the system pre-builds a map of words—just like the index at the end of a book.
The inverted index maps each word to the documents where it appears. At query time, the system just looks up the map.
So when a user types “wireless bluetooth headphones,” the system doesn’t scan the product table row by row. It queries the index and returns results—instantly.
When should you go beyond the basics?
PostgreSQL already has native support for FTS and works well for medium-scale workloads. But in high-performance architectures, the natural path is to delegate this to specialized engines like Elasticsearch.
Here are the main benefits you get:
- Fuzzy search: tolerance for typos. User searched for “gamming notebbok”? They’ll still find what they need.
- Relevance: contextual results beyond exact matches. Search for “cheap phone”? The system understands intent, not just keywords.
- Performance: extremely low latency even with millions of records and high concurrency.
Conclusion
If your application requires fast and intelligent search under high concurrency, migrating to a Full-Text Search engine is a game changer.
Just don’t forget: the best architecture is the one that fits your current needs. Don’t add complexity before you have a scaling problem that justifies it. First, make it work—then start tightening the bolts.
Bonus tip: want to keep your relational database data in sync with your search tool in real time? Look into CDC (Change Data Capture). You’ll thank yourself later.