Elasticsearch and PostgreSQL combination

Question

I have a marketplace application and I stored data in a PostgreSQL. I have performance problem for product search, I know I can improve search performance if I use Elasticsearch instead of PostgreSQL but I'm confused for is Elasticsearch strong as PostgreSQL (RDBMS) in create, update or delete operations. which makes sense; Using Elasticsearch for searches only, in other cases, using PostgreSQL, such as fetching by id. or using Elasticsearch for all operations

• Robert Harvey: Elasticsearch isn't a database. It is a search and analytics engine. So no, it's not going to have the same qualities as Postgres.
• zblash: then I should use elasticsearch with rdbms right?
• Robert Harvey: I have no idea. What are your goals? Have you read at least some of the documentation for Elasticsearch to discover its appropriate uses and setup procedures?
• skott: Elasticsearch instead of PostgreSQL: Nope. Elasticsearch with PostgresSQL: Possible - but depends on what you are looking for. Performance problem for search - depends on the complexity of your application and search queries. If the queries are too simple but find poor performance, you might have overlooked DB abilities, your db itself has features which when used could solve perf issues.
• Doc Brown: To the downvoters / close voters: come on, though the question is not well written, it is quite clear what the OP is after - how a search engine can interact with or replace a relational DB for a specific use case. That is a pretty focussed and answerable software engineering question.

Answer 1

Think of Elasticsearch as an eventually inconsistent index on top of your DBMS. The index is built once for a specific state of the database, but when the content of the DB changes, one will have to update this index afterwards (for example, in regular intervals, or somehow coupled to these changes). Depending on how one implements the update strategy, there can be some temporary inconsistencies between what Elasticsearch finds and what is really in the DB.

This is just the same situation you see everyday at search engines like Google: when the content of a website changes, and you look for a newly added keyword on a certain page, there is always a time lag until Google finds the changed page. And when a keyword or a whole page was deleted, there is also a time lag until Google does not present the page any more among its search results.

So an application has to take care for searches which may sometimes find something which is not really in the DB anymore, or does not find something immediately which was added shortly. Nevertheless it can make sense to let the search engine handle complex full text queries and deliver a resultset of product IDs, which are then queried in a relational DB by a simple SELECT ... FROM ... WHERE ID IN (...) statement.

You will have to analyse for yourself if the additional overhead is worth it, and if Elasticsearch really solves your issues. Alternatively (and definitely simpler to start with), you could look at the full text index capabilities of PostgreSQL and check if they can help you to optimize the queries of your specific application. See also this tutorial, starting with the words

.... but you're not sure you're ready to implement a full-blown search application like [...] Elasticsearch

which seems precisely to be your situation here.

• zblash: Thank you for your answer and kindly answer. I read tutorials you sent and i dont know its good/true way but i decided to use Elasticsearch for only search in my application and i will use logstash for sync PostgreSQL and Elasticsearch