4 ms·

A journey in e-commerce search

42 points by danpalmer 4y ago

polyrand 4y ago
Interesting approach, thanks for sharing! If I understood correctly, a user query is first applied to the `SearchItem` table (using the `search_vector` field?). Then the returned `filters` are used to generate a normal (not full-text search) SQL query that would retrieve items from the product table?
- danpalmer 4y ago
  That's correct! The second step therefore doesn't have any "fuzziness" to it, it's a strict inclusion/exclusion filter across products based on their attributes.
troelsSteegin 4y ago
Question: are they using "filters" as facets or what FAST called refiners? That's the impression I had on a read through. Endeca (now something inside Oracle) had some early success with faceted search for ecommerce.
- danpalmer 4y ago
  Yes I believe some systems call these facets. We did some investigation into faceted search systems, but found that by combining the faceted search in the same process as the free text search, tuning the system would be more complex and it would be possible to result in poor search results often. Essentially we forced all queries to be either only faceted search, or to fall back to full text across descriptions. For our use case, almost all queries could be satisfied by facets, so it didn't make sense to include any non-faceted search results in the results for those queries.
larrik 4y ago
As someone also dealing with "your search sucks", I felt all of this pain. I think sticking with Postgres was a mistake, though. Denormalizing into something like ElasticSearch would probably have gotten you further (and it's so fast). Searching the filters is pretty clever, though.
skimdesk 4y ago
Recently had a good experience with Typesense [0] for implementing ecommerce search. We solved the "blue shirts" problem by grouping a products color variants together using `group_by` and ranking by text match. The best "matching" shirts are then likely blue. This is a bit simplified, and not exactly the same case, but it worked surprisingly well. [0] https://typesense.org/ https://typesense.org/
- danpalmer 4y ago
  > The best "matching" shirts are then likely blue But this means there are then non-blue shirts. One of the issues for us is that we wanted to rank by item suitability for the user, not by relevance. When using FTS, this didn't work because you'd get a non-blue shirt that would be better for the user and that showed up first. But when using the filter-based search, that couldn't happen because the recommendation algorithm was only getting blue shirts to rank, so all of them were relevant, and the same level of relevance.
jrochkind1 4y ago
I don't totally understand that middle step "So why not search filters instead of products". It just means searching over the human-edited metadata instead of free-for-all free text fields that were scraped from who knows where? When we get to the last step, of implementing bitmaps for filters, and then trying to optimize to deal with the RAM implications... why not just use Solr or ElasticSearch instead of reinventing the wheel? That's basically what lucene (which Solr and ES are built on) is doing, something like that implementation you came up with, but with lots of optimizations and tunings on top of the naive implementation.
- deleted 4y ago
  [deleted]
- danpalmer 4y ago
  > It just means searching over the human-edited metadata instead of free-for-all free text fields that were scraped from who knows where? Not quite. Rather than the free text search being over products, it's over combinations of filters. These filters can then be applied to produce "exact" results, rather than "fuzzy" results based on textual matches. > why not just use Solr or ElasticSearch instead of reinventing the wheel A few reasons. Firstly, the faceted search in these took a fair bit of effort to expose to users in a way that wouldn't result in bad results (like "blue shirt" returning blue non-shirts, and non-blue shirts) Secondly, these are new infrastructure components that take a lot of work to run. ElasticSearch is notorious for being a pain to operate. We were a small engineering team, only 3-5 backend engineers in the company at the time (depending on how you count), running the whole stack, infra, everything.
habibur 4y ago
My solution to this problem had been to put the category name and all other product attributes in a full text search field and then index it. So that a search for "blue shirt" will select all shirt even if the title doesn't include it. And then fine tune what to return by putting "and" or "or" in between keywords based on that search engine's behavior.
- danpalmer 4y ago
  Yeah that would have been a pretty good intermediate option between our first and second passes at the issue. I think it would have still returned some incorrect results where we had overlap in category naming that was only apparent in context (e.g. jackets – coat jackets vs dinner jackets), but still probably a good improvement.
Jemaclus 4y ago
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!

42 points by danpalmer 4y ago

Interesting approach, thanks for sharing! If I understood correctly, a user query is first applied to the `SearchItem` table (using the `search_vector` field?). Then the returned `filters` are used to generate a normal (not full-text search) SQL query that would retrieve items from the product table?

danpalmer 4y ago
That's correct! The second step therefore doesn't have any "fuzziness" to it, it's a strict inclusion/exclusion filter across products based on their attributes.

troelsSteegin 4y ago

Question: are they using "filters" as facets or what FAST called refiners? That's the impression I had on a read through. Endeca (now something inside Oracle) had some early success with faceted search for ecommerce.

danpalmer 4y ago
Yes I believe some systems call these facets. We did some investigation into faceted search systems, but found that by combining the faceted search in the same process as the free text search, tuning the system would be more complex and it would be possible to result in poor search results often. Essentially we forced all queries to be either only faceted search, or to fall back to full text across descriptions. For our use case, almost all queries could be satisfied by facets, so it didn't make sense to include any non-faceted search results in the results for those queries.

larrik 4y ago

As someone also dealing with "your search sucks", I felt all of this pain. I think sticking with Postgres was a mistake, though. Denormalizing into something like ElasticSearch would probably have gotten you further (and it's so fast). Searching the filters is pretty clever, though.

skimdesk 4y ago

Recently had a good experience with Typesense [0] for implementing ecommerce search. We solved the "blue shirts" problem by grouping a products color variants together using `group_by` and ranking by text match. The best "matching" shirts are then likely blue. This is a bit simplified, and not exactly the same case, but it worked surprisingly well. [0] https://typesense.org/ https://typesense.org/

danpalmer 4y ago
> The best "matching" shirts are then likely blue But this means there are then non-blue shirts. One of the issues for us is that we wanted to rank by item suitability for the user, not by relevance. When using FTS, this didn't work because you'd get a non-blue shirt that would be better for the user and that showed up first. But when using the filter-based search, that couldn't happen because the recommendation algorithm was only getting blue shirts to rank, so all of them were relevant, and the same level of relevance.

jrochkind1 4y ago

I don't totally understand that middle step "So why not search filters instead of products". It just means searching over the human-edited metadata instead of free-for-all free text fields that were scraped from who knows where? When we get to the last step, of implementing bitmaps for filters, and then trying to optimize to deal with the RAM implications... why not just use Solr or ElasticSearch instead of reinventing the wheel? That's basically what lucene (which Solr and ES are built on) is doing, something like that implementation you came up with, but with lots of optimizations and tunings on top of the naive implementation.

deleted 4y ago
[deleted]
danpalmer 4y ago
> It just means searching over the human-edited metadata instead of free-for-all free text fields that were scraped from who knows where? Not quite. Rather than the free text search being over products, it's over combinations of filters. These filters can then be applied to produce "exact" results, rather than "fuzzy" results based on textual matches. > why not just use Solr or ElasticSearch instead of reinventing the wheel A few reasons. Firstly, the faceted search in these took a fair bit of effort to expose to users in a way that wouldn't result in bad results (like "blue shirt" returning blue non-shirts, and non-blue shirts) Secondly, these are new infrastructure components that take a lot of work to run. ElasticSearch is notorious for being a pain to operate. We were a small engineering team, only 3-5 backend engineers in the company at the time (depending on how you count), running the whole stack, infra, everything.

habibur 4y ago

My solution to this problem had been to put the category name and all other product attributes in a full text search field and then index it. So that a search for "blue shirt" will select all shirt even if the title doesn't include it. And then fine tune what to return by putting "and" or "or" in between keywords based on that search engine's behavior.

danpalmer 4y ago
Yeah that would have been a pretty good intermediate option between our first and second passes at the issue. I think it would have still returned some incorrect results where we had overlap in category naming that was only apparent in context (e.g. jackets – coat jackets vs dinner jackets), but still probably a good improvement.

Jemaclus 4y ago

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!