You know that search that takes forever to return? Or worse, the one that comes back with a bunch of stuff that has absolutely nothing to do with what you typed?
Yeah. There’s probably a LIKE '%product%' somewhere at the root of it all.
The Problem with LIKE
The key detail is the % at the beginning. With a wildcard at the front, the database can’t use the index: it has to read row by row until it finds what you’re looking for. A LIKE 'product%', without the leading %, can use an index just fine — the real villain is the “contains” search, not LIKE itself.
In a small project with little data, you probably won’t even notice. But as the application grows and traffic increases, this becomes one of the fastest ways to send your performance downhill.
With LIKE, the database scans every row. With FTS, it goes straight to the index.
Think about it this way: imagine having to read an entire dictionary from beginning to end just to find a single word. That’s basically what the database is doing every time that query runs.
The Golden Rule: Don’t Use What You Don’t Need
Easy there.
If you’re building a side project, an MVP, or an application with just a few users, forget about Elasticsearch and any complex FTS implementation. The maintenance, infrastructure, and data synchronization costs are way too high for a scenario that doesn’t require that level of robustness.
Your relational database’s LIKE is perfectly fine to start with. Don’t build iFood-level architecture for a barbershop appointment system!
Before Switching Tools, Add an Index
There’s also a middle ground that a lot of people skip. In PostgreSQL, you can actually make that same LIKE '%product%' query use an index:
CREATE EXTENSION pg_trgm;CREATE INDEX idx_products_name ON products USING gin (name gin_trgm_ops);
Done. Same query, without changing a single line of application code, without spinning up another service — and you even get typo tolerance as a bonus.
For most projects, the conversation ends right here.
The trade-off: the index takes up storage space and makes INSERT and UPDATE operations slightly slower. It’s almost always worth it, but it’s good to know there’s a trade-off.
The Solution: Full-Text Search (for High-Scale Systems)
When we’re talking about thousands or millions of searches and complex queries, the game changes.
Full-Text Search (FTS) solves this with a structure called an inverted index. Instead of scanning the text, the system builds a map of words in advance, much like the index at the back of a book.
The inverted index maps each word to the documents where it appears. When a search comes in, the system simply looks it up in that map.
So when a user searches for "wireless bluetooth headphones", the system doesn’t scan the entire products table row by row. It queries the index and returns the results in milliseconds.
Two things to know before migrating: FTS works with whole words, not partial strings — searching for "bluet" won’t find "bluetooth" the way LIKE would. And for languages like Portuguese, without configuring unaccent, "acucar" won’t match "açúcar".
When Should You Go Beyond the Basics?
PostgreSQL has native FTS support and can take you a lot further than most people realize. You move away from it when you need a feature it doesn’t provide, not simply because your table got bigger.
That’s when specialized search engines come into play: Meilisearch and Typesense if you want fast search without having to operate a cluster, and Elasticsearch when the requirements are genuinely large.
The main benefits you get are:
Fuzzy search: tolerance for typos. Did the user search for "notebbok gammer"? They can still find what they’re looking for.
Relevance: results are ranked based on how well they match the query. The title can weigh more than the description, and results containing both terms appear before those containing only one.
Performance: extremely low latency even with millions of records and high concurrency.
Conclusion
If your application requires fast, intelligent search under high concurrency, moving to a Full-Text Search engine can be a game changer.
Just remember: the best architecture is the one that meets the requirements of your current situation. Don’t introduce complexity before you actually have a scalability problem that justifies it.
Make it work first. Then tighten the bolts as you go.
Bonus tip: Want to keep your relational database in sync with your search engine in real time? Look into CDC (Change Data Capture).
Just remember that this is yet another system you have to keep running — and another reason to postpone the migration while an index in your database is still doing the job.
Bonus tip: Want to keep your relational database in sync with your search engine in real time? Look into CDC (Change Data Capture). You’ll thank yourself later.