Elasticsearch and relational database combination

Question

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.

---

2. 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?

Answer 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)