This is actually where something like Elasticsearch would work significantly better than Postgres Full Text Search. PFTS is pretty good when you have a small data set and all of the relevant keywords are in the product data, but as OP discovered, it falls pretty far short of the mark when you have anything even remotely complicated.

But putting that aside for a moment, most of the effort here actually has nothing to do with which engine you are using to power your search. For this kind of challenge, it doesn't matter if you use Elasticsearch, Algolia, or Postgres Full Text Search! Instead, the problem space has a lot more to do with your data and how we can teach a computer to interpret it the way a human would.

In the e-commerce/search space, one of the biggest challenges is what I call "query understanding," which is breaking down the user's query and trying to understand what they meant. This isn't trying to be "smart" and show you things you aren't looking for, but rather understand what the underlying intent is and filter on _that_.

In OP's scenario, you aren't searching for things with the word "blue" and "shirt" in it. You're actually searching for one thing: a shirt, with a (strong) preference for blue!

Most search engines, by default, will do something to the effect of "SELECT * FROM products WHERE anyfield CONTAINS 'blue' AND anyfield CONTAINS 'shirt'" and just return everything. But as OP discovered, that might return a product where "blue pants made by the people who brought you your favorite pink shirt," which definitely isn't what you want.

Instead, you look at the query: what are we looking for here? We're looking for a shirt. So the entity is "shirt" and the modifier is "blue." Typically "entities" represent "categories" or "product types", but often are more granular than what we would put in those filters. Thus, when we construct the information architecture for an e-commerce platform, we need to include specific types of data that represent higher-order abstractions of the product that we're looking at. Cheezits aren't just Cheezits -- they're "crackers", and more specifically, "cheese crackers." Milk isn't just milk, it's a beverage and it's dairy.

We now have this concept of an entity ("shirt"), and a modifier of "blue", which through various processes we have determined is a color, a theme, or perhaps a pattern. Then we can return a list of all of the shirts, prioritizing the blue ones.

Complicating things is this: is navy considered "blue"? What about teal? Turquoise? Cyan? It's important to be precise about the colors for categorization, thumbnail, filtering/faceting, and UX purposes, but when people type in search queries, they often are not as precise as their brains are.

When sorting the results, should we prioritize proper "blue" over navy or teal? If someone types in "shirt" and then clicks the "blue" filter, what are we showing and hiding based on that?

We obviously need to be a little bit smarter here....

...and now we're down this giant rabbit hole of how to optimize search. Clearly, throwing things into a Postgres Full Text Search isn't going to cut it. We're going to need to leverage more annotated data (garbage in, garbage out -- make sure your product data is properly annotated!), better query understanding, better sorting, and faceting/filtering!

And, honestly, most of this isn't even about whether we're using Elasticsearch or Postgres or Algolia -- a lot of this is taking your meatspace brains and thinking about what words really mean, how people type them in, and how to interpret those words into something that returns something meaningful.

This ain't Google, but it also ain't a phone book. There are tons and tons of weird curve balls that will make you think you'll never figure it out, even to the very end of your days.

Here are a few random examples of how this gets confusing pretty fast:

- "chocolate milk" and "milk chocolate" consist of the same words but are two totally different things! (thank you, n-grams)

- all "roses" are "flowers", but not all flowers are roses! Conversely, "garbage bags" and "trash bags" are the same thing! synonyms can make things weird

- "cream cheese" is not cream-flavored cheese; it's something totally different. If you show me "sharp cheddar cheese" next to "cream cheese," I might not be happy with those results.

- "strawberry yogurt" is entity "yogurt" (not "strawberry" -- even though strawberries are nouns/entities, this time it's "flavor"), and "strawberry yogurt made with organic milk" is still yogurt, not milk!

- people make typos -- how do you detect them? if you get zero results, try a fuzzy search? That might work, but "coke" and "cake" are one transformation away, yet two totally different things

- you may not sell "blue shirts with a whale in an ocean," but you do sell "blue shirts" and "shirts with whales," which ones do you show and in what order?

One could spend years and years trying to perfect query understanding for any particular business. And perhaps even worse, there are no generalizable strategies to solve for everything! My examples above, if it weren't obvious, are food-themed because most of my search experience has revolved around data sets involving food. Searching for clothing presents an entirely different set of challenges! For example, a box of Cheerios is a box of Cheerios, but a T-shirt can come in eleven different sizes and sixteen different colors, and each of them are valid search results. Messy!

This is a pretty fun topic, IMO. Love to see posts about it on HN and love to see conversations about it!