6

Imagine I have a marketplace application - where users can search for products (we concentrate on clothes). Every product has an ID, name (text), description (text), price (numeric), size (numeric), brand, condition and so on.

The users can search for clothes. Right now the data is stored in a relational database (PostgreSQL). There is an Elasticsearch instance running which is used for searching in the name and description fields (as they are text fields).

Problem: I want to give the user the option to narrow down the search by using all parameters - so that the user can search for a specific size, condition AND description for example.

There are two approaches that I see:

  1. Implementing a mixture of Elasticsearch and database search. This would mean that I would filter the data at one place and continue with the filtered data at the other place to filter it again.

    Advantage: Using Elasticsearch for full-text search and using the database for specific 'column-search' as this is what both are good at. Like getting the best of both worlds.

    Disadvantage: How to determine where to start the search? The idea is of course to start the search at the place where I can eliminate most of the data so that the second search is performed over a smaller data set.

    Note also that the Elasticsearch instance will be definitely on a different machine that the PostgreSQL so we are talking about network overhead and higher response times.


  1. Going only with Elasticsearch. I am totally aware of the PostgreSQL ability to perform text-search but this is nowhere near powerful as Elasticsearch and also there is no guarantee that PostgreSQL will be always the DB of choice.

    Advantage: All search happens in one place. No intermediate results or similar.

    Disadvantage: Is Elasticsearch as strong as a the relational databases in terms of when filtering data based on a predicate. Please note that we are talking about fields like size and price - numeric fields where text-search is not in its prime but a simple WHERE clause is super fast.

Are there more advantages or disadvantages of both approaches that I am missing. Something crucial that would speak for or against the one or the other way?

5
  • 2
    I would say #2 is almost certainly your better option. Why re-invent the wheel? Elasticsearch was built specifically because RDBMS's struggle with searching, even simple searches.
    – theMayer
    Nov 5, 2015 at 17:49
  • Elasticsearch facetted search will be the best fit for that type of query. It is super fast and once you start to search over multiple facets it will beat most RDBMS solutions. Not only will it give you the products you are looking for but also the number of items for each facet. So say you have a search for text like 'tshirt' and a faceted search for a brand it will tell you how many products there are for each color, sie, price etc. Not only that but for prices you can define 'buckets' of a certain size. Nov 5, 2015 at 19:38
  • Faceted search is exactly what I am looking for! Just did not know the proper term. Now I just have to wrap my head around the Elasticsearch aggregations. Thanks!
    – Anton
    Nov 9, 2015 at 10:30
  • @Anton. So you moved to fully elastic search? It will be headache for me to move all existing code to move to another database which is totally different behaviour (I mean could still be easy if switching from one rdbms to another rdbms) Apr 25, 2020 at 6:52
  • 1
    @AnonymousCreator - as said above - faceted search was what I was looking for and this is what I ended up implementing. But I did not replace my PostgreSQL completely - I am actually now running both in parallel and filling ES asynchronously - embracing eventual consistency.
    – Anton
    Apr 25, 2020 at 8:46

1 Answer 1

1

ElasticSearch is effective enough for the searches you are looking for; ElasticSearch had sustained the benchmarks I've done(100 user/sec about 3 days); but from persistence perspective you need to hold one step back, if one of the node went down then it need Hugh time to recover and again it depend on the cluster configuration(take keen decision). It's able to hold good enough size payloads(2mb) , indexing 40+ fields, storing 40Million+ orders on 9 node cluster(nodes=27, replication factor=3)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.